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

[Excel Fox]

vmwallace, the report query seems to be fetching the entire report. To split to individual reports, you probably need to break down the query based on whatever criteria your reports have to be split. So it would be best if you can save a sample of your database on a file-share site, and post the link here. One of the developers can have a look.

[Excel Fox]

And please ensure you wrap your code with code tags. The instructions are clearly mentioned in the header area of the post window, and also when you start a new thread. Please do not ignore them.

[vnwallace]

Thank you! I will definitely do that next time! What share site should I use? I have never shared a database this way so any help would be greatly appreciated. Thanks again!

[Excel Fox]

One of these should be free. 4shared should be fine. You could also try Top 15 Most Popular File Sharing Websites

But ensure that it doesn't require an account to download files.

[vnwallace]

Thank you, I used 4Share like directed and below is the link. Please let me know if this does not work, if it doesn't work I will use another one.

Collateral Database Test - Download - 4shared - Valerie Wallace

[Excel Fox]

Thanks, but you'll need to provide the linked excel file also.

[vnwallace]

Apologies, I have attached the link to the file. Thanks!

DECO Import File - Download - 4shared - Valerie Wallace

[Excel Fox]

I ran your query, and the report is only showing data for one record, ie, RGA Re DV COL

Does the report have any filter somewhere?

[vnwallace]

You are correct in that there is only one record. That day for our demands there was only one record that had the column for "collateral to receive" > 0. I am attaching another link for the excel file that will have more than one record. Thank you so much for your help!!

DECO Import File - Download - 4shared - Valerie Wallace