KC Lemson

By KC Lemson [MS]

A poor man's mail merge

A poor man's mail merge

  • Comments 5
  • Likes
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 http://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
  • Wouldn't it be just as easy to BCC everyone. They would still think it went just to them and reply to you?

  • 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.

  • 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.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment