Page 5 of 5 FirstFirst ... 345
Results 41 to 42 of 42

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

  1. #41
    Junior Member
    Join Date
    Aug 2013
    Posts
    23
    Rep Power
    0
    This works!! Thank you very much for all of your help!

  2. #42
    Junior Member
    Join Date
    Aug 2013
    Posts
    23
    Rep Power
    0
    I have another question... In this sequence, I need the error handler to go to the next record if I so choose to exit out of one of the emails (I do not want to send one some days). Any ideas? Also, after the records are through, I want to clear the macro error and bring up a message box. I have the clear macro error and message box done but I think the sequence will need to change since sometimes I need to close out of an email.

    Code:
    Private Sub Command11_Click()
    
    On Error GoTo ErrorHandler
    
        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." & vbNewLine & vbNewLine & "Thanks!" & vbNewLine & "Derivative Collateral Team" & vbNewLine & "derivcollateral@rgare.com" & vbNewLine & vbNewLine & "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."
            'States subject of email
        
        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 date of thereport.
    
        myPath = "J:\Investments\Trade Operations\Derivative Transactions\Collateral\Access Database\Daily Demands\"
            'This sets path for PDF file to save to
        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)
            'Makes file a recordset
        
        While Not (rst.BOF And rst.EOF)
            stSubject = "Collateral Demand - " & rst.Fields(2).Value & " vs. " & 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
            
            'this loops through recordset to send emails & print & save
        
    ErrorHandler:
        DoCmd.ClearMacroError
        MsgBox ("Demands Complete!")
        
    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
    
    Sub ReturntoForm()
    DoCmd.OpenForm (CollateralDB)
    DoCmd.ClearMacroError
    End Sub
    
    
    Private Sub Command29_Click()
    If MsgBox("Are you sure you want to close the database?", vbQuestion + vbYesNo, "Close Database") = vbYes Then
    DoCmd.CloseDatabase
    Else
    DoCmd.CancelEvent
    End If
    End Sub

    Thanks!

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
  •