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

[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