Mail merge a personalised email to every row of a spreadsheet

Give it a spreadsheet of recipients and a message written once, with merge fields where each row's first name and company should go. Each time you run it, it sends one personalised email per row from your own Gmail account.

Nothing in your recipient tab is ever changed. A separate 'Sent log' tab is added alongside it and one line is appended there for every email sent. Every address is checked against that log before sending, so running it again skips everyone already written to and catches an address that appears twice. A row with no usable email, or a blank cell where the message needs a name or company, is reported and skipped rather than sent a half-finished greeting. If an email goes out but the log cannot be written, the run stops at once rather than carrying on blind.

Your sheet needs a header row with an 'Email' column, plus 'First Name' and 'Company' if your message uses them. Each run sends only as many emails as you allow, so your first run can be a single test.

Set sheetUrl to blank.Set sheetUrl to blank.

Set sheetTabName to blank.Set sheetTabName to blank.

Set emailSubject to blank.Set emailSubject to blank.

Set emailBody to blank.Set emailBody to blank.

Set sentLogTabName to Sent log.Set sentLogTabName to Sent log.

Set maxEmailsThisRun to 1.Set maxEmailsThisRun to 1.

Set usesFirstName to emailSubject contains {{First Name}} or emailBody contains {{First Name}}.Set usesFirstName to emailSubject contains {{First Name}} or emailBody contains {{First Name}}.

Set usesCompany to emailSubject contains {{Company}} or emailBody contains {{Company}}.Set usesCompany to emailSubject contains {{Company}} or emailBody contains {{Company}}.

Set sentLogIsNew to true.Set sentLogIsNew to true.

If a step below fails, save the error as createLogTabErrorIf a step below fails, save the error as createLogTabError

Try

Google SheetsCreate the tab sentLogTabName in sheetUrl.Create the tab sentLogTabName in sheetUrl.

On error

Change sentLogIsNew to false.Change sentLogIsNew to false.

If sentLogIsNew is yesIf sentLogIsNew is yes

Yes

Google SheetsAdd a row to sentLogTabName in sheetUrl.Add a row to sentLogTabName in sheetUrl.

No

No steps

Google SheetsRead column 'A' of sentLogTabName in sheetUrl. Save it as sentLog.Read column 'A' of sentLogTabName in sheetUrl. Save it as sentLog.

Set alreadySent to an empty list.Set alreadySent to an empty list.

For each loggedAddress in sentLog.values:For each loggedAddress in sentLog.values:

If a step below fails, save the error as loggedAddressErrorIf a step below fails, save the error as loggedAddressError

Try

Add loggedAddress in lowercase to the end of alreadySent and save it as alreadySent.Add loggedAddress in lowercase to the end of alreadySent and save it as alreadySent.

On error

Show in the output: Ignored a cell in the '@sentLogTabName' tab that is not text.Show in the output: Ignored a cell in the '@sentLogTabName' tab that is not text.

Set sentThisRun to 0.Set sentThisRun to 0.

Set skippedRows to 0.Set skippedRows to 0.

Set alreadySentRows to 0.Set alreadySentRows to 0.

Google SheetsRead the rows of sheetUrl. Save it as recipientSheet.Read the rows of sheetUrl. Save it as recipientSheet.

For each row in recipientSheet.rows:For each row in recipientSheet.rows:

If sentThisRun is greater than or equal to maxEmailsThisRunIf sentThisRun is greater than or equal to maxEmailsThisRun

Yes

Stop repeating.Stop repeating.

No

No steps

Set rowLabel to row row.row_number as text.Set rowLabel to row row.row_number as text.

Set recipient to blank.Set recipient to blank.

If a step below fails, save the error as recipientErrorIf a step below fails, save the error as recipientError

Try

Make row.Email lowercase and save it as recipient.Make row.Email lowercase and save it as recipient.

On error

Change recipient to blank.Change recipient to blank.

If recipient is empty or recipient doesn't contain @If recipient is empty or recipient doesn't contain @

Yes

Show in the output: Skipped rowLabel: the Email column is empty or does not hold an email address.Show in the output: Skipped rowLabel: the Email column is empty or does not hold an email address.

