Email Individual Reports to their corresponding email addresses

[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. 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

[June7]

Common topic, search forum.

Start with:

https://www.accessforums.net/access/...ils-36702.html

[vnwallace]

Hi,

I am having troubles fitting that in to my current code.. I am not sure where to put the certain information requred to loop through the record sets and gather the email addresses from the reports to send to.. 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

[June7]

Code logic to send individual emails:
1. open recordset of email addresses
2. get address from record
3. open filtered report
4. send report
5. save PDF
6. close report
7. move to next record
8. repeat from 2

Something like:

Dim rs As DAO. Recordset
stCaption = "Collateral Demand" & Format(Now(), " mm-dd-yyyy")
myPath = "J:\Investments\Trade Operations\Derivative Transactions\Collateral\Access Database\"
stEmailMessage = "Please see the attached collateral call for today."
stSubject = "Collateral Demand"
stReport = "DECO Import File"
Set rs = CurrentDb.OpenRecordset("SELECT email FROM tablename;")
DoCmd.OpenReport "DECO Import File", acViewPreview
While Not rs.EOF
DoCmd.SendObject acSendReport, , acFormatPDF, rs!email, , , stSubject, stEmailMessage, True, ""
DoCmd.OutputTo acOutputReport, , acFormatPDF, myPath & stCaption & ".pdf", False, , , acExportQualityPrint
rs.MoveNext
Wend
DoCmd.Close acReport, stReport, acSaveNo
rs.close