none
Need to attach several PDFs from several reports to single email . . . RRS feed

  • Question

  • I am able to attach a single PDF with selection criteria to an email with the code below . . .is there a way to chain multiple reports with the same selection criteria to a single email with either separate PDFs as attachments, or a single PDF of multiple pages?

    Private Sub EmailContract_Click()

    On Error GoTo Err_EmailContract_Click

        Dim stDocName As String

        Dim stWhere As String

        Dim stEmailadd As String

        stWhere = "[HunterID] = " & Me![HunterID]

       

        stDocName = "rptHunterContract"

        stEmailadd = Me.emailfld

        DoCmd.OpenReport stDocName, acViewPreview, "", stWhere, acWindowNormal, ""

        DoCmd.SendObject acReport, stDocName, acFormatPDF, stEmailadd, , , "Invoice", "Please find your Invoice attached", True

    Exit_EmailContract_Click:

        Exit Sub

    Err_EmailContract_Click:

        MsgBox Err.Description

        Resume Exit_EmailContract_Click

    End Sub

    Wednesday, September 18, 2019 3:39 PM

Answers

  • You might like to take a look at InvoicePDF.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file includes an option for emailing multiple invoices to a customer as a single PDF file.  The email's subject line is built as a list of the attached invoices.  Note that the report's query references a hidden text box in the calling dialogue form as a parameter, using the InParam and GetToken functions published by Microsoft many years ago.  The hidden text box contains a comma-separated list of the selected invoice numbers.

    Ken Sheridan, Stafford, England

    • Marked as answer by Big Itch Thursday, September 19, 2019 12:19 PM
    Wednesday, September 18, 2019 4:50 PM
  • No, not using SendObject.  You'll need to turn towards using Outlook automation, CDO mail, ...

    If you want to use Outlook automation, the following function may be useful

    http://www.devhut.net/2010/09/03/vba-send-html-emails-using-outlook-automation/


    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    • Marked as answer by Big Itch Thursday, September 19, 2019 12:20 PM
    Wednesday, September 18, 2019 11:20 PM
  • Hi Big

    When you have the pdf:s in a folder. Use this code (got it from some one in this forum):

    Private Sub CmdButton1_Click()
    On Error GoTo MailFile_Err
        Dim mess_body As String, StrFile As String, StrPath As String
        Dim appOutLook As Object
        Dim MailOutLook As Object
        
        Set appOutLook = CreateObject("Outlook.Application")
        Set MailOutLook = appOutLook.CreateItem(0)
    
        '~~> Change path here
        'StrPath = "C:\fbw\" ‘ make it your own
    '
        With MailOutLook
            '.BodyFormat = olFormatRichText
            .To = ""
            .Subject = "YOUR SUBJETCT " & IIf(IsNull([Forms]![report]![UNIVMONTH]), Me.DateInDaylyFileName, StrConv(MonthName([Forms]![report]![UNIVMONTH]), 3) & " " & [Forms]![report]![UNIVYEAR])
            '.HTMLBody = "YOUR TEXT"
    
            '~~> *.* for all files
    ‘ only pdf? See below
            StrFile = Dir(StrPath & "*.pdf")
    
            Do While Len(StrFile) > 0
                .Attachments.Add StrPath & StrFile
                StrFile = Dir
            Loop
            .display
            '.DeleteAfterSubmit = True
            '.Send
        End With
        'MsgBox "Reports have been attached to email", vbOKOnly    
    MailFile_Exit:
        Exit Sub
    
    MailFile_Err:
        MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: Kommandoknapp11_Click" & vbCrLf & _
               "Error Description: " & Err.description _
               , vbOKOnly + vbCritical, "An Error has Occured!"   
        Resume MailFile_Exit
    End Sub
    


    Cheers // Peter Forss Stockholm

    • Marked as answer by Big Itch Thursday, September 19, 2019 12:21 PM
    Thursday, September 19, 2019 5:33 AM

All replies

  • You might like to take a look at InvoicePDF.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file includes an option for emailing multiple invoices to a customer as a single PDF file.  The email's subject line is built as a list of the attached invoices.  Note that the report's query references a hidden text box in the calling dialogue form as a parameter, using the InParam and GetToken functions published by Microsoft many years ago.  The hidden text box contains a comma-separated list of the selected invoice numbers.

    Ken Sheridan, Stafford, England

    • Marked as answer by Big Itch Thursday, September 19, 2019 12:19 PM
    Wednesday, September 18, 2019 4:50 PM
  • No, not using SendObject.  You'll need to turn towards using Outlook automation, CDO mail, ...

    If you want to use Outlook automation, the following function may be useful

    http://www.devhut.net/2010/09/03/vba-send-html-emails-using-outlook-automation/


    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    • Marked as answer by Big Itch Thursday, September 19, 2019 12:20 PM
    Wednesday, September 18, 2019 11:20 PM
  • Hi Big

    When you have the pdf:s in a folder. Use this code (got it from some one in this forum):

    Private Sub CmdButton1_Click()
    On Error GoTo MailFile_Err
        Dim mess_body As String, StrFile As String, StrPath As String
        Dim appOutLook As Object
        Dim MailOutLook As Object
        
        Set appOutLook = CreateObject("Outlook.Application")
        Set MailOutLook = appOutLook.CreateItem(0)
    
        '~~> Change path here
        'StrPath = "C:\fbw\" ‘ make it your own
    '
        With MailOutLook
            '.BodyFormat = olFormatRichText
            .To = ""
            .Subject = "YOUR SUBJETCT " & IIf(IsNull([Forms]![report]![UNIVMONTH]), Me.DateInDaylyFileName, StrConv(MonthName([Forms]![report]![UNIVMONTH]), 3) & " " & [Forms]![report]![UNIVYEAR])
            '.HTMLBody = "YOUR TEXT"
    
            '~~> *.* for all files
    ‘ only pdf? See below
            StrFile = Dir(StrPath & "*.pdf")
    
            Do While Len(StrFile) > 0
                .Attachments.Add StrPath & StrFile
                StrFile = Dir
            Loop
            .display
            '.DeleteAfterSubmit = True
            '.Send
        End With
        'MsgBox "Reports have been attached to email", vbOKOnly    
    MailFile_Exit:
        Exit Sub
    
    MailFile_Err:
        MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: Kommandoknapp11_Click" & vbCrLf & _
               "Error Description: " & Err.description _
               , vbOKOnly + vbCritical, "An Error has Occured!"   
        Resume MailFile_Exit
    End Sub
    


    Cheers // Peter Forss Stockholm

    • Marked as answer by Big Itch Thursday, September 19, 2019 12:21 PM
    Thursday, September 19, 2019 5:33 AM
  • Thanks . . . I'll see if that works for my application.
    Thursday, September 19, 2019 12:20 PM
  • Thanks . . . I'll see if that works for my application.
    Thursday, September 19, 2019 12:21 PM
  • Thanks . . . I'll see if that works for my application.
    Thursday, September 19, 2019 12:21 PM