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

**vnwallace** · 08-19-2013, 11:59 PM

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?

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

**Excel Fox** · 08-20-2013, 12:14 AM

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

**vnwallace** · 08-20-2013, 12:27 AM

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

Any help would be greatly appreciated!

**vnwallace** · 08-20-2013, 12:32 AM

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** · 08-20-2013, 01:54 AM

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** · 08-20-2013, 09:30 PM

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

**Excel Fox** · 08-20-2013, 11:59 PM

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.

Code:

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** · 08-21-2013, 12:20 AM

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** · 08-21-2013, 12:32 AM

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

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 = "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** · 08-21-2013, 12:43 AM

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