How To Mail Merge On Mac With Pages, Numbers and a Simple Script

If you need to do a "Mail Merge" to print out personalized documents from Pages, you can do it with a simple script. A table from Numbers can be used to customize text boxes in the Pages document and then you can print them all at once.



Here is the script:

var doc = Application("Pages").documents[0];
var table = Application("Numbers").documents[0].sheets[0].tables[0];

for(var i=1;i<table.rowCount();i++) {
	doc.textItems[1].objectText.set(table.rows[i].cells[0].value().toString());
	doc.textItems[0].objectText.set(table.rows[i].cells[1].value().toString());
	doc.export({to: Path("Users/macmost/Documents/Mail Merge Example/export"+i+".pdf"), as: "PDF"});
}

Here is a zip file with the script, sample Pages document and Numbers document: MailMergeScript.zip.

Comments: 59 Responses to “How To Mail Merge On Mac With Pages, Numbers and a Simple Script”

    Rudy Rugebregt
    3 years ago

    I'm working through the tutorial and get two errors:

    Running the script from your package:
    Error:Error: Invalid index.
    I tried setting the path to the folder I'm running from; both on my iCloud desktop and my local directory.

    Running my hand-entered script:
    Error:Error: Can't convert types.

    In any event, I'm getting familiar with scripting and I'll keep plugging away.

    35 years ago I was hand entering MacBasic listings from MacWorld. ;-)

    3 years ago

    Rudy: I would pay careful attention to the path you are using, as if that is the only change, then it is probably where you are going wrong.

    Douglas Brace
    3 years ago

    I know that your goal was to keep the script as simple as possible and that is a good idea. My question is, in Pages, Numbers, and the script, is it possible to give your Numbers table a name, your Pages textbox a name, and then use their names in the script instead of numbers?

    3 years ago

    Douglas: You could add a column header cell in the table, and go through a lot of trouble to figure that out in the script. But you can't name the text box as Pages tex does don't have names.

    Sky Qian
    3 years ago

    I have the same situation as Rudy too, I will try to change the path the location of the file.

    Dana Stevens
    3 years ago

    I had a lot of fun with this and am sure I will find it very useful in the future. Interesting thing is that in addition to improving my JavaScript ability it also helps me get better with AppleScript because I always end up trying to do the same thing with AppleScript to see if it is more difficult. Thanks for the inspiration Gary.

    Shane
    3 years ago

    Hi Gary, thank you very much for putting this together. Ever since apple removed mail merge from pages/numbers, I have been hoping they would bring it back, with no joy and have kept the old 09 pages on one computer, just so I can continue to use this for my conferences, for certificates and name tags. My question, is there a way to adjust this for name tags, to have multiple names to appear on the one page? Ideally ten different names.. Thanks

    3 years ago

    Shane: I suppose you could have 10 columns with 10 names in each row. Then treat each name as a different item. I have 2 items in this example, but you'd then have 10. Of course you could always just use some sort of label printing software instead.

    Shane
    3 years ago

    Thanks Gary, I will have play with it and let you know. I prefer to print onto cards as apposed to using sticker labels, as our courses run over multiple days and I would need to print new labels each day if they were stickers. I am happy for any suggestions...

    Colin
    3 years ago

    Thanks for this information. Sightly different question, I understand how your script works, but I'm looking at creating a directory with my data. In Numbers I have listed all of my relevant data (name, address, email, etc...) and would like to populate one Pages document with this information (if that makes sense). Any help would be greatly appreciated. Thanks

    3 years ago

    Colin: Populate a Pages document, how, exactly? If you have it in Numbers, then you could just copy and paste the table from Numbers to Pages. But then why not just leave it in Numbers?

    Colin
    3 years ago

    Hello Gary, thanks for your quick response. I had a mail merge functionality using Microsoft Office on a PC. I'm hoping to have a similar functionality work within Apple's Office suite. I'm looking to create an address directory to print out and distribute. In Word, I created a table, with headings (such as Name, Address, etc...) which populated from Excel. Each Row created and populated a new table in Word automatically. I don't know if I "cleared the mud" or not?

    3 years ago

    Colin: It sounds like you don't need "mail merge" for that at all. If the data is already in a table in Numbers, then just print it from Numbers. Why do you need to bring it into Pages at all? And even if you did, just copy and paste the table from Numbers to Pages as I suggested before.

    Colin
    3 years ago

    Many thanks Gary. I'll have a look. The main reason why I was mail merging in the past was due to some generic graphics added to the Word doc. I will have a play with Numbers and see what I can come up with. Many thanks for your time.

    mark
    3 years ago

    Thank you so much for this. Can you help a dummy out? How can I export to another file type, not PDF? The PDF export surprisingly messes up the printing of my non-standard paper. I need to export to pages preferably.

    3 years ago

    Mark: Run Script Editor. Go to Window, Library. Then double-click Pages to open the Pages library. Switch to JavaScript at the top. Now search for "export," the command used in the script above. There are other options for exporting besides PDF. I've never tried any of them though.

    Thomas
    3 years ago

    Gary, thank you so much, you've made my day. It worked instantly and saved me a lot of headache.

    AJ
    3 years ago

    Thanks for the explanation and script. What would be the approach to script the creation of a list on a single Pages document that references a Numbers spreadsheet. I am thinking about something akin to, but not specifically, making mailing labels for printing. And, is there any option to restrict the merge to visible rows/records in the Numbers doc, or merge by a filter of some criteria? I am interested how to generate a single new document that will compile/reorganize info from database.

    3 years ago

    AJ: It would be difficult to set this up for everything on a single sheet. Probably much easier to just design it in Numbers or copy and paste from Numbers to Pages. As for restricting the import, I would just manipulate the table as you like in Numbers first, sorting and removing rows, etc, then do the script. Much easier that way. But if this is something you need to do often and you are not a coder, then it may be better to look for a professional database solution that fits your industry.

    Ingrid
    3 years ago

    I have never coded anything in my life and I can't begin to tell you how excited I was when I got this to work. The most satisfaction I have ever had on a computer! Some things I discovered for me that may help others:

    The "Error: Error: Invalid Index" was caused by having more than one sheet in the Numbers file.
    The export issue "File Not Found" was caused by not setting the disk access permissions for Script Editor first.

    Diane
    3 years ago

    Can you create an alternate video showing the Automator workflow instead of the javascript workflow please? It would be helpful to a lot of us.

    3 years ago

    Diane: You use the same script in Automator as you do in Script Editor. You just add a single JavaScript action in Automator and use the same script. But it is sometimes a little difficult to deal with scripting in that little Automator action window, which is why it is better to use Script Editor.

    AJ
    3 years ago

    Gary, thanks for taking the time to read and respond. I anticipated your reply. I don't need this frequently, but I do need it, and was hoping to avoid purchasing, and learning, a more cumbersome new product.

    Kevin
    3 years ago

    Gary, How do you get Quick Action enabled in Finder? I'm trying to combine the highlighted individual files.

    3 years ago

    Kevin: Create a Quick Action in Automator. AT the top set its app to Finder. Save it. Then it should appear in various places, like the Services menu. If you want to see it in the Preview area, click the three-dots button and custom it to include that one.

    Ed Robinson
    3 years ago

    Is it possible to have all the pages merge into ONE multi paged document rather than selecting and merging them after doing the mail merge. I have just over 600 I want to merge but would like the final result to be one document. ??Thanks

    3 years ago

    Ed: If you have the need for that kind of thing a lot, maybe see about getting an app that handles it or using Microsoft 365 to do it. Otherwise, I'm not sure if it is possible with a script. If it is, you'll have to work at it to get it to do what you want.

    Linda Menkhorst
    3 years ago

    Is it possible to send the pdf from the script by email as an attachment?

    3 years ago

    Linda: Automatically? Probably. But not a good idea. You'd be behaving like a spammer in that case, sending out large amounts of automatic email. So you could end up getting kicked off your email service.

    Kevin Gilbraith
    3 years ago

    Thanks Gary, 220 letters created with 3 text box of personal info integrated. PDFs generated and combined to 1 file. Printed locally in colour and sent yesterday! Brilliant help!

    Brandon
    3 years ago

    Hi Mark,

    How am I able to use this mail merge in such a way that I can send a mass e-mail from Outlook?

    Thanks!

    Brandon

    Brandon
    3 years ago

    Sorry, I meant Gary, not Mark! haha

    3 years ago

    Brandon: Not sure who Mark is, but I think Outlook has its own Mail Merge thing. Or at least something going on with MS Office (I assume you use Office if you are using Outlook).

    Karmen Barker
    3 years ago

    so need this for the awards I do for lots of kids - getting Error on line 5: Error: Can't get object. The only part I have not been able to do is enable full disk access -- it is not a choice on Sierra? I will try on newer OS. Thank you for your time.

    3 years ago

    Karmen: Not sure, but using older versions of macOS (and Numbers and Mail) will probably not work too well.

    Karmen Barker
    3 years ago

    @Gary -- thank you. Updated OS worked great. This is a life saver! So grateful. Been dependent on a very old machine with old pages/numbers - so freeing.

    Angela Wolf
    3 years ago

    Gary, Thanks for this. All was going well until I got to the path part. I have a table with 77 addresses and 6 columns. At +i+ I got confused. I could never get it to run at this point. I am trying to save to my desktop and I have tried to save to a documents file. I believe my issue is having my loop repeat over and over for all 78 addresses. I even tried copy and pasting the first row as you did in the video but I could never get it to run. Have checked and double checked.

    3 years ago

    Angela: Start out by doing the exact example I am showing, including copying and pasting the code above. Then slowly modify it to work with your data instead.

    SAMSON NAINAN
    3 years ago

    Can I merge data from numbers into Apple Mail?

    3 years ago

    Samson: Do you mean send out email messages to people listed in a Numbers spreadsheet? No, not really. Even if you could, if the list is long you may run into problems with your ISP as most don't let you send out bulk email. If the list is small, you can just copy and paste those cells into Mail.

    Craig Jackson
    3 years ago

    This will be very helpful. I'm getting the following error: Error: Error: The document “MM Letter” could not be exported as “export1”. The file doesn’t exist.

    Craig Jackson
    3 years ago

    I figured out the root of my error regarding the file doesn't exist message. The issue I have is matching the font and formatting from the text box to match the rest of the document. I've been researching other output formats other than PDF such as "text" but that doesn't seem to work.

    Marco R.
    3 years ago

    Hello Gary,

    I have the same problem as Craig (however, I don't understand if there is actually a connection between the font and the error). Maybe it's an error in writing the path? I'm italian so my directories' names differ, however I changed them and they should be right.. It would really help me even just understanding where or what to look for in order to fix this. Thank you VERY much for you great work however!!

    Marco R.
    3 years ago

    Hi Gary and Craig,

    I found out the problem! In my case, very strangely, I should have mantained the path starting with "Users" even if in my mac it figures as "Utenti", in italian. Craig, I suggest you to go on the info of the folder where you want to export the files and copy the information about the location of the file. In this way I figured out that the folder "Users" had the english name. Now I will try to see if if works with another job! Have a good day.

    Bee
    3 years ago

    I keep getting an Invalid Index error. It reads the 1st cell of the 1st row, then I get an error after that.

    I have 4 columns and have 1 table & 1 sheet in my numbers file. I have 4 text boxes in the pages file.
    I don't know why I keep getting an invalid index number. Does it matter if I am using an older version of
    Numbers?

    3 years ago

    Bee: Not sure if using an older version is the problem, but it could be. Try doing it with exactly the example I show. Then expand from there.

    Bee
    3 years ago

    Well, it looked like it had a problem with my text boxes for some reason. I have NO idea what it didn't like about my text boxes.

    iker
    3 years ago

    Ok first off, Thank you !!!

    So... everything has worked well, except I am unable to get a pdf file...\\

    I run the script and the info from numbers scrambles thru the fields perfectly but, it seems its not saving or exporting

    3 years ago

    iker: Just check everything carefully. Make sure the path exists and you have typed it perfectly.

    Rob
    3 years ago

    Getting the error message: 'Error:Error: The Document "MM Letter" could not be exported as "export 1". The file does not exist. Have tried the script with my built documents/data as well as the docs from your zip and getting the same error message. I have adjusted the export path to ("Users/robertsampson/desktop/Mail Merge Example/export"+i+".pdf") Other than that, setup is identical. What am I missing?

    3 years ago

    Rob: Try Desktop instead of desktop.

    Rob
    3 years ago

    Didn't work. Moved the path from desktop to (“Users/robertsampson/documents/Mail Merge Example/export”+i+”.pdf”) and it now works. A quirk of Mac, I suspect. Merging over 1,000 letters with 4 personalised fields. Excellent solution, thanks.

    Robert Simms
    3 years ago

    Wow! Very nice for creating a large-type phone book for Mom, with her favorite numbers! I have to say thank you, and tell you that your instructional video and the supporting text, and the comments from your students in merging were all terrific. It was easy to add text boxes, and with just a little typing on the script, get it to do it. My problem was that the pages in my print queue sometimes were not in order. This, I know, is an operating system or print queue or spooler, or something goof.

    Alex
    2 years ago

    Gary, I've watched your videos and love your work, however, I'm dealing with a situation that will work well with your steps, however, its way more complicated. I have a wedding ministry and I've created the wedding scripts and saved them as documents to use as template files. I have a lot of placeholders as there are a lot of changes that need to be made. I used to use a third party app, which no longer works. Is there an easy way to do it with a lot of fields that are also duplicated?

    2 years ago

    Alex: No easy way, no. You could use the skills you learned from this video to make an Automator script to do it though.

    Ramnik
    2 years ago

    I am getting the same set of errors as Rudy even when I have made all the changes very carefully. Infact it worked a few times and then it started throwing error for the same piece of code.

    Ramnik
    2 years ago

    I think the problem happens at this place
    Application("Numbers").documents[0]

    this gives an invalid index error.

    2 years ago

    Ramnik: Make sure you have one and only one document (window) open, no tabs. Quit and restart Numbers maybe, with no documents open and open only that one. Keep working to debug the issue.

    Ramnik
    2 years ago

    As I continue to debug it further i notice that now even this line
    var doc = Application("Pages").documents[0];
    has started giving the invalid index error.

Comments Closed.