Send Mail Merge Emails using Gmail & Google Sheets

Video 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.