Mail Merge with Python

Aleksandr Varnin

5 years ago

If you have your app built with Python and are working on email functionality, then you are in luck. Python’s built-in module for sending emails is easy to use, provides extensive lists of features, and streamlines mass email output. Furthermore, you have several options for adding dynamic content including native functionality (loops) and external modules. Today we will explore how to send personalized emails with mail merge in Python.

A couple of words about mail merge

Mail merge is a mechanism for integrating information from a database to an email template with placeholders, in order to add personalization to the multiple emails. We have explained mail merge in detail in this blog post. To put it simply, if you need to target a list of users by email and address them by name, you can do it with a simple table and script.

Let’s see how to make this with Python and jump into coding! (Refer to our tutorial to learn how to send emails in Python in detail.)

Multiple personalized emails with loops

The simplest way to implement mail merge in Python to send multiple emails is its native functionality and script with loops. To do this, you only need to create a database of contacts (the easiest is by .csv file) and save it to the folder with your Python script. Let’s say you need to send a test score to a group of people, just put their data into the .csv database, as follows:

#name,email,score
Alex,alex@example.com,79
Ann,ann@example.com,98

Use the code below to open your database file and loop over rows. Put the {name} and {score} placeholders into the content of your message. They will be replaced with the values from the appropriate columns.

We always recommend running email experiments in a pre-production environment in order to avoid sending test messages to real recipients by mistake. Mailtrap is the best solution for this because it traps all your emails in a virtual inbox for further view and exploration. Integrate Mailtrap as a usual SMTP server by pasting your credentials as a login and password.

import csv, smtplib
port = 2525
smtp_server = "smtp.mailtrap.io"
login = "1a2b3c4d5e6f7g" # paste your login generated by Mailtrap
password = "1a2b3c4d5e6f7g" # paste your password generated by Mailtrap
message = """Subject: Your test score
To: {recipient}
From: {sender}
Hi {name}, thanks for accepting our challenge! Your current score is {score}. Contact us if you have further questions.
Cheers! """
sender = "test-center@example.com"
with smtplib.SMTP(smtp_server, port) as server:
    server.login(login, password)
    with open("students.csv") as file:
        reader = csv.reader(file)
        next(reader)  # add this to skip the header row
        for name, email, score in reader:
            server.sendmail(
               sender,
                email,
                message.format(name=name, recipient=email, sender=sender, score=score)
            )
            print(f'Sent to {name}')

Your response should look like the following:

Sent to Ann
Sent to Alex

In the Mailtrap inbox, you will see separate messages for each recipient. Check if placeholders were replaced correctly:

Test Your Emails Now

Mail merge program in Python

Another option is to use the command line mailmerge tool available on both PyPi and GitHub.

You should also store your database in a .csv file. However, the message content can be created with jinja2 template engine. It gives you more flexibility and options for crafting complex templates. In addition, this tool supports attachments, HTML, and Markdown.

Let’s see how it works. We took the examples provided on the Mailmerge GitHub page and implemented them in our environment using Mailtrap. This makes email experiments much simpler since you don’t need to send separate messages to yourself, as provided in the original code samples.

We will jump to the example with attachments right away, and we will add a yes/no condition. This will help to work with the testing score from our previous example in a more detailed and personalized way.

$ pip install mailmerge # install mailmerge first, we did it with pip
$ mailmerge
$ mailmerge --sample # run mailmerge --sample, this one will create template files
Creating sample template email mailmerge_template.txt
Creating sample database mailmerge_database.csv
Creating sample config file mailmerge_server.conf

We get three files:

Mailmerge_template.txt – message content with placeholders along with the recipient, sender, and subject
Mailmerge_database.csv – recipients details to replace placeholders
Mailmerge_server.conf – server configuration.
To set Mailtrap as an SMTP server, use the following configuration:

[smtp_server]
host = smtp.mailtrap.io
port = 2525
username = YOUR_MAILTRAP_USERNAME
security = STARTTLS

Note that we haven’t put the password here. It is requested each time you run Mailmerge.

Edit these files by adding your data and then run Mailmerge again.

Note: Placeholders in the Mailmerge templates go in double curly braces {{ placeholder }}.

Now, let’s compose and send the message.

In the Mailmerge example, they use the dry-run command to test emails and print the output right away. We won’t need to do this because all our emails will go to Mailtrap where we’ll be able to see how they should be rendered by webmail without the risk of delivery to real inboxes.

$ cat mailmerge_template.txt
TO: {{Email}}
SUBJECT: Your testing score is here
FROM: Testing House <test-score@example.com>
ATTACHMENT: attachments/{{File}} {# here, the file is located in the “attachments” folder, which must be placed in the same directory as your current template file #}
Hi {{Name}},
thanks for accepting our challenge! Your current score is {{Score}}.
{% if Answer == "Yes" -%}
We are happy to confirm that you are ready to move to the next level!
{%- endif %}
If you wish to improve your score, you take the test once again,
Contact us if you have further questions.
Cheers!
$ cat mailmerge_database.csv
Name,Email,Score,Answer,File
Alex,alex@example.com,79,No,alex.pdf
Ann,ann@example.com,98,Yes,anna.pdf
$ mailmerge --no-dry-run --no-limit # send messages to all addresses on the list

Check the output in your Mailtrap inbox:

Docx Mail Merge

Docx-mailmerge is another interesting option for creating templates that can be emailed from your Python app. As the name implies, it merges data from Office Open XML (docx) files. At the same time, it doesn’t require MS Word and is compatible with any system.

Note: This package requires lxml.

However, Docx Mail Merge and Mailmerge packages appear to be in conflict. They are installed under the same names, which might make it impossible to use these packages in parallel. Keep this in mind if you experience any troubles with installation.

$ pip install docx-mailmerge # install with pip

Before we move further with coding, we need to prepare a doc with merge fields. You can do this with any editor that supports .docx format (MS Word, OpenOffice, WPS Office, etc.). The following steps are similar in many document editors: Insert -> Quick Parts -> Fields, select MergeField and enter the field names. We will continue with our test score example, so our fields will be “Name”, and “Score”. In WPS Office it looks like this:

With this method, create the following template:

Now, we can merge values with the following code and create a set of separate documents with automatically populated fields:

# import necessary packages
from mailmerge import MailMerge
# specify the input template
template = "Test-score-template.docx"
with open("students.csv") as file:
	reader = csv.reader(file)
	next(reader)  # add this to skip the header row
	for name, email, score in reader:
      	document = MailMerge(template)
    		document.merge(
      	Name=name,
      	Score=score)
    	document.write(f'test-score-{name}.docx')

We have demonstrated a simple example of how to use this tool to show you the concept and potential options. With docx-mailmerge, you can build complex templates with a long list of variables, populate tables, and create documents with multiple pages.

Final words

Mail merge in Python is a simple but powerful method of sending personalized emails and creating templates. We have examined several simple working examples to help you master Python’s functionality. Never stop experimenting and always thoroughly test your emails before actually sending them!