Poor man’s mail merge in Apple Mail

Back at work after some "virtual" vacation (where virtual equals being at sea, yet nailed to a phone for most of the day… busy times over here), I have been confronted with what seemed to be a brain dead issue, that is sending a sizable number of emails to a lot of recipients. I'm not fond of Bcc: lists, so I thought there would have been an easy way to do some sort of mail merge in Apple Mail.

Unless I'm seriously missing something, that's not the case: there is no built-in functionality I could find, and all I manage to scavenge on the Net were dubious crippleware packages. There is a remote possibility using Automator, but it seems to depend from addressbook entries, while all I had was a text file with a list of email addresses.

I thought that would make for a good chance to finally have a look at AppleScript and, to my surprise, it was way easier than I thought. I'm sure there are much better ways of coding this (I particularly hate how I had to build the From: address by hand, yet apparently there is no easy way to grab that from the AppleScript dictionary), but if all you have is a text file with your e-mail in it and another file with a list of email addresses, you might find this script useful. Or not. It floats my boat, so I thought I'd share it.

Enjoy!(or just download it)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
tell application "Mail" to set allAccounts to name of every account
choose from list allAccounts with title "Choose the Mail account to use..."
set theAccount to result as string
 
set subjectDialog to display dialog ¬
	"Enter the subject of the email to send" default answer "no subject"
set theSubject to text returned of subjectDialog
 
set sendOrPreview to the button returned of ¬
	(display dialog ¬
		"Send the messages right away or preview and send manually?" with title ¬
		"Send or Preview?" with icon caution ¬
		buttons {"Preview", "Send"} ¬
		default button 1)
 
set theText to (choose file with prompt "Pick a text file containing the email text")
 
set theContent to read theText
 
tell application "Finder"
	set addresses to paragraphs of ¬
		(read (choose file with prompt "Pick a text file containing email addresses, one by line"))
end tell
 
tell application "Mail"
	activate
	set activeAccount to account theAccount
	repeat with i from 1 to (the length of addresses)
		set newMessage to make new outgoing message ¬
			with properties {account:activeAccount, subject:theSubject, content:theContent}
		tell newMessage
			set sender to ¬
				((full name of activeAccount & " < " & email addresses of activeAccount as string) & ">")
			make new to recipient at end of to recipients ¬
				with properties {address:(a reference to item i of addresses)}
			set visible to true
		end tell
		if sendOrPreview is equal to "Send" then
			send newMessage
		end if
	end repeat
end tell

Comments

comments

