In this article I will show you how to create automated emails from within Microsoft Access against a table containing a list of email addresses. I use this method quite a bit because of how frequent an email distribution list can be changed in the workplace, along with the fact that just about everything I do someone always wants it emailed to them. So for this example I will loop through a set of records in a table for which I want to set as my email recipients, and send a bulk email to them.

In addition, I want to give credit to Wayne Phillips for his original article in which my solution is based off of. I have been using a version of his original solution since around 2006 and still works like a champ. I have tweaked his original code to work for my solutions obviously, and I encourage you to do the same.

I have provided a download of my example below which is a working copy of the techniques described in this article…Enjoy!

[download id=”2″ format=”1″]
[center-ad]

Tools Required

[bullet_list icon=”check”]

  • Microsoft Access – Compatible and tested with versions 2003-2010
  • Microsoft Outlook – Compatible and tested with versions 2003-2007
  • Intermediate Knowledge of VBA – Used in MS Access & MS Outlook
  • Basic Knowledge of HTML – For styling HTML Emails

[/bullet_list]

Setting Up Microsoft Outlook

I start with this step simply because I usually forget about it when I am creating automated emails from Microsoft Access. It’s pretty simple and straight forward, so let’s go ahead and get this taken care of.

Adding VBA & Changing Security Setting in Microsoft Outlook

[bullet_list icon=”rightarrow”]

  • Step 1: Go ahead and open up Microsoft Outlook (Sort of need to start there)
  • Step 2: Update your security settings by via ‘tools’->’macro’->’security’->’no security check for macros’
  • Step 3: Close and reopen Microsoft Outlook
  • Step 4: With Outlook open, now open the VBA window. Note: You can access the VBA window by using alt+F11 on your keyboard.
  • Step 5: Once the VBA window opens go to ‘ThisOutlookSession’ in the module selection and double-click to open it up.
  • Step 6: After the ‘ThisOutlookSession’ opens, insert the code below.

[/bullet_list]

Testing Your Code in Microsoft Outlook

Since you have everything added to Outlook now, I would strongly suggest testing your code out to ensure that it works. If it doesn’t work, do not move forward in the steps below, and instead solve the problems you are running in to now. To test your code, simply open up the immediate window in VBA and type this in (using your information of course) and pressing enter.

?ThisOutlookSession.FnSendMailSafe("email@domain.com","","","Test","Test")

If this works, which it should, let’s go ahead and move on to doing some stuff in Microsoft Access.

Configuring Microsoft Access

Ok, so now that we have things working in Microsoft Outlook, it’s time we get things added in Microsoft Access in order to pull all this together. Essentially, Microsoft Access will call the exposed function you placed in Microsoft Outlook, and perform some magic 😛

Adding VBA Function to MS Access and Calling Outlook VBA Function

[bullet_list icon=”rightarrow”]

  • Step 1: Open up your Microsoft Access Database (Again, kind of need to start here)
  • Step 2: Add a new VBA module to your database
  • Step 3: Copy the code below and add it to your newly created VBA Module (Don’t forget to save!)
  • Step 4: After the ‘ThisOutlookSession’ opens, insert the code below.

[/bullet_list]

Testing Your VBA Function in Microsoft Access

Now that you have the VBA added to MS Access, you should test this module to ensure it works properly. To do so, just copy/paste the below to your MS Access VBA Immediate Window and press enter with the cursor at the end of the statement.
fnsafesendemail "addy@domain.com","test","test"

Building the Email List

So here comes the fun part. Pretty much everything we have done so far is just getting the email automation to work between MS Access and MS Outlook. Now that you can send an email avoiding the security prompts in Microsoft Outlook, you can now figure out how your automation will work. For this example I kept it pretty simple and straight forward, but you can obviously expand this and make it your own.

For example, you could create additional fields in the table to query off of and use this table as a one size fits all for your email distro lists you will be sending to. Sky is really the limit here, so get creative with what you want to do. Now, on to the steps.

Creating the table to contain the email list

If you already have a defined table somewhere, for instance in SharePoint, then you can you that or if another table already exists in your database, then you can use that as well. For this scenario though, let’s pretend none of that exists and we need to build it. So here we go!

[bullet_list icon=”rightarrow”]

  • Step 1: Create a new table in Microsoft Access and name it tblEmailAccts
  • Step 2: Add a column labeled EmailAcct with a data type of text
  • Step 3: Add a column labeled EmailType with a data type of text
  • Step 4: Plug in some example data to test from (make sure these are legit email addresses)

[/bullet_list]

Create some code to generate the email list and send out

This part is pretty simple for this example, but you can obviously expand this to your liking. To get this to work though, just copy and past the sub below into a new module, then call the sub to trigger it and watch it work. Of course, you could also step through it (recommended) so you can see what it does, and how it works, but I’ll leave that up to you. In short though, it loops through the recordset that contains the email addresses, and then parses and creates the email distribution list and then calls the FnSafeSendEmail() function you added earlier.

Testing your VBA Code in MS Access

Again, please test your code before you say you are done. To do this, simple type the following in your immediate window in MS Access and press enter with the cursor at the end of the statement: sendemail

That’s A Wrap!

Well, you guessed it, that is the end of this tutorial. Hopefully this has helped you out in some fashion and if so, please shoot me a note in the comments section below. Cheers!

Tags: