In this article we will show how to send mass email from google sheets in just a few simple steps using apps script and with Lido
Start by populating Sheet 1 with details like the recipient's name, email, and any other relevant data. Keep each piece of data in its own column.
Lido, an advanced spreadsheet software designed for automation, works seamlessly with Google Sheets. It lets you automate without delving into Google Apps Script or other coding methods. Sign up for free at Lido's Signup Page.
In a new worksheet, draft your email subject and body. Use this format [@Column] to reference your columns.
For instance:
Subject: Thank you, [@Name]
Body: Hello [@Name], we've received your inquiry regarding [@Service].
Lido offers a formula, =STRINGTEMPLATE(), which substitutes column references (e.g., [@Name]) with the row values from a table.
For creating dynamic subjects and bodies:
This will dynamically populate the subject and body columns with your content.
To send an email via Lido, the formula is =SENDGMAIL(sender, recipient, subject, body).
Example:
SENDGMAIL("youremail@gmail.com", B2, D2, E2)
Input this formula next to your body column to generate another Computed Column.
For a test run, right-click on a SENDGMAIL formula cell and opt for 'Run action'. The first time, you’ll need to sign in to your Gmail.
Click the 3-dot menu in your SENDGMAIL column and choose 'Run column now' for dispatching all emails simultaneously.
You have now mass emailed all of the names and emails in your google sheet!
You can also send bulk email using AppsScript but this is more complex and less user friendly than the method above. If you are more tech savvy, here are the steps:
The script we will use to send mass email requires two sheets / tabs to work correctly: one for the email list and the other for the email subject and body:
Rename the first tab emails: add the following headers for each column: Name for Column A; Email Address
for Column B; Phone number for Column C; and Sent? for Column D.
Create a second tab called subjectbody. In Column A we will add the headers Email Contents in cell A1, Subject in cell A2 and Body in cell A3.
We will set B2 as the cell containing the email subject and C2 as the cell containing the email body. We also add the headers in Column A: Subject
We can add dummy data first to see whether the script works later on.
Email list:
Subject and body:
We are now ready to add the script. Click Extensions then select Apps Script.
A new tab containing the Apps Script workspace will be loaded, with placeholder code placed in the code area.
We will clear the code area by selecting the placeholder code and deleting it. You can do this using keyboard shortcuts: Ctrl+A then Del for Windows and Cmd+A then Delete for Mac OS.
Go to the following page:
https://github.com/rvbautista/google-sheets-send-emails/blob/main/Code.gs
Copy the entire code by clicking the Copy raw file icon in the upper-right corner of the code.
After copying, paste it in the code area of Apps Script.
Click Save project.
You can now close the Apps Script tab.
To run the script on demand, we need to insert a button. To start, click Insert then select Drawing. Alternatively, if you already have designed a button and saved it in an image file, click Image then follow the instructions on uploading it to Google Sheets.
In the Drawing box, start drawing a button that you want to use. Once you are done, click Save and Close.
The button will be inserted to the spreadsheet. Hover your cursor over the button. A set of three dots will appear on the upper-right corner of the button. Click it, then select Assign script among the drop-down list that will appear.
A small box will appear with a textbox. Type emailMerge to the textbox, then click OK.
Since it is the first time you will run the script, Google will ask for your authorization to run it. Click Continue.
A new window will appear, asking you to select the Google account associated with the spreadsheet containing the button.
After selecting the account, a warning will appear reminding you that Google has not verified the app. Click the small link on the lower-left corner labeled Advanced. Another reminder will appear to continue only if you understand the risks and trust the developer. Click Go to Untitled project (unsafe) or whatever the project name is in Google Apps Script.
A new page will appear listing the permissions needed by the script. Click Allow.
The window will close, and the script will run. You have now successfully sent emails to your email list!
You will see that the timestamp when the email was sent will appear in Column D.
What if you want to send an email to the same recipient? Simple. Delete the timestamp. The script is designed to not send emails to recipients with existing time stamps.
We hope that you now have a better understanding of how to send mass emails from google sheets!
If you enjoyed this article, check out our article on how to set up email notifications in google sheets or how to set up email reminders in google sheets.
