Sending an email with attached report - dont sent if nothing attached? (1 Viewer)

Number11

Member
Local time
Today, 11:20
Joined
Jan 29, 2020
Messages
312
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 :)
 
Last edited:

Minty

AWF VIP
Local time
Today, 11:20
Joined
Jul 26, 2013
Messages
8,460
I'm confused (easily and often) as surely this line

Code:
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
Code:
If rst.RecordCount > 0 Then
Remove the else e.g. If there are records then proceed
OR my preferred route would be
Code:
If rst.RecordCount = 0 Then Exit Sub   ' No records lets get out of here.
 

Minty

AWF VIP
Local time
Today, 11:20
Joined
Jul 26, 2013
Messages
8,460
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

Enthusiastic Amateur
Local time
Today, 11:20
Joined
Sep 21, 2011
Messages
8,301
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

Enthusiastic Amateur
Local time
Today, 11:20
Joined
Sep 21, 2011
Messages
8,301
For signatures I used a Template.
 

Number11

Member
Local time
Today, 11:20
Joined
Jan 29, 2020
Messages
312
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?
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:20
Joined
Sep 21, 2011
Messages
8,301

Users who are viewing this thread

Top Bottom