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.
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:
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):
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.
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
Hi Don
If I had to automate the delivery I probably would use a recordset.
Thanks!
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!
Thanks Gina!
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?
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.
Can you do the same integration with an Access web app?
THanks
No, you cannot. It’s one of the drawbacks to using a web app.
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
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
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.
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.
Your welcome!
Thanks Ben, late-binding wouldn’t allow you to record whether the user actually sent the email or not?
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.
Great stuff. I suppose if you also want to automatically send the message you just add
objOutLookMsg.send to the code you posted?
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.