Automate Sending Emails from a Google Sheet using Python

Automating sending emails from a Google Sheet filled with names, emails, messages etc. is a common task for any person or business.

Image for post
Image for post
Send Email

This tutorial is opinionated, i.e. it outlines one specific way we found best to automate sending emails. We also use our own product API Spreadsheets to get the data from your Google Sheet.

There are many ways to go about this all and you should modify the code to fit your needs.

Pre-requisite

If you do not know how to use the Terminal and Python, then go through this tutorial first: Intro to Reading and Writing Spreadsheets with Python

Google Sheet

We are going to use a sample Google Sheet for this tutorial. You can view it here: Email Sample

Image for post
Image for post
Values in the file

The header names matter here: Name, Email, Subject, Message. We will be using these names to read the values later.

The file has dummy email addresses so these emails will get returned to you. If you update these values with real emails then the emails should send.

If they still don’t, you can let us know in the comments with the error you are facing and we will try to help.

Full Code

You can follow along with the entire code

The tutorial will be broken down by each chunk of code

Create the Python File

Open a text editor and create a file email_send.py.

Note: make sure to NOT name the file email.py because it will throw an error. (error happens because email takes the same namespace as a class when we import requests later)

Import Libraries

Lines 1–2

Import both requests and smtplib to read the Google Sheet and send emails respectively.

smptlib comes pre installed so you don’t need to do anything

requests might not be installed on your system. To install it, go to your terminal and enter the following command

$ pip install requests

Specify Your Credentials

Lines 7–9

These are fake obviously :) Change these to your email credentials

your_name = "Bill Butlicker"
your_email = "william.m.butlicker@gmail.com"
your_password = "bearsbeetsbattlestar"

Set Up the Email Server to Send the Email

Lines 13–15

We are going to set up the smtp server for Gmail. There are two crucial things to watch out for here:

  1. If you are sending the emails from any other email besides gmail, then you should change the ‘smtp.gmail.com’ in line 13 to the email provider you are using. Here is a list of some smtp values based on your email provider
  2. Depending on your email provider security setting, the login in line 15 might error out this script. For example, if you are using gmail you must allow your gmail account access to a less secure app. Here is how to disable that option
server = smtplib.SMTP_SSL('smtp.gmail.com', 465)
server.ehlo()
server.login(your_email, your_password)

Read the Google Sheet

Lines 19–28

We are going to be reading the Google Sheet using API Spreadsheets and storing the result in a variable data

r = requests.get("https://api.apispreadsheets.com/data/3727/")if r.status_code == 200:
# SUCCESS
data = r.json()["data"]
print("SUCCESS")
else:
# ERROR
data=None
print("ERROR")

To get this we first go to APISpreadsheets.com, create a free account and connect your Google Sheet.

You can also check out this guide to get started on API Spreadsheets.

After you connect your Google Sheet, you will see information about your file. Click on the Read tab.

Image for post
Image for post

Then under Code Samples, click the Python tab and this is the code you will place at line 18.

Image for post
Image for post

Loop through the Values of Each Google Sheet Row

Lines 31–37

We will be using Python syntax go through the index values 0, 1, 2…by looping from 0 till the number of our rows (data). Python index starts at 0 and corresponds to the row number in our Google Sheet (not counting the header).

We will use the idx to get the row of the data and then use the header names from the Google Sheet to get all the necessary values.

If you have other columns, you can access their value in this part of the code using the syntax shown here.

for idx in range(len(data)):    # Get each records name, email, subject and message
name = data[idx]["Name"].strip()
email = data[idx]["Email"].strip()
subject = data[idx]["Subject"].strip()
message = data[idx]["Message"].strip()

the .strip() function is a preventative function that removes any white or extra spaces from the front and back of your value

Create the Email to Send

Lines 39–44

This is the meat of our code where everything comes together. We will create the email that we want to send. The {} notation specifies that we want there to be a placeholder. The value inside the bracket specifies which variable should be filled in depending on its location inside the format method.

For example the {0} will be filled in by our your_name variable that we created in line 7. The {1} will be filled in by our your_email variable etc.

full_email = ("From: {0} <{1}>\n"
"To: {2} <{3}>\n"
"Subject: {4}\n\n"
"{5}"
.format(your_name, your_email, name, email, subject, message))

Send the Email

Lines 48–52

We are going to use a try…catch block here where we are going to try sending the email on line 48. If that succeeds, line 51 will print saying your email was successfully sent.

Note: The syntax for sending the email accepts the second parameter in a list. So even though in this case, we are sending the email to one person, we still have to pass it in a list like [email]

try:
server.sendmail(your_email, [email], full_email)
print('Email to {} successfully sent!\n\n'.format(email))

However, if there was a problem sending the email line 48 will print saying the email wasn’t sent because of the error received.

print('Email to {} could not be sent :( because {}\n\n'.format(email, str(e)))

Close the Server

Line 55

CONGRATS!! You have just completed a very hard to do task. We finally close the server!

server.close()

Next Steps

There are couple of more things you can do if you want:

  1. Refactor and automate this script further by making functions for each chunk
  2. Read up on sending HTML emails and attachments
  3. Check out our tutorial if you want to do this with an Excel file instead

Products and resources that magically simplify hard spreadsheet tasks. Check us out at www.lovespreadsheets.com

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store