Solved Sending email without the attachment - How can i stop this... (1 Viewer)

Number11

Member
Local time
Today, 02:51
Joined
Jan 29, 2020
Messages
278
Hi, this is my code that works but every now and then it emails without the attachment - how can i change the code to check for attachment before sending the email..... :(

Private Sub Command48_Click()
Dim rst
Dim XL As Excel.Application
Set XL = CreateObject("excel.application")
Dim vFile
vFile = "Template Location Here"
Set rst = CurrentDb.OpenRecordset("AllJobs")
If rst.RecordCount = 0 Then
Dialog.Box "No Job Requests Today!", vbInformation, "Database Message"
Else
rst.MoveLast
Dialog.Box "A Total Of: " & rst.RecordCount & " Booking Requests Found And Will Be Emailed!", vbInformation, "Database Message"
rst.MoveFirst
With XL
.Visible = False
.Workbooks.Open vFile
.Sheets("JOBS").Select
.Range("A4").Select
.ActiveCell.CopyFromRecordset rst
.ActiveWorkbook.SaveAs filename:=(LOCATION HERE), password:="HA123"
.ActiveWorkbook.close
.Application.Quit
Dim signature As String
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail ' This creates a blank email and captures the users default signature.
.BodyFormat = olFormatHTML
.Display
End With
signature = OutMail.HTMLBody
strBodyText = "Hi,<br>" & _
"Please find attached appointment requests.<br>" & _
"Let me know if you have problems.<br>" & _
"<br><br>Thanks,<br>"
With OutMail
.To = "email address here"
.CC = ""
.BCC = ""
.Subject = "Job Request Notification"
.HTMLBody = strFntNormal & strBodyText & strTableBody & "<br><br>" & signature
.Attachments.Add "File Location Here"
.Send 'or use .Send


End With
'outlook tidy up
Set OutMail = Nothing
Set OutApp = Nothing

Kill "file location here and name"

Dialog.Box "All Data has been exported and has been sent", vbInformation, "Task Complete"

End With
End If
End Sub
 

CJ_London

Super Moderator
Staff member
Local time
Today, 02:51
Joined
Feb 19, 2013
Messages
12,957
try
Code:
if dir("file location here and name") then
    'send email
else
    'don't send email
end if
 

Number11

Member
Local time
Today, 02:51
Joined
Jan 29, 2020
Messages
278
try
Code:
if dir("file location here and name") then
    'send email
else
    'don't send email
end if

Thanks no that didnt work! I added Kill file before running your suggested code and the email still got send
 

CJ_London

Super Moderator
Staff member
Local time
Today, 02:51
Joined
Feb 19, 2013
Messages
12,957
ah forgot it returns a zls if not found try

if dir("file location here and name")<>"" then
 

Number11

Member
Local time
Today, 02:51
Joined
Jan 29, 2020
Messages
278
ah forgot it returns a zls if not found try

if dir("file location here and name")<>"" then
Thanks that did work, but it did open the email but didnt send it so guess need to add end statement?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 02:51
Joined
Feb 19, 2013
Messages
12,957
don't know without knowing how you used the snippet.

you don't have an end if your code is on one line

if true then do something

if on two or more lines your do use an end per post #2
 

Users who are viewing this thread

Top Bottom