-
OK, this seems to be working, but I haven't tested the mailing part. Can you try this
Code:
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
-
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!
-
Try this
Code:
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
-
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?
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."
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
-
All I can see is that you changed
Code:
stReport = "DECO Import File"
to
Code:
stReport = "Collateral Demand"
-
I also changed
Code:
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!
-
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.
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
-
Apologies, here is the tagged code:
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
-
"DECO Import File" is the name of your report. You can only change that if you change that in the database itself.
-
There is NO "Collateral Demand" report in your database. Did you modify that in the database itself?
