Page 2 of 5 FirstFirst 1234 ... LastLast
Results 11 to 20 of 42

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

  1. #11
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    OK, this seems to be working, but I haven't tested the mailing part. 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
    
        '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.
        stCaption = "Collateral Demand" & Format(Now(), " mm-dd-yyyy")
    
        myPath = "J:\Investments\Trade Operations\Derivative Transactions\Collateral\Access Database\"
        strSQL = "SELECT [DECO Import File].Combo" & 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
            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, , , , 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
    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. #12
    Junior Member
    Join Date
    Aug 2013
    Posts
    23
    Rep Power
    0
    Thanks!! There are a few things that I need help with.. I need to add a "To" address to the emails that gather from the table and a "CC" address of derivcollateral@rgare.com. Also, I need either the entire report file or each individually to save to my designated folder and this time only one of them did so. Thank you SO much for all of your time and effort in this.. It's truly appreciated!

  3. #13
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    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
    
        '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
    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. #14
    Junior Member
    Join Date
    Aug 2013
    Posts
    23
    Rep Power
    0
    Thank you this works great! I have now changed the Report title to "Collateral Demand" but I cannot seem to get the PDF to change to that title on the attachment in the email. I have my changed code below.. any ideas?

    Code:
    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].PortfolioName, [Counterparty Data].CPEmail" & vbLf & _
                "FROM [Counterparty Data] INNER JOIN [DECO Import File] ON [Counterparty Data].PortfolioName = [DECO Import File].PortfolioName" & 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 "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

  5. #15
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    All I can see is that you changed
    Code:
    stReport = "DECO Import File"
    to

    Code:
    stReport = "Collateral Demand"
    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. #16
    Junior Member
    Join Date
    Aug 2013
    Posts
    23
    Rep Power
    0
    I also changed
    Code:
    DoCmd.OpenReport "Collateral Demand", acViewNormal
    How do I get my report title to change on the email? I think there is something that I am missing. Thank you again!

  7. #17
    Junior Member
    Join Date
    Aug 2013
    Posts
    23
    Rep Power
    0
    Also I notice now that when I see the emails there is not actual data in there with the below code. It takes out all of the fill ins.

    Code:
    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." & (Chr(13) + Chr(10)) & (Chr(13) + Chr(10)) & "Thanks!" & (Chr(13) + Chr(10)) & "Derivative Collateral Team" & (Chr(13) + Chr(10)) & "derivcollateral@rgare.com" & (Chr(13) + Chr(10)) & (Chr(13) + Chr(10)) & "This message is intended only for the use of the individual or entity to which it is addressed, and may contain information that is privileged, confidential, and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by telephone and return the original message to its sender. Thank you for your cooperation and assistance."
        
        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].PortfolioName, [Counterparty Data].CPEmail" & vbLf & _
                "FROM [Counterparty Data] INNER JOIN [DECO Import File] ON [Counterparty Data].PortfolioName = [DECO Import File].PortfolioName" & 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 "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
    Last edited by Excel Fox; 08-19-2013 at 11:57 PM. Reason: Corrected code tag

  8. #18
    Junior Member
    Join Date
    Aug 2013
    Posts
    23
    Rep Power
    0
    Apologies, here is the tagged code:

    Code:
    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." & (Chr(13) + Chr(10)) & (Chr(13) + Chr(10)) & "Thanks!" & (Chr(13) + Chr(10)) & "Derivative Collateral Team" & (Chr(13) + Chr(10)) & "derivcollateral@rgare.com" & (Chr(13) + Chr(10)) & (Chr(13) + Chr(10)) & "This message is intended only for the use of the individual or entity to which it is addressed, and may contain information that is privileged, confidential, and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by telephone and return the original message to its sender. Thank you for your cooperation and assistance."
        
        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].PortfolioName, [Counterparty Data].CPEmail" & vbLf & _
                "FROM [Counterparty Data] INNER JOIN [DECO Import File] ON [Counterparty Data].PortfolioName = [DECO Import File].PortfolioName" & 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 "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

  9. #19
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    "DECO Import File" is the name of your report. You can only change that if you change that in the database itself.
    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. #20
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    There is NO "Collateral Demand" report in your database. Did you modify that in the database itself?
    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
  •