Attaching Report as PDF to HTML Email

[easykey]

OK I have sussed how to:

• attach a report as a PDF
• Change the Caption so it adds the unique invoice number to the name of PDF file
• Add the Invoice number to the Subject of the email
• Get the TO email address from the customer record
• Get the First Name of the customer and write the email

Dim strReportName As String
strReportName = "Invoices2"
DoCmd.OpenReport strReportName, acViewDesign, , , acHidden
Reports(strReportName).Caption = "Invoice-" & Me.InvoiceNo
DoCmd.Close acReport, strReportName, acSaveYes
DoCmd.SendObject acSendReport, strReportName, acFormatPDF, Me.WorkEmail, , , "Easykey - Invoice-" & Me.InvoiceNo, "Hello " & Me.FirstName & "," & _
vbNewLine & vbNewLine & _
"Please find attached Invoice Number " & Me.InvoiceNo & _
vbNewLine & vbNewLine & _
"Kind Regards, Sue Brown (Administrator)"

All great except one tiny detail. I need the email to send using the HTML font and style set in Outlook and include our standard .htm signature that contains several images. So tantalisingly close but I have read that using DoCmd.SendObject restricts everything to Plain Text

 

[Minty]

I'm afraid you'll need to use Outlook automation to achieve that as far as I am aware.
It's not as painful as it sound but needs a little more code.

    Dim OutApp As Object
    Dim OutMail As Object
    Dim signature As String
    '-- Standard Email Variables
    Dim Variable_To As String
    Dim Variable_Subject As String
    Dim Variable_Body As String
    Dim Variable_AttachmentFile As String
    Dim Variable_savepath As String

    'Set your variables in here
    Variable_To = "john.doe@ibm.com" 
    'etc etc

    Set OutApp = GetObject(, "Outlook.Application")
    
    If OutApp Is Null Then
        Set OutApp = CreateObject("Outlook.Application")
    End If
    
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)
   
    With OutMail        'This creates the users default signature if they have one set, then saves it to add to the end of the email.
        .Display
    End With
        signature = OutMail.body
        
    With OutMail
    
        .To = Variable_To
        .CC = ""
        .BCC = ""
        .Subject = Variable_Subject
        .MailItem.ReplyRecipients.Add = "doodah@somewhere.com"
        .Attachments.Add (Variable_AttachmentFile)
        .HTMLBody = Variable_Body '& vbNewLine & Signature
        .Display   'or use .Send
        .ReadReceiptRequested = False
    End With
   
    Set OutMail = Nothing
    Set OutApp = Nothing

The fun part is formatting the HTML correctly..

 Variable_Body = "<p>" & "Good " & Greeting & ContactName & "," & "</p>" & _
    "<p>" & "" & "</p>" & _
    "<p>" & "Please find attached your quote - Ref: " & ID & "<br/>" & _
    "Please don't hesitate to contact the sales team if you have any further enquiries." & "</p>" & _
    "<p>" & "Best regards," & _
    "<p style=""color:#b22222;"">" & "
' etc etc etc

 

[easykey]

Many thanks Minty for your swift reply

I don't quite get these 2 variables:
Variable_Attachment = ?
Variable_savepath = ?

Where do I call the report Invoices2 and turn it into a PDF?

Are you saying I need to save the report in a location and then attach it?

 

[Minty]

Yes that's how this version works. It does allow you to store the file on a network share, handy for centralised storage.
Use output to PDF to store the PDF in the first instance.
Sorry on tablet otherwise would post up some code.

 

[easykey]

Hi Minty thanks for all your help so far,

I've tried lots of combinations with no success. If I understand correctly I am trying to create a procedure whereby the invoice file is saved locally (with the unique invoice number as part of the file name) then I guess there is a second part that creates the HTML email and attaches the PDF file that was just created in a local location?

So far - all attempts just do nothing. Any chance you have time to show the full code example? (as you were on iPad last time)

 

[Minty]

Can you show the code you have so far - I suspect it's something simple that you've missed?
(My code is not straight forward as it stores multiple types of document to a variety of folders...)