Automate Sending Emails with Python using a Spreadsheet

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

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

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

File

We are going to be use a sample file for this tutorial. You can download it by clicking on the file name: EmailList.xlsx

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

You can follow along with the entire code

Full Code for Automating email sending using Python

The tutorial will be broken down by each chunk of code

Create the File

Open a text editor and create a file email_send.py. Save this in the same folder as the sample excel file.

Import Libraries

Lines 1–2

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

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 File

Line 18

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

Lines 21–24

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

Lines 27–33

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

Lines 36–40

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 44–48

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

Line 51

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

If you want a custom app, data cleaned, or a project built using spreadsheets, you can check out our consulting service here: https://www.lovespreadsheets.com!