Buzz
If you need to analyze data from emails in Excel, the process is similar to extracting data from emails to Google Sheets.
Suppose you want to send personalized emails to a list of recipients stored in a CSV file. The usual solution for this is to use email marketing services like Mailchimp and import the spreadsheet containing recipients.
However, there's a simpler way, and you don't have to pay any fees at all to send emails directly from Google Spreadsheet. Here's how to send emails directly from Google Spreadsheet.
Sending Emails from Google Spreadsheets
You're probably familiar with the name Google Sheets by now. Google Sheets, a web-based spreadsheet platform, allows you to set up and calculate data in spreadsheet format, but it's easier to use than traditional Microsoft Excel.
If you delve deeper, you'll discover that Google Sheets has many other useful features integrated. For example, you can install additional add-ons similar to installing add-ons for Google Docs, allowing you to remove duplicates, separate first and last names, or merge cells.
Utilizing Yet Another Mail Merge Add-on
Using Yet Another Mail Merge is quite straightforward. Simply compose an email in Gmail and create corresponding data columns you want to send. Typically, Google Sheets includes email addresses, names, and if desired, you can add other fields (such as city, company name, etc.). Just add this information to Google Sheet and arrange it in the email you compose in Gmail.
Sending Emails through Google Sheets Script
Google Sheets script tool allows you to perform tasks and automate actions you do on Google Sheets. One of the tasks you can accomplish is sending emails. And that's all you need.
To begin, open the Google Sheets script editor. To do this, access Tools at the top corner of the window, then click Script Editor… as shown below.
You'll notice next to the Script Editor… option is Script Gallery, where the Yet Another Mail Merge add-on is stored.
The actions are divided into the following steps:
1. Read the email address in the first column.
Read the recipient's name in the second column.
Merge the name section.
Send personalized emails to each recipient.
Script for sending emails from Google Spreadsheet
As mentioned above, to send emails directly from Google Spreadsheet, the first step is to retrieve the recipient's email address from the first column. The next step is to retrieve the value of the second column and use it as the name in the email body.
Here are the data you see:
Below are the functions. If you carefully read the comments inside the source code, everything will become more understandable even if you don't have much coding experience:
function sendEmails() {
Extract the current sheet:
Specify the starting row:
Define the number of rows to process:
// Select the range of cells A2:B4
// Define column structure: Column A = Email Address, Column B = First Name
Extract data range based on specified rows and columns:
// Retrieve values for each row within the range.
Fetch data from the defined range:
Iterate through the data array:
Assign the current row to a variable:
Extract the email address from the first column of the selected data:
Construct the message body using the recipient's name:
Define the email subject:
Send the email using MailApp.sendEmail:
Close the for loop:
Close the sendEmails function:
Check and Execute Script
After moving the code into Google Script Editor and adjusting to your liking, here's what you'll see:
You'll notice a small Play button next to the bug icon. Click this button to execute the script. However, before pressing the button, you should consider testing on a small dataset.
Note: When you click the Play button for the first time, Google will prompt you to allow sending emails. This is normal as the script is trying to access a protected function (MailApp.sendEmail).
Here's how to send emails directly from Google Spreadsheet. This method helps you save a lot of time and money, as you don't need to spend on third-party software.
