Sending an email with attached report - dont sent if nothing attached?

[Number11]

Hi, so i have this code which is run from a button i need to stop the email from sending if no attachment has been made..

Dim rst
Dim XL As Excel.Application
Set XL = CreateObject("excel.application")
Dim vFile
vFile = "Template File Location Here"
Set rst = CurrentDb.OpenRecordset("All")
If rst.RecordCount = 0 Then
Else
rst.MoveLast
Dialog.Box "A Total Of: " & rst.RecordCount & " Records Found And Will Be Emailed!", vbInformation, "Database Message"
rst.MoveFirst

With XL
.Visible = False
.Workbooks.Open vFile
.Sheets("SSS").Select
.Range("A4").Select
.ActiveCell.CopyFromRecordset rst
.ActiveWorkbook.SaveAs filename:=("File Name and Location here & Format(Now(), "DD-MMM-YYYY hhmm ") & ".xlsx"), password:="Test”
.ActiveWorkbook.close
.Application.Quit


Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strbody = "Hi,<br>" & _
"Please find attached xx Report.<br>" & _
"Let me know if you have problems.<br>" & _
"<br><br>Thanks,<br>"
On Error Resume Next
With OutMail
'.Display
.To = ""
.CC = "
.BCC = ""
.Subject = "Test"
.HTMLBody = strbody & "<br>" & .HTMLBody
.Attachments.Add "File name and locatin here" & Format(Now(), "DD-MMM-YYYY hhmm ") & ".xlsx"
End With

On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing


Also would be good to know how to get it to add the Outlook Signatures

 

[Gasman]

Look for the file you wish to add, and if not present cancel the email?
Or count the attachments, which does look a little laborious.

https://stackoverflow.com/questions/23801737/count-outlook-vba-attachment

 

[Number11]

Look for the file you wish to add, and if not present cancel the email?
Or count the attachments, which does look a little laborious.

https://stackoverflow.com/questions/23801737/count-outlook-vba-attachment

Hi Thanks for your reply, so just want to stop sending email without the file attached?

 

[Minty]

I'm confused (easily and often) as surely this line

If rst.RecordCount = 0 Then

Else

Should stop the whole process if there are no records?
And a better way to write that would simply be

If rst.RecordCount > 0 Then

Remove the else e.g. If there are records then proceed
OR my preferred route would be

If rst.RecordCount = 0 Then Exit Sub   ' No records lets get out of here.

 

[Minty]

And having actually read the code properly there is a foible with your code that is probably making it not work.
As you may have realised the recordcount won't be populated correctly without a MoveLast (which you are doing straight afterwards).

Do that first, then check the number of records and I suspect all will be good in the world.

 

[Gasman]

So count the attachments after the Add using the code in that link I posted.
If it comes back zero exit the routing with a message.?

 

[Gasman]

For signatures I used a Template.

 

[Number11]

Ok so sometimes it falls over afte this point...

.Attachments.Add "File name and locatin here" & Format(Now(), "DD-MMM-YYYY hhmm ") & ".xlsx"

email is send without attachment so yes you are correct the

If rst.RecordCount = 0 Then

does stop if not records found, but the issue is that for some reason an email can be sent without the attachment?

 

[Number11]

For signatures I used a Template.

Oh ok sounds good how would i go about setting this up

 

[Gasman]

Well I normally start with Google.

https://docs.microsoft.com/en-us/office/vba/api/outlook.application.createitemfromtemplate

Then construct some code from what I find like below

    ' Get appdata path
    strAppdata = Environ("Appdata")
    
    ' Set paths
    strTemplatePath = strAppdata & "\Microsoft\Templates"

...
...
        Set objOutlookMsg = objOutlook.CreateItemFromTemplate(strTemplatePath & <Your Template Name>)