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@domain.com"
    .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.

Basic rule when it is not working as expected: **Single Step**. Did you try that? Also you need to eliminate all code that is not relevant: https://stackoverflow.com/help/mcve — , Apr 03 '19 at 17:19
Outlook does not open and nothing occurs if it is already open. — DevonRyder, Apr 03 '19 at 18:06
Do you have `On Error Resume Next` anywhere in your code which could be masking an error? — Lee Mac, Apr 03 '19 at 18:23
Doesn't look like DestFile contains a full file path. Code will error if filepath is not valid and file not found. You must have an error handler trapping the error. Step debug. Disable the error handler while debugging. — June7, Apr 03 '19 at 18:27
@June7 is right that my DestFile does not seem to be a full path. In testing I've called the EmailInvoice sub with the DestFile appended to the filepath in that sub (where the invoices get stored). I get a compilation error once the sub runs of "user-defined type not defined" related to my Dim(s) of the outlook objects such as `Dim objApp As Outlook.Application` and `Dim objEmail As Outlook.MailItem` — DevonRyder, Apr 03 '19 at 19:51
Seems I had to go to Tools > References and enable `Microsoft Outlook 16.0 Object Library` as answered on https://stackoverflow.com/a/30648573/5619176 . Now I only need to sort out passing a proper full file path. — DevonRyder, Apr 03 '19 at 20:07
I have sorted out the issue with `DestFile`. At first it was only the filename, then once I fixed that it turned out I was sending the filepath without the `.PDF` file extension. Now I have `strTestFile = "c:\OneDrive\Invoices\" & strNewFileName & ".pdf"`. — DevonRyder, Apr 04 '19 at 13:53
Here is my function I use in my applications to send emails from Access using Outlook: https://stackoverflow.com/a/59975664/7526564 — Vlado, Jan 31 '20 at 15:52

score 1 · Answer 1 · answered Apr 03 '19 at 19:15

1

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.

answered Apr 03 '19 at 19:15

SunKnight0

3,331
1
10
8

While this is appreciated, it doesn't seem to be the issue. I have tried with Outlook open with a profile and email loaded, and nothing happens. I do not get a email window or error of any kind. – DevonRyder Apr 03 '19 at 19:45
1

Look into it anyway. They may have done with the warning but not with the security policy. I know that I have spent many hours in the past wondering why my email sending was silently failing until I stumbled on an article about how Outlook requires special permissions to send email programmatically. – SunKnight0 Apr 03 '19 at 20:31
After ironing out some issues with the file path, I've got it loading now, and I see the message/warning you speak of. I will look into this topic however to suppress this warning, as it will be frustrating for the user. https://support.office.com/en-us/article/i-get-warnings-about-a-program-accessing-email-address-information-or-sending-email-on-my-behalf-86cc5ece-379e-45e3-b8eb-3fefba09946b?NS=MSACCESS&Version=16&SysLcid=1033&UiLcid=1033&AppVer=ZAC160&HelpId=olmain11.chm553714172&ui=en-US&rs=en-US&ad=US provides decent info. You also mention having OL open..this is the only way it now works. – DevonRyder Apr 03 '19 at 20:39
1

I have it working on customer sites in an Active directory environment with access to group policies. Without that it may be a per-workstation and/or per-user one time setting. – SunKnight0 Apr 03 '19 at 20:44

score 1 · Accepted Answer · answered Apr 04 '19 at 01:36

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@domain.com"
        .Subject = "Invoice"
        .Body = "See Attached"
        .Attachments.Add FldrFilePath
        .Display
    End With

End Function

Yes, adding `PathFile = strEstFolder & strNewFileName & ".pdf"` was the fix for my issues with file path. At first I was passing the filename, and then I was passing the path without `.pdf` appended. — DevonRyder, Apr 04 '19 at 13:56

score 1 · Answer 3 · answered Apr 04 '19 at 04:29

1

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 = "abc@abc.com;def@def.com"

  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.

answered Apr 04 '19 at 04:29

Albert D. Kallal

42,205
3
34
51

Honestly, I simply did not use `sendObject` because I was not fully aware of it, and I had a bit of familiarity with building an email the way I have done it in the post. As for being limited to Outlook with my method, that is not a concern, as the user is highly entrenched in Outlook and the MS ecosystem. As for the separate write out, that is because I need the PDFs to also be preserved in the directory, not just email them out. The directory is a One Drive folder that syncs. Though, I may have misunderstood what you have said. – DevonRyder Apr 04 '19 at 14:00
1

No problem. It just a possible suggest. And it only lets you have "one go" at this and thus is limited to one attachment. It also a great way to just launch say a blank email with the send to email - the rest of the params can be left blank, so it can save some coding (a nice quick way to launch a email ready for the user to fill out). However, I will admit that I have a great routine I call for most emails, and I also thus create a instance of outlook. So, just keep this option in your bag of tricks - it can still be useful. – Albert D. Kallal Apr 04 '19 at 23:12

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

3 Answers3