Automate Sending Emails with Python using a Spreadsheet

Love Spreadsheets
Sep 5, 2019 · 4 min read

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

Image for post
Image for post
There really is no need for this picture except to liven things up a bit

This tutorial is opinionated, i.e. it outlines one specific way we found best to automate sending emails. There are many ways to go about it and you should modify the code to fit your needs.

Pre-requisite

File

Image for post
Image for post
Values in the File

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

Full Code for Automating email sending using Python

The tutorial will be broken down by each chunk of code

Create the File

Import Libraries

Import both pandas and smtplib to read spreadsheets and send emails respectively. smptlib comes pre installed so you don’t need to do anything but if you haven’t installed pandas then once again go through this tutorial: Intro to Reading and Writing Spreadsheets with Python

Specify Your Credentials

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

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 File

We are going to be reading the spreadsheet using pandas and storing the result in a data frame email_list

email_list = pd.read_excel("EmailList.xlsx")

Get all the Values from the File

In pandas, the way to read columns is to access them as follows. In this case, we are going to access all four of our columns, Name, Email, Subject, Message and store them in variables. If you have any other columns that you need to get, you can use the same syntax to get those values.

all_names = email_list['Name']
all_emails = email_list['Email']
all_subjects = email_list['Subject']
all_messages = email_list['Message']

Loop through the Values

We will be using Python syntax go through the index values 0, 1, 2…by looping from 0 till the number of emails in our column. This will provide our idx which we will use to get the four values we need to send the email.

for idx in range(len(all_emails)):# Get each records name, email, subject and message
name = all_names[idx]
email = all_emails[idx]
subject = all_subjects[idx]
message = all_messages[idx]

Create the Email to Send

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

We are going to use a try…catch block here where we are going to try sending the email on line 45. If that succeeds, line 46 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

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

server.close()

Next Steps

  1. Refactor and automate this script further by making functions for each chunk
  2. Read up on sending HTML emails and attachments

The Startup

Medium's largest active publication, followed by +721K people. Follow to join our community.

Love Spreadsheets

Written by

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

The Startup

Medium's largest active publication, followed by +721K people. Follow to join our community.

Love Spreadsheets

Written by

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

The Startup

Medium's largest active publication, followed by +721K people. Follow to join our community.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

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