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
- 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.
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:
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)
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.