Split In To Individual MS-Access Reports And Email To Corresponding Address

[vnwallace]

Hi,

I have the below code that will run my query, print my reports, save them in bulk as a single PDF, and send them in one email. I need help finding the right code to separate the reports so that each report will email individually to their corresponding email address in a table I have created (the reports gather data from this table as well). My code currently will generate and email and I have to click the send button--this is what I want. I want to be able to review the email before I send it.

Can someone help me with the correct code I need in order to make this happen and also where to put the code in my current code?

Any help would be GREATLY appreciated!! Thanks!

Private Sub Command11_Click()

DoCmd.OpenQuery "Report Query"
DoCmd.Close acQuery, "Report Query"

DoCmd.OpenReport "DECO Import File", PrintOut
'This will print the reports

Dim stReport As String
Dim stWhere As String
Dim stSubject As String
Dim stEmailMessage As String
Dim stCaption As String
Dim myPath As String
'This sets the strings for each part of the email

stEmailMessage = "Please see the attached collateral call for today."
stSubject = "Collateral Demand"
stReport = "DECO Import File"

stCaption = "Collateral Demand" & Format(Now(), " mm-dd-yyyy")
myPath = "J:\Investments\Trade Operations\Derivative Transactions\Collateral\Access Database\"

DoCmd.SendObject acSendReport, stReport, acFormatPDF, , , , stSubject, stEmailMessage, True, ""
DoCmd.OutputTo acOutputReport, stReport, acFormatPDF, myPath & stCaption & ".pdf", False, , , acExportQualityPrint

End Sub