This is a very useful script for interlinking Microsoft Entourage with Excel, as a demonstration of what can be done with Microsoft Office AppleScript that would never have been possible with Visual Basic for Applications (VBA). In fact, it would not be useful to do the Excel part of the script in VBA, since it cannot return results to AppleScript, not even using the run VB macro command.

People often want to export information — usually contact information — from Entourage to Excel. Without AppleScript, the only way to do it is to export the entire address book (and only the local "On My Computer" one) to a tab-delimited text file and then import it into Excel.

But then you're stuck with perhaps thousands of contacts you don't need, and about 65 fields, including no fewer than 13 e-mail addresses per contact, but they are missing category information that might be crucial. You have to spend hours manually deleting rows (contacts) and columns (fields). You could perhaps be a little more selective by doing a mail merge to Microsoft Word, but you still would not have your information in a format (a table) that you could export to Excel.

With AppleScript, it's easy. For this example, contacts are chosen by category — all contacts of the category "Work." You can make it whichever category you want, or even a combination of categories. Or, you can specify only contacts whose last name starts with "B" or contacts whose company is "Northwind Traders", or any whose filter on any field.

You can also select contacts in the address book or a custom view and get the selection, remembering to check the class of the items. You can specify only the fields that you are interested in, and no more. There is no need to delete any columns in Excel.

In the following example, there are 16 fields, but you might only need three or four — perhaps only last name, first name, e-mail, and (work) phone.

tell application "Microsoft Entourage"
	set theContacts to every contact where its category ¬
		contains {category "Work"} --OR:
	--set theContacts to the selection
     
	set allContactProps to {{"Last Name", "First Name", "Title", ¬
		"Company", "Department", "Email", "Work Phone", "Cell Phone", ¬
		"Home Phone", "Assistant", "Street", "City", "State", "Zip", ¬
		"Spouse", "Notes"}} -- header row as first sublist
	repeat with theContact in theContacts
		try
			set email to (first email address of theContact whose ¬
				label is work)
		on error
			try
				set email to default email address
			on error
				set email to ""
			end try
		end try
		tell theContact
			set contactProps to {last name, first name, ¬
				job title, company, department, email, ¬
				business phone number, ¬
				mobile phone number, ¬
				home phone number, ¬
				assistant phone number, ¬
				business address's street address, ¬
				business address's city, ¬
				business address's state, ¬
				business address's zip, spouse, ¬
				description}
		end tell
		set end of allContactProps to contactProps
	end repeat
	set numRows to count allContactProps
	set numColumns to count (item 1 of allContactProps) -- 16 here
end tell
 
tell application "Microsoft Excel"
	set newWkbk to make new workbook with properties ¬
		{name:"Work Contacts"}
	set lastCell to get address (get offset (range ¬
		"A1" of active sheet of newWkbk) row offset (numRows - 1) ¬
		column offset (numColumns - 1))
	set theRange to range ("$A$1:" & lastCell) ¬
		of active sheet of newWkbk
     
	set value of theRange to allContactProps
	autofit theRange --? could make Notes column very wide
end tell

In the first line of the Entourage section, you get the contacts. The example is for contacts with the category "Work." Note the instances of where its instead of whose, which are necessary for the category class. Notice also that the syntax of the contains operator requires that the contained item ({category "Work"}) be of the same list class as the containing item (category is a list, since contacts can have multiple categories).

This is essential. If you don't put the list brackets { } around category "Work", you get an error message can't make category id 4 into a vector.

It's easy to miss this because most of time you are dealing with lists of strings, and AppleScript coerces a string to a single-item list if you forget the list braces. But this coercion doesn't exist for Entourage objects, so remember the list braces { } for the contains operator, and its inverse operator, is in, when the larger containing object is a list.

Alternatively, you can get the contacts by selection instead of by category; that option is commented out in preceding the code sample. If you use that method, make sure that you check the class of the selected items (it should be contact) and use an inner repeat 1 times loop so that you can skip items that are not contacts via exit repeat.

The allContactProps variable is a list of lists, which you use for the value of (two-dimensional) ranges containing rows and columns. You initialize it with a header row containing the names of the fields you want to get — 16 here. If you want fewer, or more, change it. You can use whatever descriptive names that you want for the fields here.

The next bit tries to find a relevant email address. A contact can have several e-mail addresses (as many as you want), so this is a bit trickier than getting properties. Normally you'd look for the default email address, which you can also get by just asking for the address of the contact. But in this case, since the only info you want is work-related info, the script attempts to get the first email address of theContact whose label is work.

Note that a coercion always resolves email address class to its contents property — the actual string address — if you don't ask for another property such as label. Versions of Entourage before Entourage 2004 had only this contents property, before label was added. So retaining this coercion prevented older scripts from breaking.

You try to get the default email address only if trying to get the first email address fails (on error). This works even if it's a home e-mail address, and if that fails (there's no email address for this contact at all), you set the email variable to "" so that it doesn't error later.

Now you can get the other properties that you want. Note that using the tell theContact syntax is the only way to do it all in one line as a list and still be able to incorporate the email variable that you have just set. If you try it as

set contactProps to {last name, first name, ... email, ... } of theContact

it will error, since the compiler doesn't recognize email as a variable and there is no email property of the contact object. The tell statement works because it first tries to resolve words to application keywords — preferably properties of the told object (theContact) — and only if it can't find any such application keyword the compiler then accepts variables.

Excel will need the number of rows (that is, the number of contact records) and the number of columns (the number of fields in each record, namely 16 in this example; you can pick any sublist to count, so it might as well be the first one). And Entourage's work is done.

In Excel, you make a new workbook, and then get a range of the right size before you can set its value (fill it) with your data. Otherwise, the script will error. In Excel 2004 AppleScript, you can refer to ranges only by their A1 format names: R1C1 format does not work.

That means that knowing the number of columns does not immediately help — you need to know the letter (character) of the last column in order to specify the range address. The simplest way to get that is to get offset of range (cell) "A1" using the number of columns less 1 (since column A is already counted) and the number of rows less 1 (for the same reason). That gets the last cell of the range, not the whole range.

Using get address of that last cell (range) gets the A1 address format that you can use. (This gets the whole A1-style address, which you need.) The result is always in "absolute" format, $A$1, so you use the same for the first part of the range address (top left corner) and the lastCell result that you just got from the get address command for the second part (bottom-right corner of the range).

Now you can set the value of this range to your list of lists, and the worksheet immediately fills with the data. You can then autofit the range so that the columns expand to the width needed to see the content of every cell completely.

If you have voluminous notes for some contacts, autofit may make the Notes column too wide, even though it is the last column. Some rows may also be too deep as a result of many lines (carriage returns) in the contact notes, but there's probably not much you can do about that unless you set row height of the sheet to be exactly a particular height. To avoid a huge width, instead of autofitting the entire range, you can simply autofit every column except the last one. Columns are ranges, too, so autofit will work on any column individually.