Mailman Mailing Lists at UNH
Recipe R01 explains how to export subscriber data from your spreadsheet using a Mailman friendly format.
You have a spreadsheet that contains a list of email addresses, and ideally, the names of the people associated with each, and you'd like to add them as subscribers to your Mailman mailing list. (It is assumed you know how to use cell formulas in your spreadsheet program.)
Add a new column to your spreadsheet and use a formula to construct a subscription entry in the form of:
"Flintstone, Fred" <firstname.lastname@example.org>
Your list has a Mass Subscriptions page which allows you to manually add one or more subscribers to your list. You can navigate to this page starting from your list's administration console page, or by going directly to:
Many list owners enter only the subscriber's email address when using this form. But the mass subscriptions feature also allows you to include the new subscriber's actual name. It is not uncommon for a subscriber to forget the original address used for their subscription. So with larger lists, having the subscriber's real name can be invaluable when trying to find this poor soul on your list.
The good news is that if you have dozens of email addresses in a spreadsheet, you most likely already have the subscriber names as well. For our example, let's assume that you have the new member's last name and first name in separate columns, and of course a column for the email address. There may be other columns as well for things like phone number, the date added, etc. which is fine.
Insert a new empty column in a convenient location. In our
example we are placing it to the right of the email address column
and naming it
In the first row of your new column, enter the following formula, making adjustments as explained below.
CONCATENATE("""", A2, ", ", B2, """ <", C2, ">")
Confirm the text built by the formula looks correct, then replicate the formula into the remaining rows that contain at least an email address.
When done it should look something like this:
|2||Hofstadter||Leonard||L.Hofstadter@example.edu||"Hofstadter, Leonard" <L.Hofstadter@example.edu>|
|3||Cooper||Sheldon||Dr.Sheldon.Cooper@example.edu||"Cooper, Sheldon" <Dr.Sheldon.Cooper@example.edu>|
|4||Hofstadter||Penny||Penny.H@sales.example.com||"Hofstadter, Penny" <Penny.H@sales.example.com>|
|5||Wolowitz||Howard||H.Wolowitz@example.edu||"Wolowitz, Howard" <H.Wolowitz@example.edu>|
|6||Koothrappali||Rajesh Ramayan||Raj.Koothrappali@example.edu||"Koothrappali, Rajesh Ramayan" <Raj.Koothrappali@example.edu>|
|7||Winkle||Leslie||Leslie.Winkle@example.edu||"Winkle, Leslie" <Leslie.Winkle@example.edu>|
|8||Physics.Dept@example.edu||", " <Physics.Dept@example.edu>|
|9||Rostenkowski‑Wolowitz||Bernadetteemail@example.com||"Rostenkowski‑Wolowitz, Bernadette" <firstname.lastname@example.org>|
|10||Farrah Fowler||Amy||A.F.Fowler@example.edu||"Farrah Fowler, Amy" <A.F.Fowler@example.edu>|
|11||Bloom||Stuartemail@example.com||"Bloom, Stuart" <firstname.lastname@example.org>|
|12||Kripke||Barry||Barry.Kripke@example.edu||"Kripke, Barry" <Barry.Kripke@example.edu>|
We use the
CONCATENATE() function to add
the needed punctuation characters around the name and the email
address, and to glue everything together. The order is
important; the name must come first, then the address.
The entire name must be surrounded by plain text quote marks
"). But to include a
quote mark within a quoted string, it must be repeated. This is
why we have the odd construct of four quote marks in a row
to create the leading quote for the subscriber's name.
Notice that the entry for the Physics Department will be given
the 'real name' of "," because the name fields were blank. This
will not cause a problem for Mailman and the resulting
subscription can be entered as is. However you can touch up the
cell data by hand, or if you are a real spreadsheet ninja, you can
IF() function to conditionally add the
name. (This is left as an exercise for the reader.)
The "Last, First" name order for the subscriber name is just a recommendation; Mailman actually doesn't care how the name data is formatted. In the example above, the formula for a "First Last" name order would be:
CONCATENATE("""", B2, " ", A2, """ <", C2, ">")
Likewise it should be clear that if your spreadsheet contains the member name in just a single column, you would use just that single field value. So if the name was in column A and the address in column B, the formula in your new column would be:
CONCATENATE("""", A2, """ <", B2, ">")
Subscribers can be added by either cut-and-paste or doing a file upload.
Warning: To avoid possible processing issues, we recommend that you add or invite no more than 50 subscribers at a time. If you have a very large number of subscriptions that you need to add to your list, contact the List Server Admin for assistance.
The easiest way to import your subscriber data into Mailman is to simply cut and paste from the subscriber column into the Mass Subscriptions text box. In this example show what it would look like to send an invitation, with our set of addresses, as well as a custom welcome message. See Pro Tips and Tricks topic below for more info.
An alternative is uploading a text file using the Browse button. If you choose this option, keep the following points in mind:
Export only the new "Subscriber" column.
Export the data in a plain text format. Do
not use "CSV" format since this will add additional
punctuation characters, breaking the syntax of your entries.
Instead use "tab delimited" with a file extension of
It is a sad fact that there is no way to know if an email address is valid and deliverable without actually attempting to send a message to it. And if the addresses you have were provided by the subscriber, there is a good chance that at least a few of them will be invalid. If you want to know immediately that a new subscriber address is non-deliverable, take the following steps before doing a bulk add:
Go to your list's administration console page and select Bounce Processing.
Make sure that "automatic bounce processing" is turned on. This is the first option in the Bounce detection sensitivity section. (This the default setting for your list when it was initially created.)
Scroll down to the bottom of the page to the Notifications section. All of the options in this section should be turned on.
Press the Submit Your Changes button to update your list' configuration.
Yes, when you passed out the sign-up sheet, it was clear to everybody that they were providing an email address to be included on your mailing list. Just the same, consider sending an "invite" rather than slamming the subscriber onto your list. The Mass Subscriptions page makes it just as easy to send an invitation, and you can include custom text as part of the invitation reminding the would-be subscriber how you came to have their email address and why you believe your list will be of vital interest. See above for an example.
Advantages to this approach include:
Avoids adding invalid addresses to your list when the invitation is not deliverable.
Because of forwarding, the address you have may not be the subscriber's actual return address. By responding to your invitation, the subscriber's actual return address will be subscribed, which is almost always preferable, particularly for discussion lists.
Increases the odds that the subscriber will remember that they voluntarily signed up for your list and thus not view messages from your list as spam.
It is likely that your spreadsheet existed before your list, or that you are using it to keep additional data about your subscribers. Consider adding a column for the date the member was added to your Mailman list. Sorting by this column will make it easier to identify and add new subscribers to your list.