Mail Merge Send Mass Email In Python

Sharing is caring!

Last Updated on February 17, 2022 by Jay

In this tutorial, we’ll learn how to use the Mail Merge, aka send mass email using Python.

Mail Merge is basically a process of automatically creating mails and sending them. In the digital era, of course, we are talking about Emails as opposed to paper mails. A lot of companies nowadays use this technique to create customized emails for their customers. For example, instead of a generalized email like “Dear Sir…”, we’ll be able to write and send customized emails such as “Dear Jay…”.

Required Libraries

We’ll need three libraries for this tutorial:

• pandas – load receipients list
• pywin32 – send emails in Outlook App
• smtp – send emails using web email (i.e. Gmail)
• EmailMessage – an easy way to create an email message

How Does Mail Merge In Python Work

At its core, mail merge can be broken down into two parts:

1. Write many mails, then
2. Send them to many receipients

This tutorial covers creating emails in Outlook App and webmail. If you need a guide on how to create a mail in a word processing software, e.g., MS Word, check out this tutorial here:

Write Mass Email

I’m going to create this Python mail merge program for a (imaginary) utility company that has thousands of customers. Each month, they need to send electricity bills to their customers by email, which looks like the below:

This is an email template, and note the green highlighted fields will vary for each customer. The customers’ information is stored inside a database (or Excel) like the following. We’ll create a customized email message for each customer using their relevant information.

Let’s start composing customized emails using the client’s information above. We’ll read the data into Python using pandas.

import pandas as pd

df = pd.read_excel('database.xlsx')
df['pay_by'] = df['pay_by'].dt.strftime('%B %d, %Y')

In our example, pandas will detect the pay_by column contains date data, and will automatically convert the column into a datetime-like data. This is okay if we want to process time-series data. However, since we’ll be using these dates in a text environment (email), it’s better to convert them into a string type.

The .dt accessor is super handy and it basically extends pandas functionalities so we can use the datetime functions on a pandas column that contains datetime-like data. The .strftime(‘%B %d, %Y’) function will convert a datetime data to a string value that looks like this “March 15, 2022”.

Once we have the data inside pandas, customizing email is a breeze. We can loop through each row of the dataframe by df.intertuples(). Each row is returned as a namedtuple, so we can use either the index (0,1,2…) or the name of the column to get values of that row.

(I know, I know, looping is bad in pandas. But we’re trying to generate emails vs some intensive computation. Speed is not a concern in this case, so please bear with me 🙂 )

The f”””….””” in front of the string is what’s known as f-string, basically allowing us to pass variables into the string with curly brackets {}.

for r in df.itertuples():
    name = r[1]
    bill_period = r[2]
    usage = r[3]
    total = r.total
    pay_by = r.pay_by
    email = r.email

    msg = f"""
Dear {name},
Please find attached your {bill_period} utility bill.
        
Your electricity usage for the month was {usage} kWh, for a total of ${round(total,2)}. Please make a payment by {pay_by}.
        
Best,
Your PIO Utility Company

"""
    print(msg)

With that, our email generating engine is complete. Now it’s time to send mass emails!

Send Mass Email Using Outlook

Outlook App is probably the preferred choice if you work for most companies. I also like this approach because it only requires one-time login (to Outlook/Office), then you don’t need to worry about authentication and you can even send emails without Outlook open!

We’ll need to use the pywin32/win32com library to communicate with Outlook Application. A couple of notes on the code below:

• The send_outlook function takes 3 arguments, the message generated from the email engine, billing period, and the client’s email address
• win32com.client.Dispatch() creates a MS office app instance, in this case, the Outlook
• CreateItem(0) makes a mail object
• We can also add attachments here, e.g., the actual invoice, using the mail.Attachments.Add(). It’s trivial so we’ll skip that part

import win32com.client

def send_outlook(msg, bill_period, to_address):
    outlook = win32com.client.Dispatch("Outlook.Application")
    mail = outlook.CreateItem(0)
    mail.To = to_address
    mail.Subject = f'Your {bill_period} Electricity Bill'
    mail.Body = msg
    #mail.Attachments.Add(attachment)
    mail.Send()

Send Mass Email Using A Webmail (Gmail, Yahoo, etc)

Using a web email is a little complicated, but not everybody has access to MS Outlook, so let’s also cover the web email here. I’m using Gmail in this example, but it works the same for other web email services. The only difference is the SMTP server name and the port number. You’ll have to find out those values from your web email provider.

With this method, we are basically interacting with the web email’s API, so we need to provide authentication/login before we can use the email services.

For Gmail, we need to first turn on the “less secure app access” for our Google account. Head to this URL and make sure it’s the account you want to use. https://myaccount.google.com/lesssecureapps

We use the EmailMessage library to create an email message, with Subject, From, To, etc. Note this only creates the email message, and will not send it yet.

To send the email, we need to use the SMTP (Simple Mail Transfer Protocol) library. There are only two steps:

1. Log in to our email (Gmail) account
2. Send the email message we just created using EmailMessage

from email.message import EmailMessage
import smtplib

from_address = 'amznbotnotification@gmail.com'
password = 'this is your from_address email password'
smtp_server = 'smtp.gmail.com'
smtp_port = 465

def send_webemail(email_body, bill_period, to_address):
    msg = EmailMessage()
    msg['Subject'] = f'Your {bill_period} Electricity Bill'
    msg['From'] = from_address 
    msg['To'] = to_address
    msg.set_content(email_body)
    #msg.add_attachment()

    with smtplib.SMTP_SSL(smtp_server , smtp_port ) as smtp:
        smtp.login(from_address , password)
        smtp.send_message(msg)

Conclusion

Just a word of caution before we end this tutorial. This mail merge program in Python will send out emails as soon as you run it. It’s better to test your email message/template and make sure it looks fine and appropriate before sending them to your customers. You can also implement a “safe-guard” to ask you to confirm that you really intend to send the emails to prevent you from accidentally sending anything.