Creating PDF in Access and emailing through Outlook

March 4, 2013 David Allan MS Access, Software, 0

http://accessexperts.net/blog/2013/02/26/email-reports-from-access-as-pdf-attachments-in-outlook/

Private Sub cmdEmailInv_Click()
    Dim DocName As String
    Dim objOutlook As Object
    Dim objOutLookMsg As Object
    Dim strBody As String
    Dim DocPath As String
    Dim rst As DAO.Recordset

    'Me.txtBillingEmail is a text field on the invoice form that has the billing email
   On Error GoTo cmdEmailInv_Click_Error

    If Me.txtBillingEmail = "No billing email on file." Then
        'When a customer has no email the system inserts the text
        '"No billing email on file."
        MsgBox "No billing email on file.", vbInformation, "Can't Email"
        Exit Sub
    End If

    'The DocName is the name of the invoice report
    DocName = "Invoice On Screen"
    'First, we open the report to the screen using the invoice #
    DoCmd.OpenReport DocName, acViewPreview, , "[invoicenumber]=" & Me.Invoicenumber
    'DocPath stores the complete path of the PDF
    DocPath = CurrentProject.Path & "\Invoice" & Me.Invoicenumber & ".pdf"

    'Delete pdf if it exists
    If Dir(DocPath) <> "" Then
        Kill DocPath
    End If

    'Second, we save the invoice to the directory where the program is launched from
    'we use the invoice number in the name of the file
    DoCmd.OutputTo acOutputReport, DocName, acFormatPDF, CurrentProject.Path & "\Invoice" & Me.Invoicenumber & ".pdf", False
    'Close the report since it's no longer needed
    DoCmd.Close acReport, DocName

    'rst is opened to the options table that holds the subject and body templates
    Set rst = CurrentDb.OpenRecordset("Select * from tblOptions")
    'objOutlook will be used to refence Outlook in the program
    'I recommend Outlook is open while the code is executing
    Set objOutlook = CreateObject("Outlook.Application")
    'Create a new message:
    Set objOutLookMsg = objOutlook.CreateItem(0)
    With objOutLookMsg
        'Use the email field on the invoice for the To field
        .To = Me.txtBillingEmail
        'Optional: copy another email address that will receive a copy of the pdf
        '.CC = "any@what_ever.com”"
        'Read the subject template and replace [InvoiceNumber] with the invoice number on the form
        'You can get more fancy and use other tokens, for example:
        '[CustomerName], [CustomerAddressBlock], etc.
        .Subject = Replace(rst!EmailSubject, "[InvoiceNumber]", Me.Invoicenumber)
        .Body = Replace(rst!EmailBody, "[InvoiceNumber]", Me.Invoicenumber)
        .Attachments.Add CurrentProject.Path & "\Invoice" & Me.Invoicenumber & ".pdf"
        'If you wish, you can have Access send the email immediately, just remove the " ' " below and comment
        'the Display action
        '.Send
        .Display
    End With

On Error GoTo 0
    Set objOutlook = Nothing
    Set objOutLookMsg = Nothing
    Set rst = Nothing
   Exit Sub

cmdEmailInv_Click_Error:
    MsgBox "Error " & Err.Number & " (" & Err.DESCRIPTION & ") in procedure cmdEmailInv_Click of VBA Document Form_Invoice Data Entry"

End Sub

Access, email, outlook, pdf, send