Quick email list
May 25, 2010 at 11:20 AM by Dr. Drang
Continuing my series of occasional posts on scripting as a way of quickly eliminating the drudgery of computer work, today I’ll describe how I generated a nice-looking list of email addresses from a spreadsheet.
My wife and I are the parent organizers of the water polo team at our neighborhood pool, and this morning I needed to send out an email to the parents of all the kids on the team. I have the team roster in a Numbers spreadsheet,1 with a line for each team member and columns for names, ages, phone numbers, email addresses, and so on.
Now I could have started by just copying the column of email addresses, but I wanted the address list to include the real names of the recipients. When I get mass emails, I always appreciate getting the real names; if I have to send a message to someone on the list, I don’t have to guess who’s who from a list of cryptic addresses. So I added a column to the spreadsheet and used this formula
=CONCATENATE(Mother," ",Last," <",Email,">")
to assemble addresses that looked like this:
Ashley Jones <aljones47@comcast.net>
I used the mothers’ names because they’re the ones who run the family calendars. If Numbers didn’t allow me to use the column headers as variable names (Mother, Last, Email), I would have used A1-style cell addresses to accomplish the same thing.
I get a lot of emails that use this format
"Ashley Jones" <aljones47@comcast.net>
The quotation marks aren’t necessary and seem stupid to me. It’s as if you’re saying her name isn’t really Ashley Jones, that’s just her CIA-supplied alias.
(The example name is sort of a joke. None of the 40-something moms are named Ashley—they have sturdy 60s names like Mary and Vickie and Patty. It’s their daughters who are all named Ashley.)
OK, so that gave me a column with real names and addresses. I copied the column (sans header) and pasted it into TextMate, which gave me a bunch of lines. Then I used the magic of Unix to prune and finalize the format of the list.
There are a lot of siblings on our team, which meant that the raw list of addresses would have duplicates of moms with more than one kid. So I used TextMate’s Filter Through Command… (at the bottom of the Text menu; shorcut ⌥⌘R) to eliminate the repeats.
The sort command gathered all the duplicates together, and uniq2 deleted the repeats. I needed to sort the lines first because, from the uniq man page,
Repeated lines in the input will not be detected if they are not adjacent, so it may be necessary to sort the files first.
The vertical bar piped the output of sort into uniq, just like on the command line.
With the list down to a minimum, it was time to change it into a comma-separated list. This find and replace
did the trick. Note that there’s a space after the comma in the replace string.
Now I had all the addresses, one for each mom, in a format that could be copied and pasted into the To field of an email message. I saved the TextMate file in my Dropbox folder, so it’s available at both of my computers. (Yes, I could have added these people to my Address Book and created a Water Polo group, but at the moment I doubt I’ll need to send more than one or two emails to this group. If I change my mind, it’ll be easy to add contacts to the Address Book from the To field in Mail.)
As is always the case, this took much longer to explain than to do. Because I already knew how sort, uniq, Filter Through Command…, and regular expressions work, the TextMate part was done in less than a minute. The most time-consuming part was generating the email addresses with real names in Numbers, because I wasn’t sure whether it concatenated strings through a function or through an operator like + or &. Even that took only a couple of minutes to figure out.
-
Let me note here that I dislike spreadsheets in general and have a particular dislike of Numbers, but since the
.xlsformat seems to be the lingua franca of the sales and management types who comprise most of the parental stock of my neighborhood, I have adapted. ↩
