Click here for the changelog. (I moved it to the bottom of the page.)

Microsoft Office is designed to work together in an effort to help users get their work done more efficiently. To this end, Microsoft has given all the pieces of the Office suite the ability to control or be controlled by other of its Office siblings through the use of Visual Basic for Applications. (VBA) VBA is result of the merging of Visual Basic and the macro languages originally designed for the individual applications that now make up office. Finally, with Office97, all applications share a common macro language... and with that, we can use Access to store our e-mail addresses and use Outlook to send them... and we don't have to do anything in the middle beyond writing a query, a simple routine and a macro to make it all happen.

NOTE: The Outlook Security Patch (it comes with Office 2000 SR-2, OfficeXP or as a seperate "security" download) makes this exercise just this side of useless. Since virus writers use these same techniques to send e-mail without you knowing, Microsoft instead pops up a dialog box for 3-5 seconds PER ADDRESS so the virus writer can't take advantage of you. Of course, YOU can't take advantage of this power any longer, since MS thinks you're unable to protect yourself. *sigh* Perhaps one of these days I will write an article explaining how to do this using a freeware mail component...

You can get around the security model if you're willing to spend a little money. Check out the Redemption object model. It's more or less a rewrite of the Outlook model, but bypasses the security triggers. However, it's $200 if you want to use it anywhere, and it does require installing a DLL on the client machine... if your IT policy doesn't allow for that, you're out of luck.

