Page 4 of 5 FirstFirst ... 2345 LastLast
Results 31 to 40 of 42

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

  1. #31
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  2. #32
    Junior Member
    Join Date
    Aug 2013
    Posts
    23
    Rep Power
    0
    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

  3. #33
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Don't know what's wrong with your database, but it's working mighty cool here. Use this
    Attached Files Attached Files
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  4. #34
    Junior Member
    Join Date
    Aug 2013
    Posts
    23
    Rep Power
    0
    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?

  5. #35
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Out of idea. Try using
    Code:
    Set objRp = Reports(1)
    instead of
    Code:
    Set objRp = Reports(stReport)
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  6. #36
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    in fact, it should be
    Code:
    Set objRp = Reports(0)
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  7. #37
    Junior Member
    Join Date
    Aug 2013
    Posts
    23
    Rep Power
    0
    It now says

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

  8. #38
    Junior Member
    Join Date
    Aug 2013
    Posts
    23
    Rep Power
    0
    I used
    Code:
    Set objRp = Reports(0)
    which still doesn't work

  9. #39
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Until I figure out what can be done, leaving this open to any other developer who can support.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  10. #40
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

Similar Threads

  1. VBA To Extract Email Address From Text
    By dunndealpr in forum Excel Help
    Replies: 43
    Last Post: 06-05-2019, 03:56 PM
  2. Converting text to an email address hyperlink
    By Aerodynamix in forum Excel Help
    Replies: 2
    Last Post: 08-09-2013, 09:56 PM
  3. Replies: 2
    Last Post: 05-23-2013, 08:08 AM
  4. Replies: 6
    Last Post: 05-20-2013, 10:06 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •