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 (http://www.ebizmba.com/articles/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 (http://www.4shared.com/zip/pEAK-B-g/Collateral_Database_Test.html)

[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 (http://www.4shared.com/file/HYTn1JOe/DECO_Import_File.html)

[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 (http://www.4shared.com/file/Pgw6Fxd_/DECO_Import_File.html)

[Excel Fox]

OK, this seems to be working, but I haven't tested the mailing part. Can you try this



Private Sub Command11_Click()

'This will print the reports
Dim stEmailMessage As String
Dim stReport As String
Dim stCaption As String
Dim myPath As String
Dim strSQL As String
Dim stSubject As String
Dim rst As Recordset

'This sets the strings for each part of the email
stEmailMessage = "Please see the attached collateral call for today."
stReport = "DECO Import File"
'This will change the caption of the report (which is what the filename is saved as" to something meaningful - in this case the numbers and date of thereport. If you have multiple people making these reports at once, and would have multiple ones with the same date/time stamp, you should modify this a little to prevent overwriting files.
stCaption = "Collateral Demand" & Format(Now(), " mm-dd-yyyy")

myPath = "J:\Investments\Trade Operations\Derivative Transactions\Collateral\Access Database\"
strSQL = "SELECT [DECO Import File].Combo" & vbLf & _
"FROM [Counterparty Data] INNER JOIN [DECO Import File] ON [Counterparty Data].Combo = [DECO Import File].Combo" & vbLf & _
"WHERE ((([DECO Import File].[Collateral To Receive])>0));"
Set rst = CurrentDb.OpenRecordset(strSQL)
While Not (rst.BOF And rst.EOF)
stSubject = "Collateral Demand " & rst.Fields(0).Value
RewriteQuerySQL "Report Query", rst.Fields(0).Value
DoCmd.OpenQuery "Report Query"
DoCmd.Close acQuery, "Report Query"
DoCmd.OpenReport "DECO Import File", acViewNormal
DoCmd.SendObject acSendReport, stReport, acFormatPDF, , , , stSubject, stEmailMessage, True, ""
DoCmd.OutputTo acOutputReport, stReport, acFormatPDF, myPath & stCaption & ".pdf", False, , , acExportQualityPrint
rst.MoveNext
Wend

End Sub

Sub RewriteQuerySQL(strQueryName As String, strParameter As String)

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs(strQueryName)

qdf.SQL = "SELECT [DECO Import File].CurrentDate, [DECO Import File].COBDate, [Counterparty Data].BrokerName, [Counterparty Data].PortfolioName, [DECO Import File].Exposure, [DECO Import File].[Support Req], [DECO Import File].[Collateral Pledged/Held], [DECO Import File].[Minimum Transfer Amt (Broker)], [DECO Import File].[Collateral To Receive], [Counterparty Data].BankName, [Counterparty Data].[CashAccount#], [Counterparty Data].CashAccountName, [Counterparty Data].CashABA, [Counterparty Data].[SecuritiesAccount#], [Counterparty Data].SecuritiesAccountName, [Counterparty Data].SecuritiesABA, [Counterparty Data].CPEmail" & vbCrLf & _
"FROM [Counterparty Data] INNER JOIN [DECO Import File] ON [Counterparty Data].Combo = [DECO Import File].Combo" & vbCrLf & _
"WHERE ((([DECO Import File].[Collateral To Receive])>0) AND (([Counterparty Data].Combo)=" & Chr(34) & strParameter & Chr(34) & "));"

End Sub

[vnwallace]

Thanks!! There are a few things that I need help with.. I need to add a "To" address to the emails that gather from the table and a "CC" address of derivcollateral@rgare.com. Also, I need either the entire report file or each individually to save to my designated folder and this time only one of them did so. Thank you SO much for all of your time and effort in this.. It's truly appreciated!

[Excel Fox]

Try this


Private Sub Command11_Click()

'This will print the reports
Dim stEmailMessage As String
Dim stReport As String
Dim stCaption As String
Dim myPath As String
Dim strSQL As String
Dim stSubject As String
Dim rst As Recordset

'This sets the strings for each part of the email
stEmailMessage = "Please see the attached collateral call for today."
stReport = "DECO Import File"
'This will change the caption of the report (which is what the filename is saved as" to something meaningful - in this case the numbers and date of thereport. If you have multiple people making these reports at once, and would have multiple ones with the same date/time stamp, you should modify this a little to prevent overwriting files.

myPath = "J:\Investments\Trade Operations\Derivative Transactions\Collateral\Access Database\"
strSQL = "SELECT [DECO Import File].Combo, [Counterparty Data].CPEmail" & vbLf & _
"FROM [Counterparty Data] INNER JOIN [DECO Import File] ON [Counterparty Data].Combo = [DECO Import File].Combo" & vbLf & _
"WHERE ((([DECO Import File].[Collateral To Receive])>0));"
Set rst = CurrentDb.OpenRecordset(strSQL)
While Not (rst.BOF And rst.EOF)
stSubject = "Collateral Demand " & rst.Fields(0).Value
stCaption = stSubject & Format(Now(), " mm-dd-yyyy")
RewriteQuerySQL "Report Query", rst.Fields(0).Value
DoCmd.OpenQuery "Report Query"
DoCmd.Close acQuery, "Report Query"
DoCmd.OpenReport "DECO Import File", acViewNormal
DoCmd.SendObject acSendReport, stReport, acFormatPDF, rst.Fields(1).Value, "derivcollateral@rgare.com", , stSubject, stEmailMessage, True, ""
DoCmd.OutputTo acOutputReport, stReport, acFormatPDF, myPath & stCaption & ".pdf", False, , , acExportQualityPrint
rst.MoveNext
Wend

End Sub

Sub RewriteQuerySQL(strQueryName As String, strParameter As String)

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs(strQueryName)

qdf.SQL = "SELECT [DECO Import File].CurrentDate, [DECO Import File].COBDate, [Counterparty Data].BrokerName, [Counterparty Data].PortfolioName, [DECO Import File].Exposure, [DECO Import File].[Support Req], [DECO Import File].[Collateral Pledged/Held], [DECO Import File].[Minimum Transfer Amt (Broker)], [DECO Import File].[Collateral To Receive], [Counterparty Data].BankName, [Counterparty Data].[CashAccount#], [Counterparty Data].CashAccountName, [Counterparty Data].CashABA, [Counterparty Data].[SecuritiesAccount#], [Counterparty Data].SecuritiesAccountName, [Counterparty Data].SecuritiesABA, [Counterparty Data].CPEmail" & vbCrLf & _
"FROM [Counterparty Data] INNER JOIN [DECO Import File] ON [Counterparty Data].Combo = [DECO Import File].Combo" & vbCrLf & _
"WHERE ((([DECO Import File].[Collateral To Receive])>0) AND (([Counterparty Data].Combo)=" & Chr(34) & strParameter & Chr(34) & "));"

End Sub

[vnwallace]

Thank you this works great! I have now changed the Report title to "Collateral Demand" but I cannot seem to get the PDF to change to that title on the attachment in the email. I have my changed code below.. any ideas?


Dim stEmailMessage As String
Dim stReport As String
Dim stCaption As String
Dim myPath As String
Dim strSQL As String
Dim stSubject As String
Dim rst As Recordset

'This sets the strings for each part of the email
stEmailMessage = "Please see the attached collateral call for today."
stReport = "Collateral Demand"
'This will change the caption of the report (which is what the filename is saved as" to something meaningful - in this case the numbers and date of thereport. If you have multiple people making these reports at once, and would have multiple ones with the same date/time stamp, you should modify this a little to prevent overwriting files.

myPath = "J:\Investments\Trade Operations\Derivative Transactions\Collateral\Access Database\"
strSQL = "SELECT [DECO Import File].PortfolioName, [Counterparty Data].CPEmail" & vbLf & _
"FROM [Counterparty Data] INNER JOIN [DECO Import File] ON [Counterparty Data].PortfolioName = [DECO Import File].PortfolioName" & vbLf & _
"WHERE ((([DECO Import File].[Collateral To Receive])>0));"
Set rst = CurrentDb.OpenRecordset(strSQL)
While Not (rst.BOF And rst.EOF)
stSubject = "Collateral Demand - " & rst.Fields(0).Value
stCaption = stSubject & Format(Now(), " mm-dd-yyyy")
RewriteQuerySQL "Report Query", rst.Fields(0).Value
DoCmd.OpenQuery "Report Query"
DoCmd.Close acQuery, "Report Query"
DoCmd.OpenReport "Collateral Demand", acViewNormal
DoCmd.SendObject acSendReport, stReport, acFormatPDF, rst.Fields(1).Value, "derivcollateral@rgare.com", , stSubject, stEmailMessage, True, ""
DoCmd.OutputTo acOutputReport, stReport, acFormatPDF, myPath & stCaption & ".pdf", False, , , acExportQualityPrint
rst.MoveNext
Wend

End Sub

Sub RewriteQuerySQL(strQueryName As String, strParameter As String)

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs(strQueryName)

qdf.SQL = "SELECT [DECO Import File].CurrentDate, [DECO Import File].COBDate, [Counterparty Data].BrokerName, [Counterparty Data].PortfolioName, [DECO Import File].Exposure, [DECO Import File].[Support Req], [DECO Import File].[Collateral Pledged/Held], [DECO Import File].[Minimum Transfer Amt (Broker)], [DECO Import File].[Collateral To Receive], [Counterparty Data].BankName, [Counterparty Data].[CashAccount#], [Counterparty Data].CashAccountName, [Counterparty Data].CashABA, [Counterparty Data].[SecuritiesAccount#], [Counterparty Data].SecuritiesAccountName, [Counterparty Data].SecuritiesABA, [Counterparty Data].CPEmail" & vbCrLf & _
"FROM [Counterparty Data] INNER JOIN [DECO Import File] ON [Counterparty Data].Combo = [DECO Import File].Combo" & vbCrLf & _
"WHERE ((([DECO Import File].[Collateral To Receive])>0) AND (([Counterparty Data].Combo)=" & Chr(34) & strParameter & Chr(34) & "));"

End Sub

[Excel Fox]

All I can see is that you changed

stReport = "DECO Import File" to


stReport = "Collateral Demand"

[vnwallace]

I also changed
DoCmd.OpenReport "Collateral Demand", acViewNormal How do I get my report title to change on the email? I think there is something that I am missing. Thank you again!

[vnwallace]

Also I notice now that when I see the emails there is not actual data in there with the below code. It takes out all of the fill ins.


Dim stEmailMessage As String
Dim stReport As String
Dim stCaption As String
Dim myPath As String
Dim strSQL As String
Dim stSubject As String
Dim rst As Recordset

'This sets the strings for each part of the email
stEmailMessage = "Please see the attached collateral call for today." & (Chr(13) + Chr(10)) & (Chr(13) + Chr(10)) & "Thanks!" & (Chr(13) + Chr(10)) & "Derivative Collateral Team" & (Chr(13) + Chr(10)) & "derivcollateral@rgare.com" & (Chr(13) + Chr(10)) & (Chr(13) + Chr(10)) & "This message is intended only for the use of the individual or entity to which it is addressed, and may contain information that is privileged, confidential, and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by telephone and return the original message to its sender. Thank you for your cooperation and assistance."

stReport = "Collateral Demand"
'This will change the caption of the report (which is what the filename is saved as" to something meaningful - in this case the numbers and date of thereport. If you have multiple people making these reports at once, and would have multiple ones with the same date/time stamp, you should modify this a little to prevent overwriting files.

myPath = "J:\Investments\Trade Operations\Derivative Transactions\Collateral\Access Database\"
strSQL = "SELECT [DECO Import File].PortfolioName, [Counterparty Data].CPEmail" & vbLf & _
"FROM [Counterparty Data] INNER JOIN [DECO Import File] ON [Counterparty Data].PortfolioName = [DECO Import File].PortfolioName" & vbLf & _
"WHERE ((([DECO Import File].[Collateral To Receive])>0));"
Set rst = CurrentDb.OpenRecordset(strSQL)
While Not (rst.BOF And rst.EOF)
stSubject = "Collateral Demand - " & rst.Fields(0).Value
stCaption = stSubject & Format(Now(), " mm-dd-yyyy")
RewriteQuerySQL "Report Query", rst.Fields(0).Value
DoCmd.OpenQuery "Report Query"
DoCmd.Close acQuery, "Report Query"
DoCmd.OpenReport "Collateral Demand", acViewNormal
DoCmd.SendObject acSendReport, stReport, acFormatPDF, rst.Fields(1).Value, "derivcollateral@rgare.com", , stSubject, stEmailMessage, True, ""
DoCmd.OutputTo acOutputReport, stReport, acFormatPDF, myPath & stCaption & ".pdf", False, , , acExportQualityPrint
rst.MoveNext
Wend

End Sub

Sub RewriteQuerySQL(strQueryName As String, strParameter As String)

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs(strQueryName)

qdf.SQL = "SELECT [DECO Import File].CurrentDate, [DECO Import File].COBDate, [Counterparty Data].BrokerName, [Counterparty Data].PortfolioName, [DECO Import File].Exposure, [DECO Import File].[Support Req], [DECO Import File].[Collateral Pledged/Held], [DECO Import File].[Minimum Transfer Amt (Broker)], [DECO Import File].[Collateral To Receive], [Counterparty Data].BankName, [Counterparty Data].[CashAccount#], [Counterparty Data].CashAccountName, [Counterparty Data].CashABA, [Counterparty Data].[SecuritiesAccount#], [Counterparty Data].SecuritiesAccountName, [Counterparty Data].SecuritiesABA, [Counterparty Data].CPEmail" & vbCrLf & _
"FROM [Counterparty Data] INNER JOIN [DECO Import File] ON [Counterparty Data].Combo = [DECO Import File].Combo" & vbCrLf & _
"WHERE ((([DECO Import File].[Collateral To Receive])>0) AND (([Counterparty Data].Combo)=" & Chr(34) & strParameter & Chr(34) & "));"

End Sub

[vnwallace]

Apologies, here is the tagged code:


Dim stEmailMessage As String
Dim stReport As String
Dim stCaption As String
Dim myPath As String
Dim strSQL As String
Dim stSubject As String
Dim rst As Recordset

'This sets the strings for each part of the email
stEmailMessage = "Please see the attached collateral call for today." & (Chr(13) + Chr(10)) & (Chr(13) + Chr(10)) & "Thanks!" & (Chr(13) + Chr(10)) & "Derivative Collateral Team" & (Chr(13) + Chr(10)) & "derivcollateral@rgare.com" & (Chr(13) + Chr(10)) & (Chr(13) + Chr(10)) & "This message is intended only for the use of the individual or entity to which it is addressed, and may contain information that is privileged, confidential, and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by telephone and return the original message to its sender. Thank you for your cooperation and assistance."

stReport = "Collateral Demand"
'This will change the caption of the report (which is what the filename is saved as" to something meaningful - in this case the numbers and date of thereport. If you have multiple people making these reports at once, and would have multiple ones with the same date/time stamp, you should modify this a little to prevent overwriting files.

myPath = "J:\Investments\Trade Operations\Derivative Transactions\Collateral\Access Database\"
strSQL = "SELECT [DECO Import File].PortfolioName, [Counterparty Data].CPEmail" & vbLf & _
"FROM [Counterparty Data] INNER JOIN [DECO Import File] ON [Counterparty Data].PortfolioName = [DECO Import File].PortfolioName" & vbLf & _
"WHERE ((([DECO Import File].[Collateral To Receive])>0));"
Set rst = CurrentDb.OpenRecordset(strSQL)
While Not (rst.BOF And rst.EOF)
stSubject = "Collateral Demand - " & rst.Fields(0).Value
stCaption = stSubject & Format(Now(), " mm-dd-yyyy")
RewriteQuerySQL "Report Query", rst.Fields(0).Value
DoCmd.OpenQuery "Report Query"
DoCmd.Close acQuery, "Report Query"
DoCmd.OpenReport "Collateral Demand", acViewNormal
DoCmd.SendObject acSendReport, stReport, acFormatPDF, rst.Fields(1).Value, "derivcollateral@rgare.com", , stSubject, stEmailMessage, True, ""
DoCmd.OutputTo acOutputReport, stReport, acFormatPDF, myPath & stCaption & ".pdf", False, , , acExportQualityPrint
rst.MoveNext
Wend

End Sub

Sub RewriteQuerySQL(strQueryName As String, strParameter As String)

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs(strQueryName)

qdf.SQL = "SELECT [DECO Import File].CurrentDate, [DECO Import File].COBDate, [Counterparty Data].BrokerName, [Counterparty Data].PortfolioName, [DECO Import File].Exposure, [DECO Import File].[Support Req], [DECO Import File].[Collateral Pledged/Held], [DECO Import File].[Minimum Transfer Amt (Broker)], [DECO Import File].[Collateral To Receive], [Counterparty Data].BankName, [Counterparty Data].[CashAccount#], [Counterparty Data].CashAccountName, [Counterparty Data].CashABA, [Counterparty Data].[SecuritiesAccount#], [Counterparty Data].SecuritiesAccountName, [Counterparty Data].SecuritiesABA, [Counterparty Data].CPEmail" & vbCrLf & _
"FROM [Counterparty Data] INNER JOIN [DECO Import File] ON [Counterparty Data].Combo = [DECO Import File].Combo" & vbCrLf & _
"WHERE ((([DECO Import File].[Collateral To Receive])>0) AND (([Counterparty Data].Combo)=" & Chr(34) & strParameter & Chr(34) & "));"

End Sub

[Excel Fox]

"DECO Import File" is the name of your report. You can only change that if you change that in the database itself.

[Excel Fox]

There is NO "Collateral Demand" report in your database. Did you modify that in the database itself?

[vnwallace]

I have restored my database back to the original version and used the previous code you gave me. Now from here can you help me rename my report?

Private Sub Command11_Click()

'This will print the reports
Dim stEmailMessage As String
Dim stReport As String
Dim stCaption As String
Dim myPath As String
Dim strSQL As String
Dim stSubject As String
Dim rst As Recordset

'This sets the strings for each part of the email
stEmailMessage = "Please see the attached collateral call for today."
stReport = "DECO Import File"
'This will change the caption of the report (which is what the filename is saved as" to something meaningful - in this case the numbers and date of thereport. If you have multiple people making these reports at once, and would have multiple ones with the same date/time stamp, you should modify this a little to prevent overwriting files.

myPath = "J:\Investments\Trade Operations\Derivative Transactions\Collateral\Access Database\"
strSQL = "SELECT [DECO Import File].Combo, [Counterparty Data].CPEmail" & vbLf & _
"FROM [Counterparty Data] INNER JOIN [DECO Import File] ON [Counterparty Data].Combo = [DECO Import File].Combo" & vbLf & _
"WHERE ((([DECO Import File].[Collateral To Receive])>0));"
Set rst = CurrentDb.OpenRecordset(strSQL)
While Not (rst.BOF And rst.EOF)
stSubject = "Collateral Demand " & rst.Fields(0).Value
stCaption = stSubject & Format(Now(), " mm-dd-yyyy")
RewriteQuerySQL "Report Query", rst.Fields(0).Value
DoCmd.OpenQuery "Report Query"
DoCmd.Close acQuery, "Report Query"
DoCmd.OpenReport "DECO Import File", acViewNormal
DoCmd.SendObject acSendReport, stReport, acFormatPDF, rst.Fields(1).Value, "derivcollateral@rgare.com", , stSubject, stEmailMessage, True, ""
DoCmd.OutputTo acOutputReport, stReport, acFormatPDF, myPath & stCaption & ".pdf", False, , , acExportQualityPrint
rst.MoveNext
Wend

End Sub

Sub RewriteQuerySQL(strQueryName As String, strParameter As String)

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs(strQueryName)

qdf.SQL = "SELECT [DECO Import File].CurrentDate, [DECO Import File].COBDate, [Counterparty Data].BrokerName, [Counterparty Data].PortfolioName, [DECO Import File].Exposure, [DECO Import File].[Support Req], [DECO Import File].[Collateral Pledged/Held], [DECO Import File].[Minimum Transfer Amt (Broker)], [DECO Import File].[Collateral To Receive], [Counterparty Data].BankName, [Counterparty Data].[CashAccount#], [Counterparty Data].CashAccountName, [Counterparty Data].CashABA, [Counterparty Data].[SecuritiesAccount#], [Counterparty Data].SecuritiesAccountName, [Counterparty Data].SecuritiesABA, [Counterparty Data].CPEmail" & vbCrLf & _
"FROM [Counterparty Data] INNER JOIN [DECO Import File] ON [Counterparty Data].Combo = [DECO Import File].Combo" & vbCrLf & _
"WHERE ((([DECO Import File].[Collateral To Receive])>0) AND (([Counterparty Data].Combo)=" & Chr(34) & strParameter & Chr(34) & "));"

End Sub

[Excel Fox]

OK, so in the original state, is my code working as you asked for? Except the report name?

[vnwallace]

Yes, I had modified the database but changed it back to original version after things started to get messed up. Here are the few changes I would like to make and when I do, the database messes up and eliminates data from the reports.

1. I would like to change the report name in my DB to "Collateral Demand" and then for the report to specify that. Here is a link to the database I am currently looking at. It shouldn't be any different than yours but just in case. Link: Collateral Database Test - Download - 4shared - Valerie Wallace (http://www.4shared.com/zip/tf2e5DFL/Collateral_Database_Test.html)
2. I would like to change the subject so it references the "PortfolioName". I have added a column in my DECO Import File that will match the data in the "Counterparty Data" table. Here is the link to the new file: DECO Import File - Download - 4shared - Valerie Wallace (http://www.4shared.com/file/TXymSwbY/DECO_Import_File.html)

Any help would be greatly appreciated!

[vnwallace]

Yes, the original code is working except for the 2 points listed in my message at 2:27 PM (CST). Thank you again for all your help!

[vnwallace]

Also, I realize that when changing the subject to reference the Portfolio Name it will be hard when the file saves because it won't have a unique identifier separate from the others.. any thoughts?

[vnwallace]

Just checking to make sure you were able to receive the more recent files? Thanks so much again, it's greatly appreciated!!

[Excel Fox]

You don't need to add that PortfolioName column in the Excel file. It is already there in the CounterParty Data table within the Access database. Here, try this.



Option Compare Database

Private Sub Command11_Click()

'This will print the reports
Dim stEmailMessage As String
Dim stReport As String
Dim stCaption As String
Dim myPath As String
Dim strSQL As String
Dim stSubject As String
Dim rst As Recordset

'This sets the strings for each part of the email
stEmailMessage = "Please see the attached collateral call for today."
stReport = "Collateral Demand"
'This will change the caption of the report (which is what the filename is saved as" to something meaningful - in this case the numbers and date of thereport. If you have multiple people making these reports at once, and would have multiple ones with the same date/time stamp, you should modify this a little to prevent overwriting files.

myPath = "J:\Investments\Trade Operations\Derivative Transactions\Collateral\Access Database\"
strSQL = "SELECT [DECO Import File].Combo, [Counterparty Data].CPEmail, [Counterparty Data].PortfolioName" & vbLf & _
"FROM [Counterparty Data] INNER JOIN [DECO Import File] ON [Counterparty Data].Combo = [DECO Import File].Combo" & vbLf & _
"WHERE ((([DECO Import File].[Collateral To Receive])>0));"
Set rst = CurrentDb.OpenRecordset(strSQL)
While Not (rst.BOF And rst.EOF)
stSubject = "Collateral Demand - " & rst.Fields(2).Value
stCaption = stSubject & Format(Now(), " mm-dd-yyyy")
RewriteQuerySQL "Report Query", rst.Fields(0).Value
DoCmd.OpenQuery "Report Query"
DoCmd.Close acQuery, "Report Query"
DoCmd.OpenReport "Collateral Demand", acViewNormal
DoCmd.SendObject acSendReport, stReport, acFormatPDF, rst.Fields(1).Value, "derivcollateral@rgare.com", , stSubject, stEmailMessage, True, ""
DoCmd.OutputTo acOutputReport, stReport, acFormatPDF, myPath & stCaption & ".pdf", False, , , acExportQualityPrint
rst.MoveNext
Wend

End Sub

Sub RewriteQuerySQL(strQueryName As String, strParameter As String)

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs(strQueryName)

qdf.SQL = "SELECT [DECO Import File].CurrentDate, [DECO Import File].COBDate, [Counterparty Data].BrokerName, [Counterparty Data].PortfolioName, [DECO Import File].Exposure, [DECO Import File].[Support Req], [DECO Import File].[Collateral Pledged/Held], [DECO Import File].[Minimum Transfer Amt (Broker)], [DECO Import File].[Collateral To Receive], [Counterparty Data].BankName, [Counterparty Data].[CashAccount#], [Counterparty Data].CashAccountName, [Counterparty Data].CashABA, [Counterparty Data].[SecuritiesAccount#], [Counterparty Data].SecuritiesAccountName, [Counterparty Data].SecuritiesABA, [Counterparty Data].CPEmail" & vbCrLf & _
"FROM [Counterparty Data] INNER JOIN [DECO Import File] ON [Counterparty Data].Combo = [DECO Import File].Combo" & vbCrLf & _
"WHERE ((([DECO Import File].[Collateral To Receive])>0) AND (([Counterparty Data].Combo)=" & Chr(34) & strParameter & Chr(34) & "));"

End Sub

[vnwallace]

Okay the report file attached to the email is still named "DECO Import File" after I inserted your code and changed the report title. Also, since the file does not have a completely unique Identifier and since some titles are the same, some files do not save. (PortfolioName is the same for some demands.. the only thing completely unique is Combo except for that is not an identifier I would like the counterparties to see on their emails) Would it be possible to have the subject & saved file be a combinantion of "PortfolioName" - "BrokerName" (including date) so that these can save properly?

[Excel Fox]

Are you saying that the name of the PDF file attached to the email is still named DECO Import File? Can you show this to me via teamviewer?

For the other requirement, use this



Private Sub Command11_Click()

'This will print the reports
Dim stEmailMessage As String
Dim stReport As String
Dim stCaption As String
Dim myPath As String
Dim strSQL As String
Dim stSubject As String
Dim rst As Recordset

'This sets the strings for each part of the email
stEmailMessage = "Please see the attached collateral call for today."
stReport = "Collateral Demand"
'This will change the caption of the report (which is what the filename is saved as" to something meaningful - in this case the numbers and date of thereport. If you have multiple people making these reports at once, and would have multiple ones with the same date/time stamp, you should modify this a little to prevent overwriting files.

myPath = "J:\Investments\Trade Operations\Derivative Transactions\Collateral\Access Database\"
strSQL = "SELECT [DECO Import File].Combo, [Counterparty Data].CPEmail, [Counterparty Data].PortfolioName, [Counterparty Data].BrokerName" & vbLf & _
"FROM [Counterparty Data] INNER JOIN [DECO Import File] ON [Counterparty Data].Combo = [DECO Import File].Combo" & vbLf & _
"WHERE ((([DECO Import File].[Collateral To Receive])>0));"
Set rst = CurrentDb.OpenRecordset(strSQL)
While Not (rst.BOF And rst.EOF)
stSubject = "Collateral Demand - " & rst.Fields(2).Value & " - " & rst.Fields(3).Value & Format(Now(), " mm-dd-yyyy")
stCaption = stSubject
RewriteQuerySQL "Report Query", rst.Fields(0).Value
DoCmd.OpenQuery "Report Query"
DoCmd.Close acQuery, "Report Query"
DoCmd.OpenReport "Collateral Demand", acViewNormal
DoCmd.SendObject acSendReport, stReport, acFormatPDF, rst.Fields(1).Value, "derivcollateral@rgare.com", , stSubject, stEmailMessage, True, ""
DoCmd.OutputTo acOutputReport, stReport, acFormatPDF, myPath & stCaption & ".pdf", False, , , acExportQualityPrint
rst.MoveNext
Wend

End Sub

[vnwallace]

That definitely fixes the issue for saving the file and the subject so thank you very much!! My computer at work does not allow for this software to run so I am putting the link below to a screenshot of what I see. Please keep in mind that I deleted out the email addresses manually, these are working correctly.

Screenshot - Download - 4shared - Valerie Wallace (http://www.4shared.com/file/MXZohj_y/Screenshot.html)

[Excel Fox]

OK, can you try this



Private Sub Command11_Click()

'This will print the reports
Dim stEmailMessage As String
Dim stReport As String
Dim stCaption As String
Dim myPath As String
Dim strSQL As String
Dim stSubject As String
Dim rst As Recordset
Dim objRp As Report

'This sets the strings for each part of the email
stEmailMessage = "Please see the attached collateral call for today."
stReport = "Collateral Demand"
'This will change the caption of the report (which is what the filename is saved as" to something meaningful - in this case the numbers and date of thereport. If you have multiple people making these reports at once, and would have multiple ones with the same date/time stamp, you should modify this a little to prevent overwriting files.

myPath = "J:\Investments\Trade Operations\Derivative Transactions\Collateral\Access Database\"
strSQL = "SELECT [DECO Import File].Combo, [Counterparty Data].CPEmail, [Counterparty Data].PortfolioName, [Counterparty Data].BrokerName" & vbLf & _
"FROM [Counterparty Data] INNER JOIN [DECO Import File] ON [Counterparty Data].Combo = [DECO Import File].Combo" & vbLf & _
"WHERE ((([DECO Import File].[Collateral To Receive])>0));"
Set rst = CurrentDb.OpenRecordset(strSQL)
Set objRp = Reports(stReport)
While Not (rst.BOF And rst.EOF)
stSubject = "Collateral Demand - " & rst.Fields(2).Value & " - " & rst.Fields(3).Value & Format(Now(), " mm-dd-yyyy")
stCaption = stSubject
objRp.Caption = stCaption
RewriteQuerySQL "Report Query", rst.Fields(0).Value
DoCmd.OpenQuery "Report Query"
DoCmd.Close acQuery, "Report Query"
DoCmd.OpenReport "Collateral Demand", acViewNormal
DoCmd.SendObject acSendReport, stReport, acFormatPDF, rst.Fields(1).Value, "derivcollateral@rgare.com", , stSubject, stEmailMessage, True, ""
DoCmd.OutputTo acOutputReport, stReport, acFormatPDF, myPath & stCaption & ".pdf", False, , , acExportQualityPrint
rst.MoveNext
Wend

End Sub

[vnwallace]

It says it is referring to a report that doesn't exist but I have made sure the report is named exactly the same. Here is a link to my updated database. Thanks!!

Collateral Database Test2 - Download - 4shared - Valerie Wallace (http://www.4shared.com/zip/CVQ_dr7x/Collateral_Database_Test2.html)

[Excel Fox]

Don't know what's wrong with your database, but it's working mighty cool here. Use this

[vnwallace]

I still cannot get this to work.. it keeps saying

Run-time error '2451': The report name 'Collateral Demand' you entered is misspelled or refers to a report that isn't open or doesn't exist.

I used your DB and just changed the linking DECO Import File and I still get this error.. any ideas?

[Excel Fox]

Out of idea. Try using

Set objRp = Reports(1) instead of
Set objRp = Reports(stReport)

[Excel Fox]

in fact, it should be

Set objRp = Reports(0)

[vnwallace]

It now says

Run-time error '2457': The number you used to refer to the report is invalid

[vnwallace]

I used
Set objRp = Reports(0) which still doesn't work

[Excel Fox]

Until I figure out what can be done, leaving this open to any other developer who can support.

[Excel Fox]

One last attempt. Very late here, gotta get to bed.



Private Sub Command11_Click()

'This will print the reports
Dim stEmailMessage As String
Dim stReport As String
Dim stCaption As String
Dim myPath As String
Dim strSQL As String
Dim stSubject As String
Dim rst As Recordset
Dim objRp As Report

'This sets the strings for each part of the email
stEmailMessage = "Please see the attached collateral call for today."
stReport = "Collateral Demand"
'This will change the caption of the report (which is what the filename is saved as" to something meaningful - in this case the numbers and date of thereport. If you have multiple people making these reports at once, and would have multiple ones with the same date/time stamp, you should modify this a little to prevent overwriting files.

myPath = "J:\Investments\Trade Operations\Derivative Transactions\Collateral\Access Database\"
strSQL = "SELECT [DECO Import File].Combo, [Counterparty Data].CPEmail, [Counterparty Data].PortfolioName, [Counterparty Data].BrokerName" & vbLf & _
"FROM [Counterparty Data] INNER JOIN [DECO Import File] ON [Counterparty Data].Combo = [DECO Import File].Combo" & vbLf & _
"WHERE ((([DECO Import File].[Collateral To Receive])>0));"
Set rst = CurrentDb.OpenRecordset(strSQL)

While Not (rst.BOF And rst.EOF)
stSubject = "Collateral Demand - " & rst.Fields(2).Value & " - " & rst.Fields(3).Value & Format(Now(), " mm-dd-yyyy")
stCaption = stSubject
RewriteQuerySQL "Report Query", rst.Fields(0).Value
DoCmd.OpenQuery "Report Query"
DoCmd.Close acQuery, "Report Query"
DoCmd.OpenReport stReport, acViewPreview
DoCmd.OpenReport stReport, acViewNormal
Reports(stReport).Caption = stCaption
DoCmd.SendObject acSendReport, stReport, acFormatPDF, rst.Fields(1).Value, "derivcollateral@rgare.com", , stSubject, stEmailMessage, True, ""
DoCmd.OutputTo acOutputReport, stReport, acFormatPDF, myPath & stCaption & ".pdf", False, , , acExportQualityPrint
DoCmd.Close acReport, stReport
rst.MoveNext
Wend

End Sub

[vnwallace]

This works!! Thank you very much for all of your help!

[vnwallace]

I have another question... In this sequence, I need the error handler to go to the next record if I so choose to exit out of one of the emails (I do not want to send one some days). Any ideas? Also, after the records are through, I want to clear the macro error and bring up a message box. I have the clear macro error and message box done but I think the sequence will need to change since sometimes I need to close out of an email.


Private Sub Command11_Click()

On Error GoTo ErrorHandler

Dim stEmailMessage As String
Dim stReport As String
Dim stCaption As String
Dim myPath As String
Dim strSQL As String
Dim stSubject As String
Dim rst As Recordset
Dim objRp As Report
'This sets the strings for each part of the email

stEmailMessage = "Please see the attached collateral call for today." & vbNewLine & vbNewLine & "Thanks!" & vbNewLine & "Derivative Collateral Team" & vbNewLine & "derivcollateral@rgare.com" & vbNewLine & vbNewLine & "This message is intended only for the use of the individual or entity to which it is addressed, and may contain information that is privileged, confidential, and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by telephone and return the original message to its sender. Thank you for your cooperation and assistance."
'States subject of email

stReport = "Collateral Demand"
'This will change the caption of the report (which is what the filename is saved as" to something meaningful - in this case the date of thereport.

myPath = "J:\Investments\Trade Operations\Derivative Transactions\Collateral\Access Database\Daily Demands\"
'This sets path for PDF file to save to
strSQL = "SELECT [DECO Import File].Combo, [Counterparty Data].CPEmail, [Counterparty Data].PortfolioName, [Counterparty Data].BrokerName" & vbLf & _
"FROM [Counterparty Data] INNER JOIN [DECO Import File] ON [Counterparty Data].Combo = [DECO Import File].Combo" & vbLf & _
"WHERE ((([DECO Import File].[Collateral To Receive])>0));"
Set rst = CurrentDb.OpenRecordset(strSQL)
'Makes file a recordset

While Not (rst.BOF And rst.EOF)
stSubject = "Collateral Demand - " & rst.Fields(2).Value & " vs. " & rst.Fields(3).Value & Format(Now(), " mm-dd-yyyy")
stCaption = stSubject
RewriteQuerySQL "Report Query", rst.Fields(0).Value
DoCmd.OpenQuery "Report Query"
DoCmd.Close acQuery, "Report Query"
DoCmd.OpenReport stReport, acViewPreview
DoCmd.OpenReport stReport, acViewNormal
Reports(stReport).Caption = stCaption
DoCmd.SendObject acSendReport, stReport, acFormatPDF, rst.Fields(1).Value, "derivcollateral@rgare.com", , stSubject, stEmailMessage, True, ""
DoCmd.OutputTo acOutputReport, stReport, acFormatPDF, myPath & stCaption & ".pdf", False, , , acExportQualityPrint
DoCmd.Close acReport, stReport
rst.MoveNext

Wend

'this loops through recordset to send emails & print & save

ErrorHandler:
DoCmd.ClearMacroError
MsgBox ("Demands Complete!")

End Sub



Sub RewriteQuerySQL(strQueryName As String, strParameter As String)

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs(strQueryName)

qdf.SQL = "SELECT [DECO Import File].CurrentDate, [DECO Import File].COBDate, [Counterparty Data].BrokerName, [Counterparty Data].PortfolioName, [DECO Import File].Exposure, [DECO Import File].[Support Req], [DECO Import File].[Collateral Pledged/Held], [DECO Import File].[Minimum Transfer Amt (Broker)], [DECO Import File].[Collateral To Receive], [Counterparty Data].BankName, [Counterparty Data].[CashAccount#], [Counterparty Data].CashAccountName, [Counterparty Data].CashABA, [Counterparty Data].[SecuritiesAccount#], [Counterparty Data].SecuritiesAccountName, [Counterparty Data].SecuritiesABA, [Counterparty Data].CPEmail" & vbCrLf & _
"FROM [Counterparty Data] INNER JOIN [DECO Import File] ON [Counterparty Data].Combo = [DECO Import File].Combo" & vbCrLf & _
"WHERE ((([DECO Import File].[Collateral To Receive])>0) AND (([Counterparty Data].Combo)=" & Chr(34) & strParameter & Chr(34) & "));"

End Sub

Sub ReturntoForm()
DoCmd.OpenForm (CollateralDB)
DoCmd.ClearMacroError
End Sub


Private Sub Command29_Click()
If MsgBox("Are you sure you want to close the database?", vbQuestion + vbYesNo, "Close Database") = vbYes Then
DoCmd.CloseDatabase
Else
DoCmd.CancelEvent
End If
End Sub


Thanks!