Description
Send Mail Merge Emails using Gmail & Google Sheets.
Documentation link used in the video https://developers.google.com/apps-script/samples/automations/mail-merge
#googlesheets #gmail
**Key Topics:**
1. **Mail Merge Concept:** Sending personalized emails in bulk using data from Google Sheets and a template created in Gmail.
2. **Data Preparation:** Getting the necessary contact information and personalization data into a Google Sheet.
3. **Gmail Template Creation:** Setting up a draft email in Gmail to serve as the base template.
4. **Placeholders:** Using double curly braces `{{Column Header}}` in the Gmail draft to mark where data from the Google Sheet should be inserted.
5. **Apps Script Implementation:** Using Google Apps Script (code provided by Google documentation) to automate the mail merge process.
6. **Script Configuration:** Modifying the script to match the specific column names used in the Google Sheet (recipient email, status tracking).
7. **Running the Script:** Executing the mail merge via a custom menu added to the Google Sheet.
8. **Authorization:** Granting the necessary permissions for the script to access Sheets and send emails via Gmail.
9. **Verification:** Checking the sent emails and the status column in the Sheet.
10. **Dynamic Subject Lines:** Modifying the script and Sheet to create unique subject lines for each email based on data in the Sheet.
**Step-by-Step Process Shown:**
1. **Get Data:** Generate or input data (First Name, Last Name, Email, Amount, Category) into a Google Sheet. (Used ChatGPT for sample data).
2. **Format Data (Optional):** Format columns like 'Amount' to currency.
3. **Rename Sheet (Optional):** Change the sheet tab name (e.g., to "Email Data").
4. **Create Gmail Draft Template:**
* Go to Gmail (same account).
* Compose a new email.
* Set a specific, unique **Subject Line** for the *draft* (e.g., "Email Template") - this is used to identify the template later.
* Write the email body, including any static text and images.
* Replace specific parts of the body with placeholders matching Sheet column headers (e.g., `Dear {{First Name}} {{Last Name}}, Here is your recent balance: {{Amount}}`).
* Close the compose window, saving it as a draft.
5. **Add & Configure Apps Script:**
* In Google Sheets, go to Extensions - Apps Script.
* Name the script project.
* Copy the code provided in the Google documentation link.
* Paste the code into the script editor, replacing the default content.
* Modify the `RECIPIENT_COL` variable in the script to match the header of the email address column in your sheet (e.g., change from `"Recipient"` to `"Email"`).
* Create a new column in the Sheet (e.g., "Email Sent").
* Ensure the `EMAIL_SENT_COL` variable in the script matches this new column header.
* *(Optional - for dynamic subjects later):* Add a `SUBJECT_COL` variable pointing to the header of your subject column (e.g., `"Subject"`).
* Save the script.
6. **Run Mail Merge:**
* Refresh the Google Sheet; a "Mail Merge" menu should appear.
* Ensure the "Email Sent" (or your status) column is empty for rows you want to send.
* *(For testing):* Change recipient emails to your own email address.
* Click Mail Merge - Send Emails.
* Authorize the script (first time only), handling the "unsafe app" warning if necessary.
* When prompted, enter the **exact subject line** of the Gmail *draft* template created earlier (e.g., "Email Template").
* Click OK.
7. **Verify Results:**
* Check the "Email Sent" column in the Sheet – it should be populated with timestamps.
* Check the Gmail inbox for received emails.
* Confirm placeholders are replaced correctly and subject lines are as expected.
8. **(Optional) Implement Dynamic Subjects:**
* Add a "Subject" column to the Sheet.
* Use formulas to create dynamic subject lines in that column (e.g., `"Your Balance is: " & DOLLAR(D2)`).
* Modify the script to point `SUBJECT_COL` to this new header.
* Modify the `GmailApp.sendEmail` line to use `row[SUBJECT_COL]` for the subject parameter instead of `msgObj.subject`.
* Save the script, clear the status column, and re-run, verifying the dynamic subjects in the received emails.