Send report as PDF attachment to email using Outlook Application object

[Pawtang]

I am currently using DoCmd.SendObject to export the current report as a PDF, attach it to an email, and pre-populate the recipient and some of the message body. However, I would like to use the more specific and powerful method for many reasons: I need to ensure that this command will launch through Outlook on other people's computers, and I want to specify body html rather than just body text.

I am not sure how to create the PDF from the report and attach it to the message when using the object method.

Current code that works:

Private Sub cmdEmail_Click()

 On Error GoTo ErrorHandler

    Dim email_target As String

    email_target = DLookup("email", "tblCustomers", "customerNumber = '" & Reports!rptRFR!cust_nb & "'")

    DoCmd.SendObject acReport, "rptRFR", acFormatPDF, email_target, , , "RMA " & Me.[RMA_nb], "Hello," & vbCrLf & vbCrLf &

 "Attached is a copy of the RMA that was requested. Please note the RMA instructions on the form. If there are any questions please let
us know.", True


ErrorHandler:
    MsgBox ("E-mail was canceled or an error occured - e-mail was not sent!")
End Sub

Outlook Object method that is not yet able to include the PDF:

'Private Sub cmdEmail_Click()
'    Dim appOL As Object
'    Dim msgOL As Object
'    Dim strSubject As String
'    Dim strBody As String
'    On Error GoTo ErrHandler
'    stSubject = "RMA " & Me![RMA_nb]
'    strBody = "Hello," & _
'        "<br><br>Attached is a copy of the RMA that was requested. Please note the RMA instructions below, that are also on the attached. If there are any questions please let us know." & _
'        "<br><br><b>Instructions:</b>" & _
'        "<br><br>Please include a copy of this RMA with the product when being returned. Please ensure that the material is returned within 30 days of the RMA authorization date. If unable to return the product within this 30 day time-frame, please contact Customer Service for an extension. Only one extension will be granted, before the RMA is cancelled. If the material being sent back deviates from the material noted below, please contact Customer Service before sending the material back so that the appropriate updates can be made to ensure timely receipt. (<a href='customerservice@scccombustion.com'>customerservice@scccombustion.com</a>)" & _
'        "<br>Guests?:</b>"
'    Set appOL = CreateObject(Class:="Outlook.Application")
'    Set msgOL = appOL.CreateItem(0)
'    With msgOL
'        .Subject = strSubject
'        .HTMLBody = strBody
'        .Display
'    End With
'    Exit Sub
'ErrHandler:
'    MsgBox "E-mail was canceled or an error occured - e-mail was not sent!", vbExclamation
'End Sub

[pbaldy]

The code is extremely difficult to read like that. Generally speaking, you can use OutputTo to create a PDF file from the report and then attach that file to your email.

[Pawtang]

The code is extremely difficult to read like that. Generally speaking, you can use OutputTo to create a PDF file from the report and then attach that file to your email.

Sorry, not sure why it got all compressed into one line, I edited to expand

[pbaldy]

Thanks, that does make it easier to read. Doesn't change my answer though. You can use OutputTo to create a PDF file from the report and then attach that file to your email. If you don't know how to add an attachment, maybe Daniel's code will give you a leg up.

https://www.devhut.net/2010/09/03/vb...ok-automation/

[Blings]

Pawtang, see code from my database which might be be helpful as it works really well for our application attaching PDF's to Outlook email with body text, tweak it to your needs:-

Private Sub Command133_Click()

10        On Error GoTo Command133_Click_Error
20            If Me.Dirty Then Me.Dirty = False
'30      MsgBox "A Copy of the Quote was saved to the C Drive PDF Folder", vbInformation
      Dim strSQL As String
      Dim OutApp As Object
      Dim OutMail As Object
      Dim strToSQL As String
      Dim strCPON As String
      Dim strSOP As String
      Dim strCN As String
     
      
      Dim strSubject As String
      Dim strMessage As String
      Dim strReportname As String
      Dim strPath As String


34    strCN = Me.ClientName
35    strCPON = Me.ClientPONumber
37    strSOP = Me.SalesOrderDatePromised
40    strToSQL = Me.EmailTo
50    strSubject = "Order Acknowledgement - " & [ClientPONumber] & ""
60    strReportname = "rptSalesOrderAcknowledgement"
70    strPath = "C:\PDF Reports\" & "Order Acknowledgement - " & strCPON & ".pdf"




80    DoCmd.OpenReport strReportname, acPreview, "", "[SalesOrderNumber]=[Forms]![frmSalesOrders].[Form]![SalesOrderNumber]"
90    DoCmd.OutputTo acOutputReport, strReportname, acFormatPDF, "C:\PDF Reports\" & "Order Acknowledgement - " & strCPON & ".pdf"




100   strMessage = "Dear " & strCN & "," & vbNewLine & vbNewLine & "This is to acknowledge that we received your Purchase Order number: " & strCPON & "." & vbNewLine & vbNewLine & "Order Acknowledgement is attached." & vbNewLine & vbNewLine & "Estimated Completion Date is: " & strSOP & "."


