10/20/20
9:00 am

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.
You can also watch this video at YouTube.
Watch more videos about related subjects: JavaScript (14 videos), Pages (225 videos).

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.

Comments: 59 Comments

Rudy Rugebregt

5 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. ;-)

Video Transcript

Comments: 59 Comments

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information