Sends a personalised email to each row of a Google Sheet, records every send, and never emails the same person twice.
Uses
Gmail
Google Sheets
Mail merge a personalised email to every row of a spreadsheet
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 createLogTabErrorTry
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 yesYes
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 loggedAddressErrorTry
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 maxEmailsThisRunYes
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 recipientErrorTry
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 recipientYes
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 firstNameErrorTry
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 companyErrorTry
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 emptyYes
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 emptyYes
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 sendErrorTry
GmailSend an email to recipient.Send an email to recipient.
Subject: mergedSubjectmergedBodyOn 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 noYes
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 logWriteErrorTry
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.