How to Mail Merge with Python

Let’s break mail merge down into its component parts:

• a text document which contains the body of the email, and is populated with data
• a spreadsheet that contains the data to be populated into the email body
• email, specifically, one email sent for each row of the spreadsheet data

Those are all things you can code up on your own with Python efficiently, especially if you lean on a couple of libraries.

Email Body

To craft your messaging, use a triple-quote string so that spacing will be preserved. Each part that will be populated by data can be replaced with curly brackets. You’ll use Python’s string.format() method to fill in the fields from the dataset.

message = """Good morning {},

Thanks so much for your help with {}. If there's anything we can do, let us know.

Thanks,
  Jake

"""

Populate the Data

You can import the csv library to and use it to pull your data from a .csv file.

import CSV
def get_from_csv(file_name):
    with open(file_name, 'r') as f:
        reader = csv.reader(f)
        data = list(reader)
    return data

data = get_from_csv("your_spreadsheet_file_name.csv")

Sending the Email

For this we’ll import a python library called smtplib, which stands for simple mail transfer protocol library. In our setup we’ll use a Gmail account, and SMTP will take the data we give it and send the emails. For this to work, you will need to temporarily adjust your Gmail settings to allow less secure apps access.

For each row in the spreadsheet, you can use a function like this:

def send(user, pwd, recipient, subject, body):
	FROM = user
	TO = recipient if type(recipient) is list else [recipient]
	SUBJECT = subject
	TEXT = body

	message = """From: %s\nTo: %s\nSubject: %s\n\n%s""" % (FROM, ", ".join(TO), SUBJECT, TEXT)
	try:
		server = smtplib.SMTP("smtp.gmail.com", 587)
		server.ehlo()
		server.starttls()
		server.login(user, pwd)
		server.sendmail(FROM, TO, message)
		server.close()
		print('successfully sent the email')
	except Exception as e:
		print('failed to send mail, because of exception:')
		print(e)

Put It All Together

The full code is below. The comments will help you understand what each line is doing. I haven’t modularized anything here, but I recommend modularizing the send function so you can import it into other Python scripts.

from time import sleep
import random
import csv
import smtplib

message = """Good morning {},

Thanks so much for your help with {}. If there's anything we can do, let us know.

Thanks,
  Jake

"""

def get_from_csv(file_name):
    with open(file_name, 'r') as f:
        reader = csv.reader(f)
        data = list(reader)
    return data

data = get_from_csv("your_spreadsheet_file_name.csv")

def send(user, pwd, recipient, subject, body):
	FROM = user
	TO = recipient if type(recipient) is list else [recipient]
	SUBJECT = subject
	TEXT = body

	message = """From: %s\nTo: %s\nSubject: %s\n\n%s""" % (FROM, ", ".join(TO), SUBJECT, TEXT)
	try:
		server = smtplib.SMTP("smtp.gmail.com", 587)
		server.ehlo()
		server.starttls()
		server.login(user, pwd)
		server.sendmail(FROM, TO, message)
		server.close()
		print('successfully sent the email')
	except Exception as e:
		print('failed to send mail, because of exception:')
		print(e)

for row in data:
	if row is data[0]:
		continue
	#this depends on what columns the first name, email, and project info are in. In this case, colums B, C, and D:
	first_name = row[1]
	email_address = row[2]
	project = row[3]
	#now add those 3 inputs to your email body:
	message_with_inputs = message.format(first_name, project)
	print("emailing", email_address)
	#send the email:
	send('your_email_address@gmail.com', 'your_gmail_password', email_address, 'Thanks ' + first_name, message_with_inputs)
	#sleep for variable amounts of time if needed:
	sleep(random.randint(3,6))