4 Ways to Automate Sending Emails from Microsoft Access
Manually sending a single email from an email client is easy. Automating email sending, by contrast, is deceptively hard.
Philipp Stiefel covers all the basics of using the
DoCmd.SendObject method to send emails from within Microsoft Access. Here's the "simple example" from Philipp's article:
DoCmd.SendObject acSendNoObject, , , _ "firstname.lastname@example.org; email@example.com", , , _ "Email without attachment", "This is the email body.", True
The article goes on to discuss attaching Access objects, such as forms and reports, to the email.
The most common approach to sending email that I've used personally is Outlook automation. This has one major drawback, though: the user must have a copy of Outlook installed. In many corporate environments, this is a safe assumption to make.
One thing that you may run afoul of, though, is the Outlook Object Model Guard. You'll know this is a problem if you (or your users) see either of the following warnings:
These warnings used to make Outlook automation a terrible user experience without extensive workarounds or third-party tools (such as vbMAPI). It seems Microsoft may have addressed that with a more forgiving approach beginning with Outlook 2007. According to this article, those warnings are hidden if the following conditions are met:
- Windows Vista or later
- Antivirus software in "Good" health status
- Default Outlook security settings
Though I've never used it–and it may be less necessary in light of the above changes–Wayne Phillips (of twinBASIC and vbWatchdog fame) offers a code library that avoids the warnings shown above. The library also includes several samples that cover common email tasks, like:
- Sending reports from Access
- Embedding images
- Resolving recipients
What if you can't guarantee that your end user will have any email client installed? One option is to use the Microsoft CDO library. Once again, Philipp Stiefel provides a thorough explanation of how this is done. Philipp recently updated his article on July 9, 2021 (he originally published it on November 15, 2015).
This solution also runs from any VBA environment, not just Access.
Total Access Emailer
While I've never used it, I would be remiss to write an article about automating emails from Microsoft Access without mentioning the Email Add-in program from FMS.
One thing that has always turned me off from the FMS offerings is that each license is tied to a specific version of Microsoft Access.
One Final Thought
I strongly recommend against using any of these solutions to run large-scale email campaigns (think 10,000+ subscriber newsletters). It is far too easy to run afoul of anti-spam laws or accidentally get your email server blacklisted by trying to roll your own version of mailgun.
Image by Muhammad Ribkhan from Pixabay