As far as first, using the Filter method of the report would be simplest. However, you will still need to iterate through a recordset to get the email addresses and the WHERE criteria. When this is a requirement, I prefer to adjust the Recordsource of the report.
I would suggest you create a report in a temp folder and email that report using Outlook Automation. The next step would be to test some code that will export a report to a temp folder and email it, without the loop.
Beginning with a new form and while in Design View, add a single command button and save your form. Paste the following into your Click event of your button. You will want to replace the address in red with your own email address. Like I mentioned, take a baby step and don't try looping through anything yet. Send the report to yourself.
Code:
'Declare variables and assign values
Dim strReportName As String
Dim strPath As String
Dim strFullPath As String
Dim strEmailAddress As String
Dim strSubject As String
Dim strBody As String
strReportName = "All Payments to Partners"
strEmailAddress = "TestEmailAddress@MyDomain.Com"
strSubject = "This is my report"
strBody = "Please find a copy of my report attached here."
'before exporting your report you will want to make
'sure there is not a file in the folder with the same name
'and that the folder actually exists
strPath = manageTempFolder
'Bail if there is a problem with the directory
If strPath = "" Then
Exit Sub
End If
'Add the file name to the path
strFullPath = strPath & "\" & strReportName & ".pdf"
'create a report in the temp folder
DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, strFullPath
'Use early binding to create an email
Dim objOutlook As New Outlook.Application
Dim objNewEmail As MailItem
Dim objAttachReport As Attachments
'Instantiate your objects
Set objNewEmail = objOutlook.CreateItem(olMailItem)
Set objAttachReport = objNewEmail.Attachments
'Add your file to the Attachments Collection
objAttachReport.Add strFullPath, olByValue
With objNewEmail
.BodyFormat = olFormatRichText
.To = strEmailAddress
.Subject = strSubject
.HTMLBody = strBody
'Save the email to ensure the attachment is a
'copy of the original file (olByValue)
.Save
.Send
End With
'Delete the report you just emailed
Kill strFullPath
'tidy up
Set objAttachReport = Nothing
Set objNewEmail = Nothing
Set objOutlook = Nothing
MsgBox "Complete"
In addition to this code, you will also need a custom function to manage your temp PDF reports. The following code will create a folder where your DB resides and the click event will use this folder to store the temp reports. You would paste this below your click event's End Sub line. Use the enter key to create empty space below your entire Click Event and then paste.
Code:
Function manageTempFolder() As String
'''DELETE the PDF files in the temporary folder
'If the temp folder does not exist, create it
'''''****************************************
On Error GoTo ERR_manageTempFolder
'Start with the DB's current path and add
'unique subdirectories
Dim strPath As String
strPath = Application.CurrentProject.Path & "\TempFolder20581A\TempEmail"
'set default for Return
manageTempFolder = strPath
'Returns empty string on error
'Make sure the path exists and make folder if not
'Test for the subfolder
If Dir(strPath, vbDirectory) = "" Then 'make a folder
'Test for the parent
If Dir(Application.CurrentProject.Path & "\TempFolder20581A", vbDirectory) <> "" Then 'Only need subfolder
MkDir (strPath)
Else 'Make both
MkDir (Application.CurrentProject.Path & "\TempFolder20581A")
MkDir (strPath)
End If
End If
'Get ready to delete all pdf files
Dim strKill As String
strKill = Dir(strPath & "*.pdf", vbHidden)
'Delete the temporary files
While strKill <> ""
Kill strPath & strKill
strKill = Dir(strPath & "*.pdf", vbHidden)
Wend
EXIT_manageTempFolder:
Exit Function
ERR_manageTempFolder:
MsgBox Err.Description & vbCrLf & vbCrLf & "Error # " & Err.Number
manageTempFolder = ""
Resume EXIT_manageTempFolder
End Function
If you have any troubles, do your best to describe the issue.