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: 47 Responses to “How To Mail Merge On Mac With Pages, Numbers and a Simple Script”

    Rudy Rugebregt
    9 months 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. ;-)

    9 months 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
    9 months 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?

    9 months 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
    9 months ago

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

    Dana Stevens
    9 months 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
    9 months 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

    9 months 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
    9 months 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
    8 months 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

    8 months 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
    8 months 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?

    8 months 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
    8 months 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
    7 months 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.

    7 months 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
    7 months ago

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

    AJ
    7 months 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.

    7 months 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
    6 months 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
    6 months 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.

    6 months 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
    5 months 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
    5 months ago

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

    5 months 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
    5 months 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

    5 months 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
    5 months ago

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

    5 months 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
    5 months 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
    5 months 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
    5 months ago

    Sorry, I meant Gary, not Mark! haha

    5 months 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 months 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 months ago

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

    Karmen Barker
    3 months 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
    2 months 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.

    2 months 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
    1 month ago

    Can I merge data from numbers into Apple Mail?

    1 month 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 weeks 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 weeks 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 weeks 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 weeks 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
    2 weeks 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?

    2 weeks 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
    2 weeks 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.

Leave a New Comment Related to "How To Mail Merge On Mac With Pages, Numbers and a Simple Script"

:
:
:
0/500 (500 character limit -- please state your comment succinctly and do not try to get around this limit by posting two comments)