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. 

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.

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.