Mail merge

Pages doesn't have built-in Mail merge but it's quite easy to mail merge to envelopes from Numbers with a short script:

In Pages, File > New and pick the envelope template you want. Delete the "to" placeholder text and replace it with a new Text Box (Insert > Text Box) and style it the way you want, with font size, etc. Make sure it is tall enough to hold the address with the most number of lines. An easy way to do that is simply enter the "tallest" address. It will be replaced with others as the script runs.

In Numbers arrange the data similar to the screenshot, with one Header Row. Each row cell will print on a separate line so have first name and last name combined in one cell, not in separate cells. You can add/insert columns as needed and some cells in the middle (for example the second line of an address) can be blank but make sure there are no blank rows at the bottom. As written the script assumes the data will be in the first table on the first sheet of the frontmost document.

Open Script Editor (in Applications > Utilities), choose Javascript in the dropdown upper left (i.e. change it from the default AppleScript), and copy paste the following script:

var table = Application("Numbers").documents[0].activeSheet.tables[0];
var doc = Application("Pages").documents[0];
var totxt = doc.textItems[0].objectText;

for(var i=1;i<table.rowCount();i++) {
	var rowvals = table.rows[i].cells.whose({_not:[{value:null}]}).value().join("\n");
	totxt.set(rowvals);
	doc.print();
}

With the Pages and Numbers documents open, click the Run button in Script Editor. If "nothing happens" make sure Script Editor is listed and checked at System Preferences > Security & Privacy > Privacy Accessibility.

Test on a short table first to make sure it is doing what you want. Setup should take only a few minutes.

SG

And here's a script that inserts personalized information value into a letter (something that can't be done via the Contacts app).

Working from this Numbers table:

And a saved template under My Templates in the Template Chooser at File > New that looks like this:

(The grey portions show where I selected text and chose Format > Advanced > Define as Placeholder Text. Normally they wouldn't be gray. The black parts are where text has been typed in, replacing any placeholders that were originally there. )

"Out of the box" (not much tinkering needed below the first line except to uncomment the print line when ready) this script will produce letters that look like this with a click:

const mytemplate = "My Pages Template"  // change to your template name

const cols = {fname:"B", lname:"A", mname:"C", street1:"D", street2:"E", 
              city:"F", state: "G", code:"H", salut:"I", usr1:"J", usr2:"K"};

             // change column letters between " " above to match Numbers table

Object.keys(cols).forEach(key => cols[key]="ABCDEFGHIJK".indexOf(cols[key]));
             // "A" offset 0, "B" offset 2, "C" offset 3, etc.

const pages = Application("Pages"), 
      t = pages.templates[mytemplate],
      table = Application("Numbers").documents[0].activeSheet.tables[0],
	  notNull = value => value !== null; //function used in filter()

for(let i=1;i<table.rowCount();i++) {

    let rv = table.rows[i].cells.formattedValue(); // get formatted values for row
	
	// name address block
	let name = [rv[cols.fname],rv[cols.mname],rv[cols.lname]].filter(notNull).join(" ");
	let street = [rv[cols.street1],rv[cols.street2]].filter(notNull).join("\n");
    let ctystcode = [[rv[cols.city], rv[cols.state]].join(", "),rv[cols.code]].join(" ");
	let nameaddr = [name,street,ctystcode].join("\n");

	// if remove these, also remove set() below, and placeholder(s) in Pages template
	let salutation = rv[cols.salut];
	let amount = rv[cols.usr1];
	let date = rv[cols.usr2];
	
	// make new doc from template and fill in placeholders
	let doc = pages.Document({documentTemplate:t}).make();
	doc.placeholderTexts[0].set(nameaddr); 
	doc.placeholderTexts[0].set(salutation); // 0 as now 1st placeholder
	doc.placeholderTexts[0].set(amount); // ditto
	doc.placeholderTexts[0].set(date); // ditto
	//doc.print();  // when ready to print, uncomment (remove first set of //)
	
	//pages.activate(); // uncomment if want Pages to pop up in front
    //doc.delete(); // uncomment to delete each doc after print
	//doc.close();  // uncomment for prompt to save each document during printing
} 

SG

Pages has not had built-in Mail merge since it was rewritten in Oct 2013, so no to merging content from a spreadsheet.

Here's revised print envelopes script, mapped to the columns in the "new" Numbers table.

As before, this depends on having typed in return address into template and creating and creating and formatting a text box for the "to" address. Other that, not much work to do.

const cols = {fname:"B", lname:"A", mname:"C", street1:"D", street2:"E", 
              city:"F", state: "G", code:"H", salut:"I", usr1:"J", usr2:"K"};

             // change column letters between " " above to match Numbers table

Object.keys(cols).forEach(key => cols[key]="ABCDEFGHIJK".indexOf(cols[key]));
			 			 
const table = Application("Numbers").documents[0].activeSheet.tables[0],
      doc = Application("Pages").documents[0],
	  notNull = value => value !== null; //function for filter()

let totxt = doc.textItems[0].objectText; //or try 1

for(var i=1;i<table.rowCount();i++) {
    let rv = table.rows[i].cells.formattedValue(); //values on a row

	let name = [rv[cols.fname],rv[cols.mname],rv[cols.lname]].filter(notNull).join(" ");
	let street = [rv[cols.street1],rv[cols.street2]].filter(notNull).join("\n");
    let ctystcode = [[rv[cols.city], rv[cols.state]].join(", "),rv[cols.code]].join(" ");
	let nameaddr = [name,street,ctystcode].join("\n");
	
	console.log(nameaddr);
	totxt.set(nameaddr);
    doc.print();
}

SG

If you need address labels (as opposed to envelopes) you can download a Pages template for labels from the Avery site and use a slightly modified script like this:

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

for(var i=1;i<table.rowCount();i++) {
    var totxt = doc.textItems[i-1].objectText;
	var rowvals = table.rows[i].cells.whose({_not:[{value:null}]}).value().join("\n");
	totxt.set(rowvals);
}

It won't necessarily fill out the labels sheet in order from top left, but it gets the job done without much fuss.

SG

southcoast-dweller wrote:

The text boxes are inserted in a blank Pages document.

Glad to see you are still working on this. You should be almost there!

To troubleshoot:

Make sure you don't change anything other than the column letters between the "". It looks as if you changed 'fname' to FIRST, 'lname' to LAST, etc. That won't work because lines below in the script are expecting the original names. (They don't have to be the same as the column headers in your table; it's just important that the column letters are mapped correctly). I suggest you start again with the original script and don't change anything other than column letters, if needed.

And are you using not a "blank Pages document", but the labels template included in the download? The script expects you to be using a document based on that template (or a similar labels template for Pages.)

SG

That's encouraging! I should go beyond 10 rows, though. Are you using the downloaded labels template I included, or something else? For further debugging the devil is in the details. Perhaps you could post image of exact script you are using and also a screenshot of the table (disguising any names if necessary to avoid posting any personal information here). If you can include the rows before and at least a few after the 10th row that would be helpful.

SG

A commendable Mail merge solution. Avery has blank templates on their website, and at least the address label sheets are available as downloads in current Pages document format, so creation of Avery address templates as Pages templates can be as you have described. Those Avery sheets may require tweaks to the JXA code though.

In order to download a blank Pages Address label template (e.g 5160) from Avery, one must have an established account there beforehand. The Avery 5160 blank Pages template uses an unordered array of shapes, each with redundant object text. One has control over the color, size, and font face for each shape's object text. One could perform a loop from shape 1 to the count of shapes, and randomly enter address information until done. I have colored where shape 2 is located.

When one uses the following AppleScript, it builds a table of shapes in numeric order, but those shapes are not in visual order as you can see above.

use scripting additions

tell application "Pages"
	tell front document
		set tbl to shapes of it
	end tell
end tell



Yes, thanks for pointing that out. The order of the text boxes apparently can vary. Not sure why.

On my setup it's definitely textItems[0].objectText. If turns out to be the wrong box, then just change to textItems[1].objectText.

Thanks for pointing out that print feature in Contacts. Select a group, File > Print > Show Details and pick Envelope, Mailing Labels or other.

Understand some on Big Sur are having trouble dragging from Contacts to Numbers. Not sure how well the other direction is working.

In any case, the simple script definitely works for me in the here and now. No fussing with csv export/import. One click.😀

SG

JXA. Great when it works, and good to see you using it here.

I wrote a JXA script that prompts for an image file (including Camera RAW), and then dumps out the EXIF, etc. information. Worked on Mojave, and returned nothing on Catalina. Bump. The same Cocoa code in Python and Swift works just fine on both platforms.

These are on my Google Drive. The first is the AppleScript calling a JXA function, and the Python script does the same thing. Python is clueless about Apple appearances (light/dark mode). One can use JXA inside Python to provide a dark file chooser, but the scrollable window has to be gamed for light/dark effect.

xpd.applescript.zip

wexifx.py.zip