Add 1 to skippedRows.Add 1 to skippedRows.

Skip to the next item.Skip to the next item.

No

No steps

If alreadySent contains recipientIf alreadySent contains recipient

Yes

Show in the output: Skipped rowLabel: recipient is already listed in the '@sentLogTabName' tab.Show in the output: Skipped rowLabel: recipient is already listed in the '@sentLogTabName' tab.

Add 1 to alreadySentRows.Add 1 to alreadySentRows.

Skip to the next item.Skip to the next item.

No

No steps

Set firstName to blank.Set firstName to blank.

If a step below fails, save the error as firstNameErrorIf a step below fails, save the error as firstNameError

Try

Trim row["First Name"] and save it as firstName.Trim row["First Name"] and save it as firstName.

On error

Change firstName to blank.Change firstName to blank.

Set company to blank.Set company to blank.

If a step below fails, save the error as companyErrorIf a step below fails, save the error as companyError

Try

Trim row.Company and save it as company.Trim row.Company and save it as company.

On error

Change company to blank.Change company to blank.

If usesFirstName is yes and firstName is emptyIf usesFirstName is yes and firstName is empty

Yes

Show in the output: Skipped rowLabel: your message uses {{First Name}} but that cell is empty.Show in the output: Skipped rowLabel: your message uses {{First Name}} but that cell is empty.

Add 1 to skippedRows.Add 1 to skippedRows.

Skip to the next item.Skip to the next item.

No

No steps

If usesCompany is yes and company is emptyIf usesCompany is yes and company is empty

Yes

Show in the output: Skipped rowLabel: your message uses {{Company}} but that cell is empty.Show in the output: Skipped rowLabel: your message uses {{Company}} but that cell is empty.

Add 1 to skippedRows.Add 1 to skippedRows.

Skip to the next item.Skip to the next item.

No

No steps

Replace {{Email}} with recipient in emailSubject with {{First Name}} replaced by firstName with {{Company}} replaced by company and save it as mergedSubject.Replace {{Email}} with recipient in emailSubject with {{First Name}} replaced by firstName with {{Company}} replaced by company and save it as mergedSubject.

Replace {{Email}} with recipient in emailBody with {{First Name}} replaced by firstName with {{Company}} replaced by company and save it as mergedBody.Replace {{Email}} with recipient in emailBody with {{First Name}} replaced by firstName with {{Company}} replaced by company and save it as mergedBody.

Set emailSent to true.Set emailSent to true.

If a step below fails, save the error as sendErrorIf a step below fails, save the error as sendError

Try

GmailSend an email to recipient.Send an email to recipient. Subject: mergedSubjectmergedBody

On error

Change emailSent to false.Change emailSent to false.

Show in the output: Could not send to recipient: sendError as text.Show in the output: Could not send to recipient: sendError as text.

If emailSent is noIf emailSent is no

Yes

Add 1 to skippedRows.Add 1 to skippedRows.

Skip to the next item.Skip to the next item.

No

No steps

If a step below fails, save the error as logWriteErrorIf a step below fails, save the error as logWriteError

Try

Google SheetsAdd a row to sentLogTabName in sheetUrl.Add a row to sentLogTabName in sheetUrl.

On error

Stop and report a problem: Sent the email to recipient but could not add it to the '@sentLogTabName' tab. The run stopped here so that nobody is emailed twice next time - fix the spreadsheet, then run it again.Stop and report a problem: Sent the email to recipient but could not add it to the '@sentLogTabName' tab. The run stopped here so that nobody is emailed twice next time - fix the spreadsheet, then run it again.

Add recipient to the end of alreadySent and save it as alreadySent.Add recipient to the end of alreadySent and save it as alreadySent.

Add 1 to sentThisRun.Add 1 to sentThisRun.

Show in the output: Sent sentThisRun as text email(s) this run. alreadySentRows as text row(s) had already been emailed and skippedRows as text row(s) were skipped. Raise 'maxEmailsThisRun' and run it again to carry on down the list.Show in the output: Sent sentThisRun as text email(s) this run. alreadySentRows as text row(s) had already been emailed and skippedRows as text row(s) were skipped. Raise 'maxEmailsThisRun' and run it again to carry on down the list.