18 thoughts on “Poor man’s mail merge in Apple Mail”

  1. Gianugo,

    What perfect timing! I was looking to do this exact thing in Apple Mail and your post was at the top of the search list. THANK YOU!

    The one addition to your script I’d love to see is the ability add a unique salutation. For instance, if the text file of email addresses looked like:
    Michael, email@email.com
    Gianugo, email2@email2.com

    Then the body of the email could be:

    Dear XXXX,

    Where XXXX is replaced by Michael for the first email and Gianugo for the second email.

    I know Iknow, you offered up something for free and now someone wants to you add their feature. But I do think it’d be a useful feature for all.

    Thanks,
    Michael

  2. I’m running into an error and was hoping you’ve already seen it and found a work around. When I run the script it works fantastic, however, it is messing with the message text. The start of the message looks like this”

    “{\rtf1\ansi\ansicpg1252\cocoartf949\cocoasubrtf350
    {\fonttbl\f0\fswiss\fcharset0 Helvetica;}
    {\colortbl;\red255\green255\blue255;}
    {\info
    {\author Michael J. McCrystal}
    {\*\copyright Copryright 2006 Michael J. McCrystal }}\margl1440\margr1440\vieww9000\viewh8400\viewkind0
    \pard\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\ql\qnatural\pardirnatural

    \f0\fs24 \cf0 Hey Everyone,\”

    Have I done something wrong in the process?

    Thanks so much for dreaming this up. I’ve been looking for a good way to do this for a long time.

  3. Awesome work, any idea on how to add an attachment? and how to I set BCC account.
    Much obliged.

    Robert

  4. Here’s a tweaked version that allows you to select a signature to use, formats the message as plaintext, and does some simple personalisation.

    If an entry in the address file has the format

    firstname lastname

    then the message body will begin “Dear firstname”.

    Graham

    ———

    (* Copyright 2008 Gianugo Rabellino – http://boldlyopen.com
    This snippet is licensed under the Apache License version 2.0
    see http://www.apache.org/licenses/LICENSE-2.0.html *)

    tell application “Mail” to set allAccounts to name of every account
    choose from list allAccounts with title “Choose the Mail account to use…”
    set theAccount to result as string

    tell application “Mail” to set allSignatures to name of every signature
    choose from list allSignatures with title “Choose the signature to use…”
    set theSignatureName to result as string

    set subjectDialog to display dialog ¬
    “Enter the subject of the email to send” default answer “no subject”
    set theSubject to text returned of subjectDialog

    set sendOrPreview to the button returned of ¬
    (display dialog ¬
    “Send the messages right away or preview and send manually?” with title ¬
    “Send or Preview?” with icon caution ¬
    buttons {“Preview”, “Send”} ¬
    default button 1)

    set theText to (choose file with prompt “Pick a text file containing the email text”)

    set messageBody to read theText

    tell application “Finder”
    set addresses to paragraphs of ¬
    (read (choose file with prompt “Pick a text file containing email addresses, one by line”))
    end tell

    set AppleScript’s text item delimiters to space

    tell application “Mail”
    activate
    set activeAccount to account theAccount
    repeat with i from 1 to (the length of addresses)

    set target to item i of addresses
    set targetWords to every text item of target

    if (the length of targetWords) > 1 then
    set forename to first word of target
    set theContent to “Dear ” & forename & return & return & messageBody
    else
    set theContent to messageBody
    end if

    set newMessage to make new outgoing message ¬
    with properties {account:activeAccount, subject:theSubject, content:theContent}

    set message signature of newMessage to signature theSignatureName of application “Mail”

    tell newMessage
    set sender to ¬
    ((full name of activeAccount & ” “)
    make new to recipient at end of to recipients ¬
    with properties {address:(a reference to target)}
    set visible to true
    end tell

    — Set to plaintext
    — From http://www.kith.org/journals/jed/2009/09/12/12380.html
    tell application “System Events”
    tell application process “Mail”
    set frontmost to true
    end tell
    keystroke “T” using {command down, shift down}
    end tell

    if sendOrPreview is equal to “Send” then
    send newMessage
    end if
    end repeat
    end tell

  5. Sorry, problem with previous posting, probably with HTML encoding of angle brackets.

    Intro should read:

    If an entry in the address file has the format

    firstname lastname <emailaddress>

  6. I grabbed the script and pasted it int eh mail application folder where the mail scripts are stored. I chose my account but not sure what to do from there. any help would be appreciated. I’d love to see this work.

  7. I made some moderately extensive modifications to the script. I’d be willing to upload my version if someone were to give me a location to which to upload it. Here is the description of my modified version.

    This script asks the user to:

    1. Select whether mail messages should be left in open windows (where they can be examined and manually sent or deleted), or saved in the Drafts mailbox (where they can be examined and manually sent or deleted), or automatically sent immediately

    2. Select a one-line file that will be used as the subject of the messages (this line may contain dummy parameters which can be replaced by merged data)

    3. Select a file with the body of the messages (this file may contain dummy parameters which can be replaced by merged data)

    4. Select a merged-data file. One E-Mail message will be generated for each line of this file. Each line must start with an E-Mail address – no name or angle brackets, just “User@Host.Domain”. The E-Mail address is optionally followed by up to eleven tab-delimited fields. The first of those eleven fields is intended to be the first name of the recipient of the E-Mail. The second of those eleven fields is intended to be the last name of the recipient of the E-Mail. The remaining nine of the eleven fields are available for any information that you want to merge into the message.

    5. Select a Mail account from which the messages will be sent

    6. Select a signature (or no signature) with which the message will be signed.

    The Subject and the Body of the message may each contain any number of the following dummy parameters which will be replaced by the contents of the corresponding field in the line of the merged data file being used to generate this message.

    a. [~EMail~] will be replaced by the E-Mail address from the first column of the merged data file

    b. [~FirstName~] will be replaced by the first name from the second column of the merged data file

    c. [~LastName~] will be replaced by the last name from the third column of the merged data file

    d. [~FullName~] will be replaced by the full name made from the 2nd & 3rd columns of the merged data file

    e. [~FullAddr~] will be replaced by the full E-Mail address made from the 1st, 2nd & 3rd columns of the merged data file

    f. [~Field-1~] will be replaced by field1 from the fourth column of the merged data file

    g. [~Field-2~] will be replaced by field2 from the fifth column of the merged data file

    h. [~Field-3~] will be replaced by field3 from the sixth column of the merged data file

    i. [~Field-4~] will be replaced by field4 from the seventh column of the merged data file

    j. [~Field-5~] will be replaced by field5 from the eighth column of the merged data file

    k. [~Field-6~] will be replaced by field6 from the ninth column of the merged data file

    l. [~Field-7~] will be replaced by field7 from the tenth column of the merged data file

    m. [~Field-8~] will be replaced by field8 from the eleventh column of the merged data file

    n. [~Field-9~] will be replaced by field9 from the twelfth column of the merged data file

    Note that at http://homepage.mac.com/aamann/Mail_Scripts.html there is a package called “Mail Scripts” containing a script named “Send all Drafts” which is described as immediately sending all messages in the “Drafts” folders for all accounts. That script might be helpful if you use the option to save E-Mails as drafts, rather than to send them immediately.

  8. Gianugo,

    Many thanks for this script. My gmail account was hacked and a spam email sent to everyone in the address book. After isolating the email addresses affected then thanks to your script I can send an apologetic email to all concerned.

    Many of the emails went to business contacts, so to be able to send a follow up email and explain what has happened will help restore some of the confidence lost by such an attach.

    It is very generous of you to make your script available, thank you again.

    regards

    Russell Greaves

  9. Hi folks,
    Thanks for this great info.
    Does anyone have the script David Rosenberg put together? Sounds like exactly what I am looking for.
    Would love to download it.

    David, if you are reading this, is your script still available?

    With much thanks,

    Jonathan

Leave a Reply

Your email address will not be published. Required fields are marked *