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.
You can also watch this video at YouTube.
Watch more videos about related subjects: JavaScript (14 videos), Pages (226 videos).
You can also watch this video at YouTube.
Watch more videos about related subjects: JavaScript (14 videos), Pages (226 videos).
Want to know more about how to use Pages on your Mac?Video Transcript
Hi, this is Gary with MacMost.com. Today let me show you how to script a mail merge using Pages and Numbers on your Mac.
MacMost is brought to you thanks to a great group of more than 750 supporters. Go to MacMost.com/patreon. There you can read more about the Patreon Campaign. Join us and get exclusive content and course discounts.
I often get asked if it's possible to do mail merge using Pages. What does mail merge mean? Well there are usually one of two things that people are talking about and I don't like either of them. So the first is actually send out an email to a large group of people and have each email be customized. You shouldn't be using your Mac for that at all. Instead you should be using a professional email service like the one I use to send out the MacMost NewsLetter. Sending it out from your personal email account is a good way to get yourself banned from your email service or marked as a spammer. Even if your email is legit you're not supposed to be sending out mass emails from a personal consumer email service. So look into a Pro email service for doing that and they have tools for that. The other thing mail merge means is to printout sheets of paper that are personalized for each person. So this could be useful in some situations although it could also be a big waste of paper. So let's say, for example, you need to printout a piece of paper for everybody in your organization, company, or school and it has to have their name on it and maybe some other pieces of information. Like, for instance in school, this could be something in the beginning of the year where it has a person's name on it, instructions how to use their locker, and their locker number. Each piece of paper will be a little different.
So here in Pages we've got an example. So you could see there is the student's name at the top and there's a spot where the locker number will appear. Now my goal here is to provide you with a really simple script. Not some long complex thing that most people won't understand but something that's the fewest number of lines possible that just about anybody can look at, understand, and then maybe continue to modify it to meet your needs. So here in Pages what I'm using are two text boxes. This is regular body text but I have two text boxes here. This one and this one. Now you may thing why aren't I using placeholders. Well, placeholders don't really work for this because the idea of a placeholder is you put them in templates. In templates you open up, change what's in the placeholder, and then save it as a new document. Here we want to actually have multiple versions of this. So you might have 30 or 100 students. Once you change the placeholder it changes and it's no longer a placeholder. So you can use it for one student but not for more than one. But with a text box we can easily identify the places where we want to change something.
So I've got just two text boxes here which will make it easy. If you already have text boxes in your document you could still do this and I'll show you how to figure out which text boxes get changed. So this is our entire Pages document. It's really simple. Now let's look at Numbers. Numbers I want to keep really simple too. So I have a simple document with a single sheet and a single table in it. The table has only two columns. It just has one row for each student. As an example here I'm just going to have three. I'm not using the first row. I'm just keeping that as a header. I have no blank rows or anything like that. It's very important to keep this concise because if you have blank rows it may give you error messages.
All right. Here's my script. I told you I just wanted to keep it to just a few simple lines. We're going to do this in Script Editor. But you could do it in Automator too. But since there are no additional actions it's simplest just to do it in Script Editor. Now we're going to switch from Apple Script to Java Script. It's important that we have it as Java Script because this is Java Script. If you don't change this it won't work. Now we're going to set two variables. doc and table. Doc is going to hold the Pages document. You can see Application("Pages") and it's going to look at the documents that are open and at the zero item. In computer code zero is the first item. So the first document. To avoid confusion make sure you have Pages open and only that one document open so it can't accidentally grab the wrong one. Then the same thing here for the Numbers document. The one and only Numbers document that you have open. But it's also going to look at the first sheet and the first table in that first sheet. So keep things simple and make sure you just have one sheet and one table in that Numbers document. It will store that in the variable table. Now it's going to loop and it's going to do a very simple loop. But instead of starting at zero, the first row, it's going to start at row 1, the second row. That's because in this Numbers document the first row are just the headers so we don't want to have a sheet that says name and user ID. We want to start with the first student. So that way we're starting with 1 instead of zero. Now we're going to go through every row until the number of rows that are there increasing by one each time. So it's going to go to the second, third, and fourth rows in the Numbers document. For each one it's going to look at the Dock and look for text items which will be those text boxes. Then it's going to take the first one, it's going to look at the object text of that and set it. What's it going to set it to? Well, it's going to look at that table, the row that it's at, the first cell in that row. The value of that first cell in that row and it's going to convert it to a string just to make sure that it's the right format to put in that text box. Then it's going to do the same thing but with the second text box and the second cell. So what it should do is take this name and this number and then place it in this text box and this text box. Now one tricky thing is we don't know for sure that this is the first text box and this is the second text box. How will we find out? Well, we'll just try it and see. Watch what happens when I actually run this script. I'm going to comment out this line for now and then put a break here. What will that do? It won't do this export which we haven't even gotten to yet and it will break after the first time through this loop. So it will just use the first name. Let's run that and then see what happens.
So what we find here is that it worked, kind of. It put one of the pieces of information in this text box and one in this one. It didn't do them in the right boxes. So it looks like this is actually the second text box and this is the first. No problem. All we need to do is switch the rows. So use 1 here and zero here. So it's going to put the first cell into the second text box and the second cell into the first text box. Now let's try it. Now we can see it did it right! So let's uncomment this line and get rid of that break and take a look at this line here. It's going to use the export function in Pages. Then inside here we include two pieces of information in the curly brackets. The first is to and we're going to give it a path name. Now to keep things simple we're actually going to manually enter the path name. So we have to use Path and then parenthesis and then quotes. Here I'm going to say okay users and then my user name/document folder/and then I've created a folder called Main Merge Example and then I'm going to name it export and outside of the quotes there I'm just going to add the number that represents i, so 1, 2, and 3, and then back inside the quotes put dot pdf. Then the second piece of information is how do I want to export because you can export multiple ways out of Pages. I'm going to use pdf to export a pdf. So now when I run this script it should create three pdf's. Let's move this aside here so we can see the Pages document in action as I run it. So we can actually see it as it goes through each one.
Now here's that folder and you could see I've got my Numbers file there and my Pages file there, and also that script there as well. It has added three pdf's. Export 1, export 2, and export 3. If I use QuickLook I could see that it has got the first name and the first locker number. This is the second name and the second locker number, third name and third locker number. So now it has worked. I've got three pdf's here. I could print these pretty easily now. I could select all three of the them and do File and then Print and it would use Preview, the default app to print them. I could also select all three of them and then use a QuickAction. One of the default QuickActions is Create PDF and it would create a single PDF from all of those. So now when I open them up in Preview you could see I've got each one on a separate page and now I could just print this document.
So a few more things I want to point out to you. In System Preferences, Security & Privacy under Privacy I've got Full Disk Access turned on for Script Editor. Otherwise Script Editor may not have the permissions to do everything it needs to do. I'm using two slightly different types of text boxes here. This is just a normal text box and I've just placed it here at the beginning of the document. This one is a little trickier. I've actually gone and Under Arrange set it to Move With Text and Inline With Text. So it actually acts as like a character. I can move it anywhere in the text. This makes it really handy if you want to insert a piece of information inside of a paragraph. Of course you still have to have kind of a default width for it so it's still not perfect. This won't really work well if you want to actually change something in the middle of a paragraph. But hopefully your task is flexible enough that you could use text boxes like these to get the job done. The other thing is we could do better here with the naming for this. Instead of Export and then a number dot pdf we could actually use one of these things here, like for instance, let's grab the first cell which is the name here. I've going to copy that and I'm going to paste that in instead of just the number. In addition to that I can get rid of the word export at the beginning and maybe use something like locker number with a space and then the name dot pdf. So now when I run this I'll actually get file names that make a little more sense. You could see here they are. It actually has locker number and then each person's name.
Now I also mentioned that if you use other text boxes that things might get a little more complex. So notice how I had to switch these numbers here to put the right information in the right text box. Well, if you had say seven text boxes but were only matching three of those to three columns in Numbers you're going to have to do even more here to figure out which text box is which. I would have like this one be text box 3 and maybe this text box 5 and maybe another one is text box 1 and then the others don't get changed at all. So you kind of have to play around with the numbers there to match the text boxes to the column in Numbers. I'll include the Pages and Numbers and Script file for this at the post at MacMost.com so you can download them and get a quick start on creating your own mail merge process.
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.
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. ;-)
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.
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?
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.
I have the same situation as Rudy too, I will try to change the path the location of the file.
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.
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
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.
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...
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
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?
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?
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.
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.
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.
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.
Gary, thank you so much, you've made my day. It worked instantly and saved me a lot of headache.
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.
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.
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.
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.
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.
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.
Gary, How do you get Quick Action enabled in Finder? I'm trying to combine the highlighted individual files.
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.
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
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.
Is it possible to send the pdf from the script by email as an attachment?
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.
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!
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
Sorry, I meant Gary, not Mark! haha
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).
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.
Karmen: Not sure, but using older versions of macOS (and Numbers and Mail) will probably not work too well.
@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.
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.
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.
Can I merge data from numbers into Apple Mail?
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.
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.
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.
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!!
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.
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?
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.
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.
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
iker: Just check everything carefully. Make sure the path exists and you have typed it perfectly.
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?
Rob: Try Desktop instead of desktop.
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.
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.
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?
Alex: No easy way, no. You could use the skills you learned from this video to make an Automator script to do it though.
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.
I think the problem happens at this place
Application("Numbers").documents[0]
this gives an invalid index error.
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.
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.