E-Mail Merge with Thunderbird

THESE INSTRUCTIONS ARE FOR INTERNAL EECS USE ONLY

See below for info on mail merge within bMail.

This procedure explains how to create multiple, customized e-mail messages using data in a spreadsheet.

  1. One-Time Setup
    1. Set up Thunderbird to Work with bMail
    2. Install Mail Merge Add-On
    3. Set up Thunderbird so your mail looks like it is coming from someone else - IF NEEDED (separate page)
  2. Procedures for Sending a Mail Merge
    1. Create a Spreadsheet
    2. Send a Mail Merge Message

  1. One-Time Setup

    1. Set up Thunderbird to Work with bMail

      1. Set bConnected (Google) Key
        • Go to: https://idc.berkeley.edu/mmk/auth/index
        • Authenticate, choose "bConnected," and then choose the account name from the list
        • Click the "Set Key" button and then the "Define your own" tab
        • Create your own key (do not auto-generate it). Choose a passphrase that is at least 9 characters and which will be easy for you to remember. Click "Set Key" button again and you should get a confirmation page on your browser and an email confirmation from donotreply@berkeley.edu.

      2. Enable IMAP in bMail
        • Open bMail, click on the gear icon (top right), and select "Settings"
        • Click the "Forwarding and POP/IMAP" tab
        • Select the "Enable IMAP" radio button
        • Click "Save changes"

      3. Configure Thunderbird for bMail
        • Open Thunderbird
        • Pull down "File" menu and select "New..."
        • Select "Existing Mail Account"
          • Your name: [your full name]
          • Email address: [your fully-qualified @berkeley.edu address] (e.g. magdalene@berkeley.edu)
          • Password: [your new Google key from step 1]
        • Click "Continue" button
        • Click "Manual Configuration" button
          • Incoming: IMAP   imap.gmail.com   993   SSL/TLS   Autodetect  
          • Outgoing: SMTP   smtp.gmail.com   465   STARTTLS   Autodetect  
          • Username: [your fully-qualified @berkeley.edu address] (e.g. magdalene@berkeley.edu)
        • Click "Done" button
        • Pull down your "Tools" menu and selecting "Account Settings." In the left-side column click on "Server Settings" under your account name. In the right-side pane, click the dropdown menu next to "Authentication method:" and select: 0Auth2
        • In the left-side column click "Outgoing Server (SMTP)." In the right-side pane click your @berkeley.edu address and then the "Edit" button. Click the dropdown menu next to "Authentication method" and select: 0Auth2. Click "OK."
        • If needed, make your berkeley.edu account your default Thunderbird profile by clicking on your @berkeley.edu address in the left side column, and pull down the dropdown menu at the bottom of the column and select "Set as Default."
        • Click "OK"
        • Close and re-open Thunderbird: Remember to log in with your Google key.
        • If you plan to continue to use the bMail web interface, DO NOT MANIPULATE YOUR EMAIL IN THUNDERBIRD.

        NOTE: If 0Auth2 does not appear in the dropdown menu you probably need to update to the latest version of Thunderbird. Also, in one instance, OAuth2 wasn't an option when setting up an account, but you could go back to server settings after the fact and change it to OAuth2. In another, OAuth2 never did work and we had to fall back on "normal password" and telling Google to allow "less secure apps".

        Other information can be found in the Berkeley campus KB article

    2. Install Mail Merge Add-On

      Since the Mail Tweak Thunderbird extension is overkill for our needs, we now recommend Mail Merge. All of this should be done as the user who wants to send the mail; no administrator privileges are required.

      1. In a web browser, go to the Mail Merge installation page: https://addons.mozilla.org/en-US/thunderbird/addon/47144/ and click "Download Now."
      2. It may take a moment or two, but a dialog box will pop up asking what you would like to do. Choose "Save File" and put the .xpi file on your desktop. (It might automatically go into your "Downloads" folder, which lives in your "My Documents" folder, which is fine too.)
      3. Start Thunderbird, pull down your Tools menu and select "Add-ons."
      4. From the options (cog icon) button next to the add-on search field, select "Install Add-on From File.."
      5. Find the .xpi file you downloaded and click "Open."
      6. Click "Install now"
      7. Click "Extensions" in the left-side column and, in the right-side pane, click "Restart Now" (next to "Mail Merge")

      Now the extension is set, and you can send a mail merge message any time.


  2. Procedures for Sending a Mail Merge

    1. Create a Spreadsheet

      1. Create an Excel spreadsheet with the data you want in the message, including the full e-mail address. Make sure there is a header row with names for all columns; these names must not contain spaces. (The file should look something like this snapshot.)
      2. Save the spreadsheet as "CSV UTF-8 (Comma delimited)(*.csv)" (see snapshot of drop down options) (If you closed Excel and viewed this file in a text editor, it would look like this snapshot.)

    2. Send a Mail Merge Message

      In Thunderbird

      1. Start a new message. Any parts of the spreadsheet that you would like to include in your message must be enclosed in 2 sets of curly braces {{ }}.
        1. In the To: field, put the column name you chose for the email address surrounded by 2 sets of curly braces: {{columnname}}.
        2. Add a Subject
        3. Draft the body of the message, enclosing the column headings for any parts of the spreadsheet that you would like to include in 2 sets curly braces.

        Using the example spreadsheet created above, you message would resemble this snapshot.

      2. Pull down the "File" menu and select "Mail Merge."
      3. A dialog box should pop up. In the CSV section, click "Browse" and find your .csv file. In the Field Delimiter dropdown, select the single quote('). In the Text Delimiter dropdown, select the double quote("). Then click "Ok." (Using the example above, the dialog box should look something like this snapshot.)
      4. Click "OK." The message should disappear. Still in Thunderbird, look under "Local Folders"and you should see a folder called "Outbox" ere with some new messages in it (one for each email address on your spreadsheet). (Sample snapshot.)
      5. When you open a message you should see the fields from the spreadsheet filled out.* (Sample snapshot.) When you are ready to post them, pull down the "File" menu and select "Send Unsent Messages."

      The mail merge add-on has online documentation if you'd like more detailed information.

      3. * If you draft your message in plain text (rather than html), you may end up with a number of blank spaces at the beginning of your message. To avoid this problem, before you draft your message:

      • Pull down your "Tools" menu and select Add-ons
      • Click the "Extensions" tab and then the "Options" button under Mail Merge.
      • In the pull-down menu next to "Format," select text/plain
      • Click "OK" and close the "Add-ons" windows. You may now draft your message.

Mail Merge in bMail

Mail merge available through bMail has a limit on the number of messages for the free version so we advise that you do not use it at this time.

These instructions tell how to use the third-party Google Apps add-on Yet Another Mail Merge to do mail merge using a bDrive spreadsheet and the bMail web client. We recommend that you use a departmental (aka SPA) account for this when possible. If your personal account has already been configured to send mail as someone else (e.g. department or committee chair) then you'll want to use it.

One-time setup: Create a new spreadsheet in bDrive. Go to the Add-ons menu, Get add-ons, search for Yet Another, find Yet Another Mail Merge, and click the Free label to install it. Read the list of permissions and grant the add-on access. (If you have questions about the permissions, contact acg@eecs.) You'll need to do this once for any account you use for mail merge.

Doing a mail merge:

  1. Created a spreadsheet in bDrive on the account you will be using to send the mail. The spreadsheet should have a column called Email Address with the addresses, plus columns for any other info you'll want to include in the message. If you already have an Excel spreadsheet, you can copy and paste into the Google sheet or use File, Import.
  2. Draft your message in bMail. Use double angle brackets around the column header to pull in info from the spreadsheet, e.g. <<lastname>> You can use spreadsheet fields in the subject or body of the message.
  3. In the spreadsheet, go to the Add-ons menu, Yet Another Mail Merge, Start. It will walk you through doing the merge. Send yourself a test message to make sure it's all correct.

There is documentation for Yet Another Mail Merge if you have questions.

Maggie Crowley
maggie@eecs
created 3 December 2010; rev. 12/4/15 by janp to add bMail