I have a function to send multiple reports to a single recipient. Error codes allow me to skip reports that were not created due an 'on error code' (in the report properties) to cancel the report. The problem I'm having is I don't want the emails to go out if there are no attachments. This may be a simple fix but alas it stumps me at the moment.
Function sndrpt_Dept1()
On Error GoTo 0
On Error Resume Next
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
Dim strAttach1 As String
Dim strAttach2 As String
Dim strAttach3 As String
Dim strAttach4 As String
Dim strAttach5 As String
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)
'Output Reports
DoCmd.OutputTo acOutputReport, "Team #1", acFormatPDF, "C:\Temp\Team #1 Report.pdf", False
DoCmd.OutputTo acOutputReport, "Team #2", acFormatPDF, "C:\Temp\Team #2 Report.pdf", False
DoCmd.OutputTo acOutputReport, "Team #3", acFormatPDF, "C:\Temp\Team #3 Report.pdf", False
'Set Attachments
strAttach1 = "C:\Temp\Team #1 Report.pdf"
strAttach2 = "C:\Temp\Team #2 Report.pdf"
strAttach3 = "C:\Temp\Team #3 Report.pdf"
'Generate email
With objEmail
.To = DLookup("[Department Manager Email]", "Dept Email Listings", "[Department]='Department #1")
.Subject = "OverDue Items"
.Body = "Fix these things."
.Display
.Attachments.Add strAttach1
.Attachments.Add strAttach2
.Attachments.Add strAttach3
.Send
End With
'Remove attachments from drive
Kill strAttach1
Kill strAttach2
Kill strAttach3
End Function