Mail Merge with Contacts Group

Here’s a powerful example of AppleScript automation, involving three OS X applications: Contacts, Pages, and Mail, that will create and mail encrypted PDF files generated from a Pages template containing text placeholders.

To try the tutorial, just follow the steps outline below:

Contacts Setup

This Mail Merge example uses the people of a chosen Contacts group as the source for the placeholder replacement data. If your target contact information is in a spreadsheet or other data container, read this page containing simple instructions for easily transferring contacts from other data sources into the Contacts application.

The Mail Merge script also uses the data in the default Contacts card (My Card) as the source for the sender placeholder data. If you haven’t already setup the default card in Contacts, do this:

DO THIS ►In the Contacts application, create a default card containing the Mail Merge sender’s data. Include First Name, Last Name, eMail address, Mailing Address, and Phone Number. Make the card the default card by choosing Make This My Card from the Card menu.

(⬇ see below ) The default user Contacts card (My Card) designated with icon (1) and overlay (2)

mycard

Before running the script, make sure you have the targeted people assigned to a Contacts group:

DO THIS ►For testing the script, create small Contacts group containing four to five people. The data requirements for the recipient contacts are: First Name, Last Name, and eMail Address. By default Mailing Address is optional for recipients. Individual Contacts entries with incomplete required data will be skipped.

(⬇ see below ) An individual from a Contacts group. This recipient’s record contains all of the required (mailing address is optional) contact information.

mycard

The Example Pages Template

Naturally, this script is meant to work with your own templates. But you should try it once with the provided example template, to become familiar with how it works.

DO THIS ►DOWNLOAD, open, and install the example Pages pre-tagged template.

mail-merge-template

The template contains text placeholders that will be located and replaced by the script using contact information from the individuals in the chosen Contacts group.

The Mail Merge Script

Here’s a script for performing a mail merge with a Pages document.

NOTE: The default set of text placeholders used by the script, such as SENDERFIRSTNAME and recipientLastName, can be customized by editing the properties at the top of the script.

The only requirement for their design, is that a placeholder must be a unique continuous string of text characters, not found as part of other words, and that a placeholder not contain spaces, numbers, hyphens, punctuation, or word delimiters.

DO THIS ►Open the script in the AppleScript Editor application and run the script. Follow the prompts and instructions.

TIP: If you want to use this script often, install it into the system-wide Script Menu.

For each of the people in the chosen group, the script will:

  • Create a new document using the chosen template
  • Replace the text placeholders with the sender and recipient data
  • Export the new document as a PDF (encryption is optional)
  • Attach the exported PDF file to a new outgoing Mail message addressed to the current recipient
  • Close the created document without saving

The script offers an AUTO-SEND option to automatically send the generated eMail messages once the processing has completed.

Mail Merge with Chosen Contact Group
  