This code doesn't work with Redemption -- the theory does, but the actual code doesn't. Maybe someday I'll make a "Redemption" page, but I just don't have the time right now. (If anyone wants to convert it, lemme know, and I'll post your article.)

Here's a page with some links about the security model: http://www.granite.ab.ca/access/email/outlook.htm

You can download the sample database in either Access97 (128k), Access2000 (132k) or Access2002 (164k) format. You can then just import the query and module into your own database and skip the cutting and pasting. I'm going to assume you have a basic understanding how Access works, and can make a query.

The Query

You have a table that has a list of email addresses. (If you didn't, you'd be reading something else, I'm sure!) You want to send all the people in the list an e-mail... so we need to use a query to get the e-mail addresses we want. Open a new query and add the table that has the email addresses you want. Select the field with the email addresses and call it "EMail". If the field that holds your email addresses is called anything other than "EMail" you can force it to be referred to as "Email" by putting "EMail:" in fromt of the field name in the query grid. You can also add any other fields you want for purposes of filtering and whatnot. We're only concerned with the field known as "email" Once you get the query the way you like it, save it as "MyEmailAddresses"

The Body

To keep things as generic as possible, I grab the body of the e-mail from a text file. That way, you can just change the text file and not have to touch any of this code again. There are other ways to do this (like storing the body in the database, for instance) but using an external file allows us to look at how to grab text from a file; another handy skill to have. So, when you run the macro, it will ask you for the full path of the text file you just created with the body text in it.

The Macro

Macros provide easy ways to trigger code or run actions. In our case, we'll use it to trigger some code we're going to write. The nice thing is, we can make the macro first... tho the macro won't work until we write the code. So, let's make a macro.

1. Click on the macro tab and create a new macro.
2. In the action column, choose "RunCode"
3. In the spot down below where it asks for the function name, type =SendEMail()
4. Save the macro.

The Module

Now, we need to add some code to our database to let it talk to Outlook and send our mail. We do this by clicking on the module tab and adding a new module. After you cut and paste the code in, save the module. The name is irrelevant, but I try and group related things together in a module, so I recommend calling it "SendMail" or something similar. The other thing you need to do is set some references to the Outlook object model and the Scripting runtime. The Outlook Object Model is what allows Access to talk Outlook easily, and the scripting runtime allows you manipulate and read files. To set the references you need to check off a few boxes... here's how.

1. In the module, click on Tools then References
2. Scroll down the list and place a checkmark next to Outlook x.0 Object Model (for Outlook 98 it is version 8.0, Outlook 2000 is version 9.0, Outlook 2002/XP is version 10.0)
3. If it wasn't already checked near the top, scroll down a little bit more and check Microsoft DAO 3.x Object Library
4. Scroll down a little bit more and check Microsoft Scripting Runtime
5. Click OK to close that window.

Failure to do the above will result in "User Defined Type not defined" errors. (There's a chance that on newer systems DAO may not be installed. If that's the case, then you're out of luck until I can update this article with the newer ADO code. Tho the concepts are still the same, this code worn't work. Sorry.) You can get the DAO libraries as part of the Jet 4.0 Engine. Grab the latest service pack from Microsoft.

The Explanation

The code is pretty well commented, but this is what it does in English. The code opens Outlook and opens your query. It asks you for the subject line to use and the text file to use for the body of the message. The code then creates a message, addresses it and sends it to each person in your list that made it through gauntlet known as "MyEMailAddresses." Many people keep Outlook running all the time, so this routine will not shut it down when it's done running unless you uncomment the line (remove the ') that says MyOutlook.Quit. Now, let's get this code into the module and call it a day.

The Code

Copy and paste the following code into the Access code module:

Public Function SendEMail()

Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String

Set fso = New FileSystemObject

' First, we need to know the subject.

' We can��t very well be sending around blank messages...

Subjectline$ = InputBox$("Please enter the subject line for this mailing.", _
"We Need A Subject Line!")

' If there��s no subject, call it a day.

If Subjectline$ = "" Then
MsgBox "No subject line, no message." & vbNewLine & vbNewLine & _
"Quitting...", vbCritical, "E-Mail Merger"
Exit Function
End If

' Now we need to put something in our letter...

BodyFile$ = InputBox$("Please enter the filename of the body of the message.", _
"We Need A Body!")

' If there��s nothing to say, call it a day.

If BodyFile$ = "" Then
MsgBox "No body, no message." & vbNewLine & vbNewLine & _
"Quitting...", vbCritical, "I Ain��t Got No-Body!"
Exit Function
End If

' Check to make sure the file exists...
If fso.FileExists(BodyFile$) = False Then
MsgBox "The body file isn��t where you say it is. " & vbNewLine & vbNewLine & _
"Quitting...", vbCritical, "I Ain��t Got No-Body!"
Exit Function
End If

' Since we got a file, we can open it up.
Set MyBody = fso.OpenTextFile(BodyFile, ForReading, False, TristateUseDefault)

' and read it into a variable.
MyBodyText = MyBody.ReadAll

' and close the file.
MyBody.Close

' Now, we open Outlook for our own device..
Set MyOutlook = New Outlook.Application

' Set up the database and query connections

Set db = CurrentDb()

Set MailList = db.OpenRecordset("MyEmailAddresses")

' now, this is the meat and potatoes.
' this is where we loop through our list of addresses,
' adding them to e-mails and sending them.

Do Until MailList.EOF

' This creates the e-mail

Set MyMail = MyOutlook.CreateItem(olMailItem)

' This addresses it

MyMail.To = MailList("email")

'This gives it a subject
MyMail.Subject = Subjectline$

'This gives it the body
MyMail.Body = MyBodyText

'If you want to send an attachment
'uncomment the following line

'MyMail.Attachments.Add "c:myfile.txt", olByValue, 1, "My Displayname"

' To briefly describe:
' "c:myfile.txt" = the file you want to attach
'
' olByVaue = how to pass the file. olByValue attaches it, olByReference creates a shortcut.
' the shortcut only works if the file is available locally (via mapped or local drive)
'
' 1 = the position in the outlook message where to attachment goes. This is ignored by most
' other mailers, so you might want to ignore it too. Using 1 puts the attachment
' first in line.
'
' "My Displayname" = If you don��t want the attachment��s icon string to be "c:myfile.txt" you
' can use this property to change it to something useful, i.e. "4th Qtr Report"

'This sends it!

MyMail.Send

'Some people have asked how to see the e-mail
'instead of automaticially sending it.
'Uncomment the next line
'And comment the "MyMail.Send" line above this.

'MyMail.Display

'And on to the next one...
MailList.MoveNext

Loop

'Cleanup after ourselves

Set MyMail = Nothing

'Uncomment the next line if you want Outlook to shut down when its done.
'Otherwise, it will stay running.

'MyOutlook.Quit
Set MyOutlook = Nothing

MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing

End Function

Further Options

The sky is really the limit as to what you can do.

I've been asked about "personalizing" the text with data from the database a few times, so after I wrote my variation on the reply, I thought I'd add it to the page. It's not particularly hard, but it requires some planning and special markups in the text file you're using as the body.

If you need something as simple as a greeting line, all you have to do is change one line:

The big difference would be to change the MyMail.Body from your text file to something else that would have the "Hi Joe!" in it.

MyMail.Body = MyBodyText

becomes

MyMail.Body = "Hi " & MailList("FirstName") & "!" & vbNewLine & vbNewLine & MyBodyText

vbNewLine puts a hard return at the end of the line, so two of them give you in essence, a paragraph break before the text from your template.

To get a little more complicated, you can "tokenize" the text you want to replace.

Since the text of your e-mail is just a string, you can manipulate that string however you want. You could make what we call "tokens" in the template, and then use string manipulation functions to replace the tokens with fields from your database.

For instance, say you want the text of your e-mail to read: "Joe: Yesterday, you sold 20 widgets. You did good!"

You can't just have a text file that says that, otherwise everyone in your database would know that Joe sold 20 widgets (Good for Joe!), and they'd have no idea about their own performance, so we need to create tokens.

So, we need to place specific words or phrases with generic ones:

[[Firstname]]: Yesterday, you sold [[NumberOfUnits]] widgets. [[GoodOrBad]]

Then, in the loop that goes thru the names, we'd need to replace the tokens with the values from the database... However, once we replaced it the first time, we'd lose our token (it was replaced the first go-round) so we need to create a new varibale to hold our custom-per-message body:

' This line will copy the "master" template into
' a variable we can mess around with

MyNewBodyText = MyBodyText

' Now we can replace tokens to our heart's content
' without worrying about corrupting the "master" template

MyNewBodyText = Replace(MyNewBodyText, "[[FirstName]]", MailList("FirstName"))

MyNewBodyText = Replace(MyNewBodyText, "[[NumberOfUnits]]", MailList("NumberofUnits"))

that would then replace the tokens [[Firstname]] and [[NumberOfUnits]] with their values from the database.

A token can be any sequence of characters that won't be repeated by accident. So, you could use the word "you" as a token, but every time it was encountered in the document, whether or not it was meant to, it would be replaced by the new value.

(i.e the above desired sentence would become "Yesterday 20 sold [[NumberOfUnits]] widgets. 20 did good!" -- not the result you were hoping for...)

So, we create tokens that are unique -- I use two open brackets, a descriptive string, and two closing brackets -- that sequence of characters won't occur anywhere else by accident, so I can be confident that I won't accidentally create a sentence like the above.

You can also add all sorts of logic to things.

For instance, instead of everyone getting the "You did good!" you might use the token [[GoodOrBad]] and then use a logic statement like:

If MailList("NumberOfUnits") > 20 then
MyBodyText = Replace(MyNewBodyText, "[[GoodOrBad]]", "You did good!")
else
MyBodyText = Replace(MyNewBodyText, "[[GoodOrBad]]", "You stink!")
End If

... which would text of the person made a quota, and offer a value statement based on his performance.

(Of course, remember to then change your variable name when you assign the body to it --

MyMail.Body = MyNewBodyText

Conclusion

There's so much you can do with this simple routine... I enjoy hearing from you (and getting your test messages!) and am glad we can help you out this way.

Here's something else...

Someone asked me about tracking the e-mails sent in a table in the database. Since its on my mind, here's what I wrote:

You would open the table, and after each e-mail, append a record:

[Near the top of the code]

Dim MyTrackingTable as Recordset
Set MyTrackingTable = Currentdb.OpenRecordset("TrackingTableName")

[later on in the code, after MyMail.Send]

MyTrackingTable.AddNew
MyTrackingTable("emailaddress") = MyMail.To
MyTrackingTable("emailsubject") = MyMail.Subject
MyTrackingTable("DateSent") = now()
MyTrackingTable.Update

[back to code]

    'And on to the next one...
    MailList.MoveNext

Loop

[etc.]

So, obviously, you'll need a table with three fields in it, emailaddress, emailsubject and datesent. You can then tweak this table setup as you see fit.

More Recipients!

By moving your loop, you can add many recipients to one e-mail. However, in doing so, you can no longer use the simple .TO modifier, you need to use the RECIPIENTS collection, and add each e-mail address as their own RECIPIENT.

Happily, it's pretty simple stuff.

So we take the code from above, and we change it a little:

' now, this is the meat and potatoes.
 ' this is where we loop through our list of addresses,
 ' adding them to e-mails and sending them.

    Do Until MailList.EOF

           ' This creates the e-mail

        Set MyMail = MyOutlook.CreateItem(olMailItem)

            ' This addresses it
            MyMail.To = MailList("email")

[...]

    'And on to the next one...
    MailList.MoveNext

Loop

So, we morph this into:

        ' This creates the e-mail
        ' We need to move it BEFORE we start the loop, since
        ' we don't want to make a bunch of e-mails, we just want one.

        Set MyMail = MyOutlook.CreateItem(olMailItem)

         ' now, this is the meat and potatoes.
         ' this is where we loop through our list of addresses,
         ' and we add them to the RECIPIENTS collection

    Do Until MailList.EOF

            ' This adds the address to the list of recipients
            MyMail.Recipients.Add MailList("email")

            'And on to the next one...

    MailList.MoveNext

Loop

			' And now that we've addressed it, we can finish composing the rest of the fields.

            'This gives it a subject

            MyMail.Subject = Subjectline$

               'This gives it the body
            MyMail.Body = MyBodyText

[...]

Changelog

03-28-06: I dropped a link where you can get the DAO libraries

02-16-06: Typos stink. I made a typo in the "many people, one e-mail" message, an equals sign where there shouldn't have been one. Apologies. It works now as advertised.

02-10-06: I am still amazed at how much traffic this page gets. Thanks! I've added a little bit on how to add multiple people to the e-mail, instead of multiples e-mails to one person each...

10-24-05: Apparently, I mucked up the personilzation code to the tune of using examples and not the object names we established in the code above it, you can't just cut and paste it. I've since corrected it, and you should be able to cut-and-paste it now.

12-25-03: Merry Christmas! It seems the attachment code is broken in later versions of Outlook. If you change the position argument to 1 instead of -1, ot seems to work. I also made an Access 2002 version of the database... dunno why, really... Keep those cards and letters coming... but remember, I can't rewrite this for you or offer too much help... this is meant to help you learn, not just drop it into a project (tho you could!)

05-28-02: I'm still flattered at how much e-mail I get from and about this document. I added a bit to explain how to add attachments.

11-13-01: I'm flattered at how much e-mail I get from and about this document. I edited the document a little for OfficeXP and added a caveat about newer systems without DAO (Data Access Objects). Also, added a comment in the code about displaying instead of automaticially sending the e-mail messages.

11-28-00: Edited document slightly to re-insert paragraphs about using a text file for the body of the message.