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

**Excel Fox** · 08-21-2013, 01:00 AM

OK, 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
    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** · 08-21-2013, 01:07 AM

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

**Excel Fox** · 08-21-2013, 01:18 AM

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

**vnwallace** · 08-21-2013, 01:27 AM

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

Out of idea. Try using

Code:

Set objRp = Reports(1)

instead of

Code:

Set objRp = Reports(stReport)

**Excel Fox** · 08-21-2013, 01:36 AM

in fact, it should be

Code:

Set objRp = Reports(0)

**vnwallace** · 08-21-2013, 01:43 AM

It now says

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

**vnwallace** · 08-21-2013, 01:44 AM

I used

Code:

Set objRp = Reports(0)

which still doesn't work

**Excel Fox** · 08-21-2013, 01:55 AM

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

**Excel Fox** · 08-21-2013, 02:11 AM

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

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