01-- USER SETABLE PROPERTIES
02property senderFirstNamePlaceholder : "SENDERFIRSTNAME"
03property senderLastNamePlaceholder : "SENDERLASTNAME"
04property senderEmailAddressPlaceholder : "SENDEREMAILADDRESS"
05property senderFullAddressPlaceholder : "SENDERFULLADDRESS"
06property senderPhoneNumberPlaceholder : "SENDERPHONENUMBER"
07 
08property recipientFirstNamePlaceholder : "recipientFirstName"
09property recipientLastNamePlaceholder : "recipientLastName"
10property recipientFullAddressPlaceholder : "recipientFullAddress"
11 
12property attemptMailSend : false
13 
14global peopleCount, recipientGroup
15 
16tell application "Contacts"
17 activate
18 try
19 -- DISPLAY OPENING DIALOG WITH SETABLE PREFERENCES
20 repeat
21 if attemptMailSend is false then
22 set autoSendState to "OFF"
23 else
24 set autoSendState to "ON"
25 end if
26 display dialog "This script will perform a Mail Merge between a Pages tagged-template and a chosen Contacts group." & return & return & "Contact data requirements for the sender are: First Name, Last Name, eMail Address, Mailing Address, and phone." & return & return & "Contact data requirements for recipients are: First Name, Last Name, and eMail Address." & return & return & "AUTO-SEND: " & autoSendState with icon 1 buttons {"Cancel", "Set Prefs", "Begin"} default button 3
27 if the button returned of the result is "Begin" then
28 exit repeat
29 else
30 display dialog "Set AUTO-SEND to:" buttons {"Cancel", "OFF", "ON"} default button autoSendState
31 if the button returned of the result is "OFF" then
32 set attemptMailSend to false
33 else
34 set attemptMailSend to true
35 end if
36 end if
37 end repeat
38 
39 -- CHECK AND RETRIEVE THE SENDER DATA
40 -- assumes that the sender is the default Contact card
41 set thisPerson to my card
42 if thisPerson is missing value then error number 10000
43 
44 -- get the sender’s data
45 tell thisPerson
46 set senderFirstName to first name
47 set senderLastName to last name
48 if senderFirstName is missing value or senderLastName is missing value then
49 error number 10001
50 end if
51 
52 set the emailCount to the count of emails
53 if the emailCount is 0 then
54 error number 10002
55 else if the emailCount is 1 then
56 set senderEmailAddress to the value of first email
57 else -- multiple email addresses, prompt the user to pick one
58 set theseEmailAddress to the value of every email
59 set senderEmailAddress to ¬
60 (choose from list theseEmailAddress with prompt ¬
61 "Pick the sender email address to use:" default items (item 1 of theseEmailAddress))
62 if senderEmailAddress is false then error number -128
63 set senderEmailAddress to senderEmailAddress as string
64 end if
65 
66 set the addressCount to the count of addresses
67 if the addressCount is 0 then
68 error number 10003
69 else if the addressCount is 1 then
70 set senderAddress to the formatted address of the first address
71 else -- multiple addresses, prompt the user to pick one
72 set theseAddresses to the formatted address of every address
73 set senderAddress to ¬
74 (choose from list theseAddresses with prompt ¬
75 "Pick the sender address to use:" default items (item 1 of theseAddresses))
76 if senderAddress is false then error number -128
77 set senderAddress to senderAddress as string
78 end if
79 
80 set the phoneCount to the count of phones
81 if the phoneCount is 0 then
82 error number 10004
83 else if the phoneCount is 1 then
84 set senderPhoneNumber to the value of first phone of it
85 else -- multiple phone numbers, prompt the user to pick one
86 set thesePhoneNumbers to the value of every phone
87 set senderPhoneNumber to ¬
88 (choose from list thesePhoneNumbers with prompt ¬
89 "Pick the sender phone number to use:" default items (item 1 of thesePhoneNumbers))
90 if senderPhoneNumber is false then error number -128
91 set senderPhoneNumber to senderPhoneNumber as string
92 end if
93 end tell
94 
95 -- CHECK AND PROMPT FOR TARGET GROUP
96 set groupCount to count of groups
97 if the groupCount is 0 then
98 error number 10005
99 else if the groupCount is 1 then
100 set recipientGroup to group 1
101 set recipientGroupName to name of recipientGroup
102 else -- multiple groups, prompt the user to pick one
103 set theseGroupNames to the name of every group
104 set recipientGroupName to ¬
105 (choose from list theseGroupNames with prompt ¬
106 "Pick the recipient Contacts group:" default items (item 1 of theseGroupNames))
107 if recipientGroupName is false then error number -128
108 set recipientGroupName to recipientGroupName as string
109 set recipientGroup to group recipientGroupName
110 end if
111 
112 -- CHECK GROUP FOR PEOPLE
113 set peopleCount to the count of people of recipientGroup
114 if the groupCount is 0 then
115 error number 10006
116 end if
117 
118 on error errorMessage number errorNumber
119 if errorNumber is 10000 then
120 set errorNumber to "NO DEFAULT CARD"
121 set errorMessage to ¬
122 "No person in this Contacts database is set to be the default card or “My Card.” Select a person and choose “Make This My Card” from the Card menu."
123 else if errorNumber is 10001 then
124 set selection to my card
125 set errorNumber to "MISSING SENDER INFO"
126 set errorMessage to ¬
127 "The current default card is missing a value for the First Name or Last Name fields."
128 else if errorNumber is 10002 then
129 set selection to my card
130 set errorNumber to "MISSING EMAIL ADDRESS"
131 set errorMessage to ¬
132 "The current default card is missing a value for the email address field."
133 else if errorNumber is 10003 then
134 set selection to my card
135 set errorNumber to "MISSING MAILING ADDRESS"
136 set errorMessage to ¬
137 "The current default card is missing a value for the address fields."
138 else if errorNumber is 10004 then
139 set selection to my card
140 set errorNumber to "MISSING PHONE NUMBER"
141 set errorMessage to ¬
142 "The current default card is missing a value for the phone number field."
143 else if errorNumber is 10005 then
144 set errorNumber to "MISSING GROUPS"
145 set errorMessage to ¬
146 "There are no groups in the current contact database."
147 else if errorNumber is 10006 then
148 set errorNumber to "MISSING PEOPLE"
149 set errorMessage to ¬
150 "The chosen Contacts group contains no people."
151 end if
152 if errorNumber is not -128 then
153 display alert (errorNumber as string) message errorMessage
154 end if
155 error number -128
156 end try
157end tell
158 
159-- LOCATE THE TEMPORARY ITEMS FOLDER
160set thisDirectoryHFSpath to (path to temporary items folder) as string
161 
162-- SWITCH TO PAGES
163tell application "Pages"
164 activate
165 try
166 -- CHECK TO SEE ALL DOCUMENTS ARE CLOSED
167 if the (count of documents) is not 0 then error number 10000
168 
169 -- GET a LIST OF THE INSTALLED USER TEMPLATES AND PROMPT USER TO PICK THE ONE TO USE
170 set userTemplateNames to the name of every template whose id of it begins with "User/"
171 if userTemplateNames is {} then error number 10001
172 
173 set the chosenTemplateName to ¬
174 (choose from list userTemplateNames with prompt ¬
175 "Pick the tagged Pages template to use:" default items (item 1 of userTemplateNames))
176 if chosenTemplateName is false then error number -128
177 set chosenTemplateName to chosenTemplateName as string
178 
179 -- PROMPT FOR THE NAME OF THE EXPORTED PDFs
180 repeat
181 display dialog "Enter the name to use for the exported document:" default answer ""
182 set thisDocName to the text returned of the result
183 if thisDocName is not "" then
184 if thisDocName does not end with ".pdf" then
185 set exportDocName to thisDocName & ".pdf"
186 else
187 set exportDocName to thisDocName
188 end if
189 exit repeat
190 end if
191 end repeat
192 
193 -- PROMPT FOR PASSWORD (OPTIONAL)
194 repeat
195 display dialog "Enter a password for the PDF file:" default answer ¬
196 "" buttons {"Cancel", "No Password", "OK"} default button 3 with hidden answer
197 copy the result to {button returned:buttonPressed, text returned:firstPassword}
198 if buttonPressed is "No Password" then
199 set usePDFEncryption to false
200 exit repeat
201 else
202 display dialog "Enter the password again:" default answer ¬
203 "" buttons {"Cancel", "No Password", "OK"} default button 3 with hidden answer
204 copy the result to {button returned:buttonPressed, text returned:secondPassword}
205 if buttonPressed is "No Password" then
206 set usePDFEncryption to false
207 exit repeat
208 else
209 if firstPassword is not secondPassword then
210 display dialog "Passwords do no match." buttons {"Cancel", "Try Again"} default button 2
211 else
212 set providedPassword to the firstPassword
213 set usePDFEncryption to true
214 exit repeat
215 end if
216 end if
217 end if
218 end repeat
219 
220 -- PROMPT FOR MAIL SUBJECT LINE
221 set defaultMessageSubject to "Document from " & senderFirstName & space & senderLastName
222 repeat
223 display dialog "Enter the subject for the created Mail message:" default answer defaultMessageSubject
224 set the outgoingMessageSubject to the text returned of the result
225 if the outgoingMessageSubject is not "" then exit repeat
226 end repeat
227 
228 -- OPEN TEMPLATE AND CHECK FOR OPTIONAL PLACEHOLDERS
229 display dialog "Scanning template…" buttons {"•"} default button 1 giving up after 1
230 set thisDocument to ¬
231 make new document with properties {document template:template chosenTemplateName}
232 tell body text of thisDocument
233 set recipientFullAddressPlaceholderCount to ¬
234 the count of (every word where it is recipientFullAddressPlaceholder)
235 end tell
236 close thisdocument saving no
237 display dialog "Scan complete. Beginning Mail Merge…" buttons ¬
238 {"•"} default button 1 giving up after 1
239 
240 -- BEGIN MERGE
241 set the errorLog to "MAIL MERGE ERROR LOG"
242 set createdMessages to {}
243 repeat with i from 1 to the peopleCount
244 set skipFlag to false
245 tell application "Contacts"
246 set thisPerson to person i of the recipientGroup
247 set thisPersonIDString to the id of person i
248 try
249 set recipientFirstName to first name of thisPerson
250 if recipientFirstName is missing value then error
251 on error
252 set recipientFirstName to "NO-FIRST-NAME"
253 set skipFlag to true
254 set the errorLog to ¬
255 errorLog & return & recipientFirstName & space & thisPersonIDString
256 end try
257 try
258 set recipientLastName to last name of thisPerson
259 if recipientLastName is missing value then error
260 on error
261 set recipientLastName to "NO-LAST-NAME"
262 set skipFlag to true
263 set the errorLog to ¬
264 errorLog & return & recipientLastName & space & thisPersonIDString
265 end try
266 try
267 set recipientFullAddress to the formatted address of the first address of thisPerson
268 if recipientFullAddressPlaceholderCount is not 0 and ¬
269 recipientFullAddress is missing value then error
270 on error
271 set recipientFullAddress to "NO-FULL-ADDRESS"
272 set skipFlag to true
273 set the errorLog to ¬
274 errorLog & return & recipientFullAddress & space & thisPersonIDString
275 end try
276 try
277 set recipientEmailAddress to the value of the first email of thisPerson
278 if recipientEmailAddress is missing value then error
279 on error
280 set recipientEmailAddress to "NO-EMAIL-ADDRESS"
281 set skipFlag to true
282 set the errorLog to ¬
283 errorLog & return & recipientEmailAddress & space & thisPersonIDString
284 end try
285 end tell
286 
287 if skipFlag is false then
288 -- open a copy of the tagged template
289 set thisDocument to ¬
290 make new document with properties ¬
291 {document template:template chosenTemplateName}
292 tell thisDocument
293 -- replace the placeholders with the person data
294 set placeholderWordreplacementStringPairings to {{senderFirstNamePlaceholder, senderFirstName}, {senderLastNamePlaceholder, senderLastName}, {senderEmailAddressPlaceholder, senderEmailAddress}, {senderFullAddressPlaceholder, senderAddress}, {senderPhoneNumberPlaceholder, senderPhoneNumber}, {recipientFirstNamePlaceholder, recipientFirstName}, {recipientLastNamePlaceholder, recipientLastName}, {recipientFullAddressPlaceholder, recipientFullAddress}}
295 tell body text
296 with timeout of 600 seconds -- allow up to 10 minutes to process really long documents
297 repeat with i from 1 to the count of placeholderWordreplacementStringPairings
298 copy item i of placeholderWordreplacementStringPairings to ¬
299 {placeholderWord, replacementString}
300 my replaceWordWithStringInBodyText(placeholderWord, replacementString)
301 end repeat
302 end timeout
303 end tell
304 end tell
305 
306 -- export the PDF to the temporary items folder
307 set the targetExportFileHFSpath to (thisDirectoryHFSpath & exportDocName)
308 if usePDFEncryption is true then
309 export thisDocument to file targetExportFileHFSpath ¬
310 as PDF with properties {password:providedPassword}
311 else
312 export thisDocument to file targetExportFileHFSpath as PDF
313 end if
314 
315 -- close the new document without saving it
316 close thisdocument saving no
317 
318 -- make a new message, attaching the exported PDF file
319 tell application "Mail"
320 set thisMessage to make new outgoing message with properties ¬
321 {subject:outgoingMessageSubject, visible:true}
322 tell thisMessage
323 make new to recipient with properties ¬
324 {address:recipientEmailAddress, name:(recipientFirstName & space & recipientLastName)}
325 make new attachment at end of paragraphs of content with properties ¬
326 {file name:file targetExportFileHFSpath}
327 end tell
328 set the end of createdMessages to thisMessage
329 end tell
330 
331 -- delete the exported PDF file
332 tell application "Finder"
333 move document file targetExportFileHFSpath to the trash
334 end tell
335 end if
336 end repeat
337 
338 -- SEND THE CREATED MESSAGES (OPTIONAL)
339 if attemptMailSend is true then
340 try
341 tell application "Mail"
342 activate
343 repeat with i from 1 to the count of the createdMessages
344 set thisMessage to item i of the createdMessages
345 send thisMessage
346 end repeat
347 end tell
348 on error errorMessage
349 set the errorLog to errorLog & return & ¬
350 "Attempt at sending messages failed." & space & errorMessage
351 end try
352 else
353 tell application "Mail" to activate
354 end if
355 
356 -- CHECK THE ERROR LOG
357 if the (count of paragraphs of errorLog) is greater than 1 then
358 -- create a new Pages document with the eror log
359 set thisDocument to ¬
360 make new document with properties {document template:template "Blank"}
361 set body text of thisDocument to errorLog
362 error number 10002
363 else
364 -- display dialog "Mail Merge completed." buttons {"OK"} default button 1
365 display notification "Process completed." with title "Mail Merge with Contacts Group"
366 end if
367 on error errorMessage number errorNumber
368 if errorNumber is 10000 then
369 set errorNumber to "OPEN DOCUMENTS"
370 set errorMessage to ¬
371 "Please close all documents before running this script."
372 else if errorNumber is 10001 then
373 set errorNumber to "NO USER TEMPLATES"
374 set errorMessage to ¬
375 "There are no Pages user templates installed on this computer."
376 else if errorNumber is 10002 then
377 set errorNumber to "MAIL MERGE ERRORS"
378 set errorMessage to ¬
379 "The open document lists the errors occuring during the Mail Merge."
380 end if
381 if errorNumber is not -128 then
382 if errorNumber is in {10002} then
383 display notification "Errors occured." with title "Mail Merge with Contacts Group"
384 end if
385 display alert (errorNumber as string) message errorMessage
386 end if
387 error number -128
388 end try
389end tell
390 
391on replaceWordWithStringInBodyText(searchWord, replacementString)
392 tell application "Pages"
393 activate
394 tell the front document
395 tell body text
396 -- start at the end and go to the beginning
397 repeat with i from the (count of paragraphs) to 1 by -1
398 tell paragraph i
399 repeat
400 try
401 if exists searchWord then
402 set (last word where it is searchWord) to replacementString
403 else
404 exit repeat
405 end if
406 on error errorMessage
407 exit repeat
408 end try
409 end repeat
410 end tell
411 end repeat
412 end tell
413 end tell
414 return true
415 end tell
416end replaceWordWithStringInBodyText

TOP | CONTINUE