Mail Merge for Batch Email

"Mail Merge for Batch Email" is a project built on a Google Sheets file with Google Apps Script behind to generate and send emails to a group of people based on a template.  It works similar to the mail merge function in major document editors. The first version came in 2017, and there have been newer versions with more functions, such as rich-text content, file attachment, multiple templates, inline images and QR codes, etc. 

The latest version is maintained by Charlotte.

User Guide

1. On the worksheet "DataSheet", prepare the data for mail merge:

   • Column "To Send": Enter "Yes" or "Y" for the records to process.

   • Column "Done": Indicate whether a record has been processed. (must clear it to send again)

   • Column "Email Address": Store the email address for each record. You may enter multiple email addresses OK; should be comma-separated.

   • Column "Attachment List": A comma separated list of the names of the files to send. File name is case-sensitive. All files must be saved in the same folder.  Refer to "Folder ID" below.

   • Column "SubFolder" (*NEW in v7.00): The subfolder name under the attachment folder. Must be a direct child. If no subfoler is given, the attachment folder will be used.

   • Column "Template Name": Name of the worksheet of the template to use.

   • Name of the column for inline image from a link, must start with "imglink", e.g. "imglink1", "imglink2", etc. Optional field data should be URL.(Note: This will embed the image in the email. If you want to use external image source, please use HTML code instead.)

   • Name of the column for inline image from a file in the attachment folder, must start with "imgfile", e.g. "imgfile1", "imgfile2", etc. Optional field data should be the filename of the image file in the attachment folder.

   • Name of the column for a QR code, must start with "qrdata", e.g. "qrdata1", "qrdata2", etc.. Optional field data should be the data for the QR code (text or URL).(Note: Google Chart API is used, and so it may fail to generate QR code if the Google Chart API is unavailable)

   • WARNING: The images are embedded in the email, so don't include a large image! To include a linked external image, write HTML code directly (accepted by MarkDown).

2. On the worksheet "Settings"

   • Data "Cc" and "Bcc": Add Cc and Bcc to each email. Can be comma-separated list.

   • Data "Folder ID":

     • Fill in the ID of the folder in Google Drive storing files for attachment; OR,

     • Click menu “Mail Merge for Batch Email” -> “Pick Folder for Attachment” to choose the folder.

     • Important: Leave this field blank for no attachment or the script will stop if the ID is invalid.

   • Data "Send as" can change the name (not email address) of the sender.

3. On the worksheet "Template1"

   • Note: "Template1" is the name of the default template. Don't change this name or else the script may fail to work.

   • Prepare the templates of mail subject and body.

   • Mail body now uses markdown syntax for rich-text formatting (See below for more information). You can also use HTML tags.

   • Use the syntax ${"fieldName"} for a merge field.

   • Create a new template by duplicating this template.

4. Click menu “Mail Merge for Batch Email” -> “Send Emails Now!”.

5. Click menu “Mail Merge for Batch Email” -> “Check Mail Quota” to check remaining daily quota.

6. You will receive a log by email after execution.

Special note for v6.20 or above: Automatic checking and re-running will be done in background but you may not see the message "The Script is Running". The column "Done" in the worksheet "Datasheet" should be updating if the script is still running.

Note

• This example is a derived work of "Simple Mail Merge", "Markdown to document converter" and "Showdown" at:

  • https://developers.google.com/apps-script/articles/mail_merge

  • https://github.com/showdownjs/showdown 

  • https://github.com/anshulguleria/markdown-to-doc 

• Limit:

  • Google account (Personal): 100 mail/day,  250 attachments/mail, 25 MB/mail

  • G Suite for Education: 1500 mail/day,  250 attachments/mail, 25 MB/mail

  • Ref: https://developers.google.com/apps-script/guides/services/quotas 

• There will be random delay for sending emails, and the emails may not arrive in order.