Automatically populate an email template with data from Google Sheets. The emails are sent from your Gmail account so that you can respond to recipient replies.

How it works: You create a Gmail draft template with placeholders that correspond to data in a Google Sheets spreadsheet. Each column header in a sheet represents a placeholder tag. The script sends the information for each placeholder from the spreadsheet to the location of the corresponding placeholder tag in your email draft

Step 1: Set up the spreadsheet

1. Click the button below to make a copy of the Gmail/Sheets Mail Merge sample spreadsheet. Make a copy
2. Update the Recipients column with email addresses you want to use in the mail merge.
3. (Optional) Add, edit, or remove columns to customize the data you want to include in your email template.

If you change the name of the Recipient or Email Sent columns, you need to update the corresponding code in the Apps Script project. You can open the Apps Script project from the spreadsheet by clicking Extensions > Apps Script.

Step 2: Create an email template

1. In your Gmail account, create an email draft. To include data from the spreadsheet in your email, use placeholders that correspond to column names surrounded by curly braces, such as {{First name}}.
   • If you format the text in the email, ensure you also format the placeholder brackets.
   • Placeholders are case-sensitive and must exactly match the column headers.
2. Copy the subject line of your email draft.

Step 3: Send emails

1. In the spreadsheet, click Mail Merge > Send Emails. You might need to refresh the page for this custom menu to appear.
2. When prompted, authorize the script.
3. Click Mail Merge > Send Emails again.
4. Paste the email template's subject line and click OK.

If you apply a filter to the sheet, the script still emails the filtered participants, but it won't add the timestamp.

Note:  Keep an eye on the no of emails you send out per day.  It varies on what type of gmail account you use (Free / Paid)

Source: Create a mail merge with Gmail & Google Sheets  |  Apps Script  |  Google Developers