Share via

A poor man's mail merge

  • Article

Several times in the
last few months I've needed to send the exact same mail content to a
list of people, but each mail had to be separately addressed, for the
following reasons:

-

  • I am expecting each recipient to reply and continue the thread with me.
    This way there's no risk of "Reply All" going to anyone other than me.

  • To
    gain the psychological benefit of "Oh, the message is sent only to me,
    so I should probably respond." I find that when I email something to
    multiple people, in some cases people will assume that since there are
    several people on the receiving end, someone else will handle it, or
    they can at least push off reading it until later.

In each case, the content of the mail was a one-time
thing, i.e. I needed to send this one mail to five people today, and
then next week I might have another email to send to six people, but
that would be a different email. So in my case, it didn't really make
sense to create an email template. I also didn't want to use Word and
do an official mail merge because I wanted a quick and dirty way to
save time, and I didn't want to step through the 6 page mail merge
wizard where you select the document, the contacts folder, etc etc.

So, I wrote a macro. It's pretty durn rough around the edges
(I'd be embarrassed to put this next to any of the samples on https://www.outlookcode.com,
but hey, it works for me :-), but it works so I thought I'd share it.
First off, if you're not familiar with running macros in Outlook see my posts about VBA to learn the basics. The way this macro works is:

#1: Create the message you want to create a template out of (i.e. enter
the subject and body). In the To field, put the list of people to which
you want to send individual emails.

#2: Save this item to your drafts folder

#3: Run the macro (assigning it to a button will make this easier)

#4: It will pop up the messages in front of you. Hit Alt+S to send each of them in turn.

Here's the code:

Public Sub KCsCheapoMailMerge()

Dim outapp As Outlook.Application

Dim olExp As Outlook.Explorer

Dim olItemTemplate As Outlook.MailItem

Dim olItem As Outlook.MailItem

Dim arrAddresses() As String
Dim strAddresses As String
Dim i As Integer

Set outapp = CreateObject("Outlook.application")
Set olExp = outapp.ActiveExplorer

Set olItemTemplate = olExp.Selection.Item(1)

strAddresses = olItemTemplate.To

arrAddresses = Split(strAddresses, ";")

For i = 0 To UBound(arrAddresses)

Set olItem = outapp.CreateItem(olMailItem)

olItem.Subject = olItemTemplate.Subject

olItem.Body = olItemTemplate.Body

olItem.To = arrAddresses(i)

olItem.Recipients.ResolveAll

olItem.Display

Next

End Sub

See? I told you it
was cheap and rough around the edges. And I never destroy my objects in
my macros, too lazy and plus it just seems cruel, they barely had a
chance at existing! Some minutae:

-

  • Since you're
    programatically accessing fields on a message that could contain
    recipient info, you'll get the popup to allow it. That's the price you
    pay for quick and dirty macros rather than fancy com add-ins :-)

  • I didn't do
    automatic sending of the messages because it'll hit the object model
    block which makes you wait five seconds per message and the entire
    point of this was to be quick. Also, I'm a perfectionist (funny, given
    my laziness around destroying objects...) and it gives me a chance to
    do a last minute once-over of the messages and recipients to make sure
    everything's as I expected it.

Comments

  • Anonymous
    May 03, 2005
    Wouldn't it be just as easy to BCC everyone. They would still think it went just to them and reply to you?
  • Anonymous
    May 03, 2005
    There are a variety of problems with BCC. It's the opposite of what you say in my experience, when people are BCC'd they don't know who the mail is to, so they assume it's not important. Plus, a lot of people have custom views that look for their name in the to/cc fields and color those messages differently or make them stand out; with bcc, you'd lose that.
  • Anonymous
    May 06, 2005
    Wouldn't you know it, KC, but I needed to do something like this just today! I couldn't resist adding a few improvements: My version at http://www.outlookcode.com/codedetail.aspx?id=869 works from either the selected message or an open message (so you don't have to save in Drafts if you don't want to). It uses email addresses not names (which are not a sure thing) to address the messages. It preserves the formatting in HTML messages. And it avoids Outlook security prompts if you run it in Outlook 2003 VBA by deriving all objects from the intrinsic Application object.