From Address Book to Excel for a Mail Merge

Q. I need to do a mail merge with Excel and Microsoft Word. How can I get my addresses out of my Address Book and into Excel?

Answer: You have a couple of options.

Address Book Exporters

Either of these will allow you to export your address book as a plain text file that you can import into Excel.

Use AppleScript

If you haven’t used AppleScript for anything before, there is a small and easy set up — but the rewards are vast. After you have it set up, you can do many, many, time saving things with it.

AppleScript is a simple but powerful scripting language built into Mac OS X. With AppleScript, you can automate repetitive tasks and transfer data from one application to another. If you are like most of us, you have certain tasks that you do over and over again on your computer. Maybe something like one of these:

  • Send a weekly email to the same group of people
  • Resize a batch of photos for another project
  • Upload files to an FTP every month/week/day
  • Take a copy of an email and duplicate it into a special folder for safekeeping
  • Or something else that is repetitive.

The list of repetitive tasks go on and on. If you have a repetitive task, AppleScript can probably help you.

In addition to repetitive tasks, you can also take care of loads of tedious tasks like:

  • Removing duplicate email addresses from your Address Book
  • Exporting a group of contacts
  • Removing duplicate alarms from iCal
  • Having iCal only show a certain series of dates
  • Managing your iTunes artwork, track lists, or playlists
  • Backups of selected files
  • Empty your cache files at once
  • Create a list of clickable links on a web page
  • Rename a hundred files or folders in one click

If you have an interest, you can program your own Applescripts, but you can also get existing Applescripts for all kinds of things. Lots of people write them and even give them away for free. Here are some I found by searching google for “AppleScripts”

  • Trevor’s AppleScripts: Trevor has a bunch of great scripts for removing duplicate alarms from ical, removing duplicate emails, phone numbers, and addresses from Address Book to ejecting local drives.
  • Mail Scripts: Here is a great group of scripts for Mail that include exporting addresses, removing duplicate messages, schedule mail delivery, changing your SMTP server and many more.
  • Doug’s Applescripts for iTunes.
  • Some Applescripts.

To get a better idea of what AppleScript can do for you, let’s enable it and add some scripts to it specifically for your Address Book import.

  1. We want your scripts to be easy to access by showing an icon in your menu bar. Go to Applications –> Utilities –> AppleScript Editor.
  2. While AppleScript Editor is launched, go to AppleScriptEditor –> Preferences
    applescript-preferences
  3. Check the “Show Script menu in menu bar”. You will see a new icon in your menu bar. applescript-icon
  4. Now you need to install some scripts! Go to this page and download “Mail Scripts 2.10. Now, install it by double clicking the icon. One day you may find some scripts may need to be added manually. If you need to do this, you need to find the scripts folder. To find the folder, go to the new scripts icon in your menu and select “Open Scripts Folder –> Open User Scripts Folder”. You can place them in there.open-scripts-folder
  5. Note in the illustration above the “Address Book Scripts” in the menu. That will show up after you do the installation (You will also note I have a lot of other scripts installed — so if your menu doesn’t look like mine, don’t sweat it). When you are ready just choose “Export Addresses” from that menu item and you are good to go. After it is done doing it’s magic, you can open the file in Excel to complete your mail merge.

If you like the idea of Applescripts, do a google search for AppleScripts and take a gander. You’ll get all kinds of ideas of free scripts that are out there and what will become big time savers for you. And when you learn to love AppleScripts, wait until I tell you about the Automator!

Posted in ,

Lena Shore

Lena is a full-time freelancer and nerd that specializes in web development, graphic design, and illustration. She enjoys building things, learning new things, pursuing creative endeavors, and giving free advice.

Leave a Comment





This site uses Akismet to reduce spam. Learn how your comment data is processed.

Archives