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.