Go to Top

Email reports from Access as PDF attachments in Outlook

I LOVE integrating Access with Outlook, it’s really easy to create a system that allows our clients to export reports as PDF attachments in an email.

Objective:
Email pdf invoice to a recipient using Outlook.

ADVERTISING
ACCESS SAFETY AND TRAINING DATABASE

Needed
Option table to hold templates for the Subject and body of email.
Make sure client table has BillingEmail field.
Add three buttons to form: One to email, another to edit customer record and another to pop the option table.

Table Needed: tblOptions
We usually have an options table in our apps to control defaults and other aspects of the database. In this case we are going to create a simple table and form that will allow admins to edit subject and email body options. Here is a screen shot of what it looks like in our app:

The Email Body field is a Memo type and the Subject line is only a text field.

Email Invoice Form
We added three buttons to the email invoice form, a portion can be seen below:
InvoiceForm

 

 

The Email Inv button will do exactly what it says: print the invoice to PDF, launch an Outlook message, attach the PDF and prepare the subject and email body. You can view the code here.

The Customer button simply launches the customer form and allow the user to enter or edit the destination email address.

Here’s how the email looks like when it’s all done, (the email To field was deleted to protect the receipient):

Email

About Juan Soto

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

18 Responses to "Email reports from Access as PDF attachments in Outlook"

  • Chris M
    March 14, 2015 - 2:45 am Reply

    Hi, hoping someone can help me out. I have most of this code working, my problem is that the PDF file that gets generated has the form but no records shown. The form number is properly being assigned in the name of the form, not sure what is wrong. Here is the code that I think I have goofed up.

    ‘The DocName is the name of the report
    DocName = “CarEmail”
    ‘First, we open the report to the screen using the invoice #
    DoCmd.OpenReport DocName, acViewPreview, , “[LabNum]=” & Me.ID
    ‘DocPath stores the complete path of the PDF
    DocPath = CurrentProject.Path & “\CAR ” & Me.LabNum & “.pdf”

    One of the things that I am not sure on, in the DoCmd, the “[LabNum]=” should that be the key name in the data base? Also, should the “ME.ID” be the key name in the database or the box name on the form that shows the key? Right now “ID” is the name of the box on the form. I have tried the key name in the database in both spots, with the same result. Please help.

  • Don Gentle
    January 9, 2015 - 6:11 am Reply

    This works great! Is their any way to automate this or use some sort of NextRecord or the like to move on to the next rcord on the form and start the process again?

    thanks,

    Don

    • Juan Soto
      January 17, 2015 - 4:31 am Reply

      Hi Don

      If I had to automate the delivery I probably would use a recordset.

      Thanks!

  • Gina Maylone
    December 20, 2014 - 8:48 pm Reply

    I have struggled with various email functions over the last couple of years – none of them were solid. This one is amazingly easy to follow and solid. Thank you Mr. Soto!!! I have saved it in my favorite functions!

    • Juan Soto
      January 17, 2015 - 4:30 am Reply

      Thanks Gina!

  • Ryan
    June 3, 2013 - 9:05 am Reply

    Looking at your code there, I can’t help but wonder why you aren’t calling everything with DocPath after you declared it and set it to your PDF path.

    Every time you call something that needs the PDF path you repeat that long CurrentProject.Path & “\Invoice” & Me.Invoicenumber & “.pdf” string over and over.

    Why?

  • Rusty
    March 25, 2013 - 5:20 am Reply

    When using this code with the Outlook object model, do you have issues with the Outlook security feature popping up and telling you another program is trying to access Outlook and do you want to allow it? If so, how do you get around it? We’ve used the redemption dll in the past.

  • Ken Mulvihill
    March 8, 2013 - 2:15 am Reply

    Can you do the same integration with an Access web app?
    THanks

    • Juan Soto
      March 8, 2013 - 11:43 pm Reply

      No, you cannot. It’s one of the drawbacks to using a web app.

      • Ken Mulvihill
        March 9, 2013 - 12:35 am Reply

        Thanks Juan.
        Integrating Outlook is a big issue for my client. So I will be doing a client server app for them.
        I look forward to seeing you in Portland.
        Ken

  • Bill Sutton
    March 7, 2013 - 10:05 pm Reply

    How timely, this is something I have been working on. I have this very close to working, but I am trying to replace about 20 fields in the body of the email. You infer that I should be able to do this:

    ‘You can get more fancy and use other tokens, for example:
    ‘[CustomerName], [CustomerAddressBlock], etc.

    but I am having trouble with the syntax. Can you further explain how to do multiple replaces in the email body

    Thanks in advance, Great Article

    • Bill Sutton
      March 8, 2013 - 1:20 am Reply

      I solved my issue by creating an extra field in the options database, to how a copy of my original email body. I use a DoCmd.RunSQl Update statement to copy the original to a working email body field. I then run multiple DoCmd.RunSQl Update, statements [EmailBody] = Replace (EmailBody],'[Product], Forms!frmEmail.Product). My Email form has 2 pages in tabCtrl, one holds the Email Body in a subform, the other list all the fields needed in my email, and populated from a query of the emails to send.

  • Anonymous
    March 2, 2013 - 4:38 am Reply

    Hi Juan

    I was in the process of creating a process for automating outlook with Access. I had a process in mind and had actually begun coding. Then I read your post and had a “I could have had a V8 moment”. Your examples was right on the money for what I was attempting do. Thank you. It works like a charm.

    • Juan Soto
      March 2, 2013 - 8:01 pm Reply

      Your welcome!

  • grovelli
    February 28, 2013 - 10:15 pm Reply

    Thanks Ben, late-binding wouldn’t allow you to record whether the user actually sent the email or not?

    • Ben Clothier
      March 1, 2013 - 7:14 pm Reply

      Not cleanly, no. The closest thing is not to display the Outlook window at all and use only Access form to accept inputs and send email directly but we’ve found that an Access form misses out on many features that Outlook email window has to offer and in such cases, our client likes having Outlook email window with all its features and are willing to deal with the versioning issue when using early binding.

  • grovelli
    February 26, 2013 - 3:48 pm Reply

    Great stuff. I suppose if you also want to automatically send the message you just add
    objOutLookMsg.send to the code you posted?

    • Ben Clothier
      February 27, 2013 - 7:47 pm Reply

      Yes, but generally, we don’t do that. It’s usually better user experience to show the email for the user to do a final review or maybe add whatever. It can be frightening to some people when an email is sent in their name without their knowledge (even if they knew that they had clicked a button to do this).

      In some scenarios where we needed to record whether the user actually sent the email or not, we may opt to use early-binding and register the Send event.

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us
[gravityform id="16" title="false" description="false"]