Page 3 of 5 FirstFirst 12345 LastLast
Results 21 to 30 of 42

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

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

  2. #22
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    OK, so in the original state, is my code working as you asked for? Except the report name?
    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

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

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

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

  6. #26
    Junior Member
    Join Date
    Aug 2013
    Posts
    23
    Rep Power
    0
    Just checking to make sure you were able to receive the more recent files? Thanks so much again, it's greatly appreciated!!

  7. #27
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    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
    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

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

  9. #29
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    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
    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. #30
    Junior Member
    Join Date
    Aug 2013
    Posts
    23
    Rep Power
    0
    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

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
  •