110   Set OutApp = CreateObject("Outlook.Application")
120   Set OutMail = OutApp.CreateItem(0)


130   On Error Resume Next


160   With OutMail
170   .To = strToSQL
      '190     .CC = strCC1 & ";" & strCC2
180     .Subject = strSubject
190     .Attachments.Add strPath
200     .Body = strMessage
210       .Display
220   End With
       
230       On Error GoTo 0
240       Exit Sub


Command133_Click_Error:


250       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Command133_Click, line " & Erl & "."


End Sub

[Pawtang]

Thanks, that does make it easier to read. Doesn't change my answer though. You can use OutputTo to create a PDF file from the report and then attach that file to your email. If you don't know how to add an attachment, maybe Daniel's code will give you a leg up.

https://www.devhut.net/2010/09/03/vb...ok-automation/

Unfortunately my firewall at the office is blocking that link...

My hesitance on this method is that I don't want to generate a local .pdf file each time, but rather just generate the file as an attachment to an outlook mail item. Does this save the PDF as a local file first, and then in the next step you attach the PDF to an outlook object? How does this work on different users computers, where the path to the file would be different? I suppose I can define a location on the network drive, but then will encounter errors if anything is moved in the future. Or can you OutputTo directly to an attachment?

[Blings]

Yes, the code I shared creates a PDF as a local file then in the next step attaches that file to Outlook as an attachment. We have since modified the code to store the PDF's on a server location instead as we conveniently use those PDF's on more than one occasion.

You wouldn't encounter an error as when you run the code it'll just recreate the PDF again and overwrite it if it was already there unless you give the file a dynamic name as to avoid overwriting the previous.

We need expert advice WRT: "Or can you OutputTo directly to an attachment?"

[pbaldy]

Yes, this method creates a file and then attaches it. To my knowledge there's no way to attach a file without first saving it other than SendObject. You can easily delete the file after sending it. I typically use the same folder that my apps are run from, which the user doesn't typically see anyway.

[Pawtang]

Another point to ponder here is excessive data duplication... we (hypothetically) are creating PDFs of reports of data that already exists in its smallest form in the database and saving it to our drives. The database can recreate those reports on the fly with data that already exists in the database. So exporting reports to PDFs to the drive is doing some unnecessary system bloating.

Edit: As I posted this, pbaldy's last post appeared, so on that note: yes, I suppose part of the same process could be to delete the file as soon as its attached and sent.

[Minty]

I created a "TempExport" folder on all users machines in the same location as the DBpath.
It was a simple matter to then a) know where you had stashed the output b) Tidy it up, as they were all in one place.

You could easily write a routine that every Monday if the db was opened delete everything from that folder.
Make sure the users understood it was a temporary location and if they want to to keep an export it was their responsibility to move it.

Obviously important things like quotes or contracts were stored centrally on the network under the end clients account ID and date stamped, for easy retrieval.

[Pawtang]

This is what I have ended up with and it works nicely. I'm wondering if I can insert the command to delete the temporary file as soon as I have the file attached - so, right after the "End With", or if I need to wait until after the email is sent.

Private Sub cmdEmail_Click()
    Dim appOL As Object
    Dim msgOL As Object
    Dim strSubject As String
    Dim strBody As String
    Dim strTo As String
    Dim strPath As String
    
    On Error GoTo ErrHandler
    strPath = "Z:\RETURNS\Database\Temp\RMA " & Me![RMA_nb] & ".pdf"
    DoCmd.OutputTo acOutputReport, rptRFR, acFormatPDF, strPath
    
    strSubject = "RMA " & Me![RMA_nb]
    strBody = "Hello," & _
        "<br><br>Attached is a copy of the RMA that was requested. Please note the RMA instructions below, that are also on the attached. If there are any questions please let us know." & _
        "<br><br><b>Instructions:</b>" & _
        "<br><br>Please include a copy of this RMA with the product when being returned. Please ensure that the material is returned within 30 days of the RMA authorization date. If unable to return the product within this 30 day time-frame, please contact Customer Service for an extension. Only one extension will be granted, before the RMA is cancelled. If the material being sent back deviates from the material noted below, please contact Customer Service before sending the material back so that the appropriate updates can be made to ensure timely receipt. (<a href='customerservice@scccombustion.com'>customerservice@scccombustion.com</a>)"
    Set appOL = CreateObject(Class:="Outlook.Application")
    Set msgOL = appOL.CreateItem(0)
    With msgOL
        .Subject = strSubject
        .attachments.Add strPath
        .HTMLBody = strBody
        .Display
    End With
    
    
    Exit Sub
ErrHandler:
    MsgBox "E-mail was canceled or an error occured - e-mail was not sent!", vbExclamation
End Sub

[pbaldy]

Not sure, I usually send rather than display. Easy to test.

Kill strPath

[Pawtang]

Not sure, I usually send rather than display. Easy to test.

Kill strPath

I tested, you can kill as soon as its attached, so you can create the temp file and get rid of it all in the same process. Clean.