Using VBA Script with Microsoft Access to Send Emails as Outlook Messages with Examples
Emailing from Access
A useful feature of a Microsoft Access database is using VBA and Outlook to automatically send an email based on an event occuring in
your database. For example, if you have a database that is used by several departments, then it can be useful to have an email sent to someone in the next department during a process, once another user has completed their part of the process. Or, if you’re producing a monthly report, it can be a significant time saver to be able to just press a button to save, attach and email your figures to all the relevant people.
The Easy Way
There’s a very easy way to use VBA script to send an email as an Outlook message. In fact, you don’t even have to use VBA; you can just create a macro and use the SendObject command, but you’re more likely to want to do this as part of some code.
The command you’ll be needing is:
Docmd.SendObject [ObjectType], [ObjectName], [OutputFormat], [To], [Cc], [Bcc], [Subject], [MessageText], [EditMessage], [TemplateFile]
[ObjectType] - The type of database object you wish to attach to the email (table, report, etc), assuming you want to attach an object.
[ObjectName] - A string containing the name of the object you wish to send or blank if you don’t wish to attach an object.
[OutputFormat] - A string containing the output format of the object. You can send an attachment in xls, txt, rtf & html formats. Leave this blank if there’s no attachment. If you leave it blank having specified an attachment, you’ll be prompted for the format.
[To] - A string containing the email addresses that you want in the ‘To’ field. They should be separated by a semi-colon, or whatever you have setup as a list separator in your Windows options. If no addresses are specified, you will be prompted for an email address.
[Cc] & [Bcc] - Same as [To], but for the corresponding fields, and not mandatory.
[Subject] - The subject of your email
[MessageText] - The message of your email
[EditMessage] - True or false, if set to true, then the email will be displayed for editing before sending.
[TemplateFile] - A path to a file containing an html template you wish to use for this email.
So, for example
Docmd.SendObject acSendQuery, “Query1”, “MicrosoftExcel(*.xls)”, “[email protected]”, , , “Monthly Report”, “Here are the figures you wanted”, False
would send a copy of the Query1 query to your boss as an Excel spreadsheet attachment.
Depending on how you wanted to use this in your database, you might want to have a table of email addresses that can be maintained by your users, and then you could read those email addresses from the table and use them to make the appropriate [To]. [Cc] & [Bcc] strings.
What about Real Attachments?
The obvious thing missing with the Send Object command found in Access is that you can only send objects from within the database. This certainly has its uses, particularly if you just need to send someone a quick message to tell them they have a task to perform in the database, but what if you have a file you want to attach?. If you were to carry out the example in the last section, your boss would probably send you the spreadsheet straight back and ask you to make it a little more presentable. This is where the VBA will really come in handy, because you can put together some code that will export your query to a spreadsheet, and then format the spreadsheet in Excel before emailing it to your boss. Now you have a nice presentable spreadsheet, but you need to be able to attach it to an email.
You may already be familiar with using Automation from VBA (particularly to format that Excel spreadsheet we’re about to email). That is the method we will use now to attach a file to an email and send it as an Outlook message.
Set oLook = CreateObject(“Outlook.Application”)
Set oMail = oLook.createitem(0)
oMail.To = “[email protected]” (also use oMail.Cc & oMail.Bcc)
oMail.Subject = “Monthly Report”
oMail.body = “Here are the figures you wanted”
oMail.Attachments.Add Path to attachment (assuming you’ve saved the file earlier in your code, you will probably have the path in a string already, so just use that)
oMail.Send (or use oMail.Display to show the email before sending).
- Image Credit - Wikimedia Commons - EJavanainen
- Based on the writer’s experience.