How to Send an Email with a PDF attached with Outlook using MS Access VBA?

Question

I am working with an Access application within Access 2016. The application outputs to a PDF file via the DoCmd.OutputTo method.

I want to either send this PDF attached to an email I build in code, or open a new Outlook email with the file attached.

When I click the button in the form which triggers the code that includes my sub(s) (which are located in separate modules), the email window is never displayed nor is an email sent (depending on the use of .Display vs .Send). I also do not receive any errors.

I think it may also be worth noting that the Call to the sub inside of a module that creates the PDF works as expected.

I am running Access 2016 and Outlook 2016 installed as part of Office 2016 Pro Plus on a Windows 7 64-bit machine. The Office suite is 32-bit.

The Module & Sub (Email Address Redacted)

Dim objEmail As Outlook.MailItem
Dim objApp As Outlook.Application

Set objApp = CreateObject("Outlook.Application")

Set objEmail = oApp.CreateItem(olMailItem)
With objEmail
    .Recipients.Add "[email protected]"
    .Subject = "Invoice"
    .Body = "See Attached"
    .Attachments.Add DestFile
    .Display        
End With

The Sub Call

MsgBox "Now saving the Invoice as a PDF"
strInvoiceNbr = Int(InvoiceNbr)
strWhere = "[InvoiceNbr]=" & Me!InvoiceNbr
strDocName = "Invoice Print One"
ScrFile = "Invoice Print One"
DestFile = "Inv" + strInvoiceNbr + " - " + Me.GetLastname + " - " + GetLocation
MsgBox DestFile, vbOKOnly

DoCmd.OpenForm strDocName, , , strWhere
Call ExportToPDF(SrcFile, DestFile, "INV")
Call EmailInvoice(DestFile)

Based on the fact that the PDF is being output within a sub in a Module file, should I be creating the email (or calling the sub) within the sub that creates the PDF?

NOTE: I have looked over this accepted answer here on Stack Overflow, as well as many others. My question differs due to the fact that I am asking why the message is not being displayed or sent, not how to build and send a message as the others are.

EDIT: Outlook does not open and nothing occurs if Outlook is already open.

Final Note: To add to the accepted answer, in the VBA editor for Access, you will likely have to go to Tools > References and enable Microsoft Outlook 16.0 Object Library or similar based on your version of Office/Outlook.

Answer 1

To pass full path try using Function EmailInvoice

Example

Option Explicit
#Const LateBind = True
Const olFolderInbox As Long = 6

Public Sub ExportToPDF( _
           ByVal strSrcFileName As String, _
           ByVal strNewFileName As String, _
           ByVal strReportType As String _
                )

    Dim PathFile As String

    Dim strEstFolder As String
        strEstFolder = "c:\OneDrive\Estimates\"
    Dim strInvFolder As String
        strInvFolder = "c:\OneDrive\Invoices\"

    ' Export to Estimates or Invoices Folder based on passed parameter
    If strReportType = "EST" Then
        DoCmd.OutputTo acOutputForm, strSrcFileName, acFormatPDF, _
                       strEstFolder & strNewFileName & ".pdf", False, ""


        PathFile = strEstFolder & strNewFileName & ".pdf"


    ElseIf strReportType = "INV" Then
        DoCmd.OutputTo acOutputForm, strSrcFileName, acFormatPDF, _
                       strInvFolder & strNewFileName & ".pdf", False, ""


        PathFile = strEstFolder & strNewFileName & ".pdf"


    End If

    EmailInvoice PathFile ' call function

End Sub

Public Function EmailInvoice(FldrFilePath As String)

    Dim objApp As Object
    Set objApp = CreateObject("Outlook.Application")

    Dim objNS As Object
    Set objNS = olApp.GetNamespace("MAPI")

    Dim olFolder As Object
    Set olFolder = objNS.GetDefaultFolder(olFolderInbox)
        'Open inbox to prevent errors with security prompts
        olFolder.Display

    Dim objEmail As Outlook.MailItem
    Set objEmail = oApp.CreateItem(olMailItem)

    With objEmail
        .Recipients.Add "[email protected]"
        .Subject = "Invoice"
        .Body = "See Attached"
        .Attachments.Add FldrFilePath
        .Display
    End With

End Function

Answer 2

Your issue is with probably Outlook security. Normally Outlook would show a popup that says that a 3rd party application is attempting to send email through it. Would you like to allow it or not. However since you are doing this programmatically that popup never appears. There used to be a way to bypass this.

Test your program while the user is logged on and has Outlook open. See if there will be any difference in behavior. If that popup does come up, google the exact message and you will probably find a way to bypass it.

Answer 3

Any reason why you not using sendOject?

The advantage of sendobject, is that you not restriced to Outlook, and any email client should work.

So, this code can be used:

  Dim strTo   As String
  Dim strMessage    As String
  Dim strSubject    As String

  strTo = "[email protected];[email protected]"

  strSubject = "Your invoice"
  strMessage = "Please find the invoice attached"

  DoCmd.SendObject acSendReport, "rptInvoice", acFormatPDF, _
        strTo, , , strSubject, strMessage

Note that if you need to filter the report, then open it first before you run send object. And of course you close the report after (only required if you had to filter, and open the report before - if no filter is to be supplied, then above code will suffice without having to open the report first).

There is no need to separate write out the pdf file, and no need to write code to attach the resulting pdf. The above does everything in one step, and is effectively one line of code.