Doing a simple mail merge with Numbers and Mail

A really old favourite feature of word processing apps is mail merge. This could be used to personalize and send bulk letters, emails, labels, or other documents to multiple recipients.

Today I'm going to do a very simple mail merge with Numbers and Mail.

Getting data out of Numbers

I only use Numbers for simple spreadsheets and haven't done AppleScript with Numbers before. Therefore, I had to struggle a bit to get the data out of Numbers.

First I made some simple test data:

Normally, AppleScript should be able to get data from any open document. Numbers was very particular. It wants the document including the file extension. "value of every cell of range" gets all cells in the range:

The result of the AppleScript is a single list:

{"[email protected]", "test name", "subject", "message", "[email protected]", "other test name", "some subject", "more message"}

Making lists for email address, recipient, subject and message

I could have used 4 calls to get the value out of ranges for the 4 columns. Instead I took the list and changed it to 4 lists.

The email address list needs the first and the 5th item of the list. The recipient needs the second and the 6th element. Therefore, I need to count from starting with 1. The first 4 items go into the 4 different lists. When the counter is at 5 then I reduce the counter back to 1 and then fill my 4 lists again.

This gives me the following code:

tell application "Numbers" to tell document "MailList.numbers" to tell sheet 1 to tell table 1
set emailData to value of every cell of range "A2:D3"
end tell

set Counter to 1
set RecipientList to {}
set RecipientNameList to {}
set SubjectList to {}
set MessageList to {}

repeat with theItem in emailData
if Counter = 1 then
set end of RecipientList to theItem
else if Counter = 2 then
set end of RecipientNameList to theItem
else if Counter = 3 then
set end of SubjectList to theItem
else if Counter = 4 then
set end of MessageList to theItem
end if
set Counter to Counter + 1
if Counter = 5 then set Counter to 1

end repeat

return RecipientList

The result for the recipientlist variable looks a bit odd but it's okay:

{item 1 of {"[email protected]", "test name", "subject", "message", "[email protected]", "other test name", "some subject", "more message"}, item 5 of {"[email protected]", "test name", "subject", "message", "[email protected]", "other test name", "some subject", "more message"}}

Creating emails for Mail

Creating the emails in AppleScript is now dead easy. I need to make a loop over the lists and pick the necessary information. An email is made for Mail with "make new outgoing message with properties".

This gives me the following AppleScript:

-- Get the email data from the Numbers sheet
tell application "Numbers" to tell document "MailList.numbers" to tell sheet 1 to tell table 1
    set emailData to value of every cell of range "A2:D3"
end tell

--put the single list into 4 lists
set Counter to 1
set RecipientList to {}
set RecipientNameList to {}
set SubjectList to {}
set MessageList to {}

repeat with theItem in emailData
    if Counter = 1 then
        set end of RecipientList to theItem
    else if Counter = 2 then
        set end of RecipientNameList to theItem
    else if Counter = 3 then
        set end of SubjectList to theItem
    else if Counter = 4 then
        set end of MessageList to theItem
    end if
    set Counter to Counter + 1
    if Counter = 5 then set Counter to 1

end repeat

repeat with currentEmail from 1 to length of RecipientList
    set theRecipient to item currentEmail of RecipientList
    set theRecipientName to item currentEmail of RecipientNameList
    set theSubject to item currentEmail of SubjectList
    set theMessage to item currentEmail of MessageList

    -- Compose the email message
    set emailContent to "Dear " & theRecipientName & "," & return & return & theMessage

    -- Send the email
    tell application "Mail"
        set newEmail to make new outgoing message with properties {subject:theSubject, content:emailContent}
        tell newEmail
            make new to recipient at end of to recipients with properties {address:theRecipient}
            --send
        end tell
    end tell
end repeat

And here is the result of the script:

Verdict

You can see above that the script works. However, there is no error handling at all and the script isn't really robust:

The range is hard coded. Ideally, I would have the script get the filled-in cells in Numbers without hard coding the beginning and the end rows and columns. In my 20 years doing Excel VBA I had multiple methods of doing this: counting from above, from below. I had to take care that the column for counting always was filled in fully.
The columns should be identified by a header. Currently, the order of the columns is assumed.
There is no check if a cell is empty.
There is no check if the email column really contains an email.

If I only want to send more than one email at one time the script is okay. For continued use the script needs a lot more work.

I could easily adapt the script to use Excel instead of Numbers or Outlook instead of Mail.

Moth Software