Programming in PythonProject 2: Mail Merge

Suppose you want to send an email to dozens of people, with some elements of the message varying by recipient. For example, you'd like to insert the recipient's first name in the salutation, and you might also need to insert a personal URL or passcode, information on the recipient's status, etc.

This problem is called mail merge, and there are many commercial software solutions available. However, in this section you'll implement a simple and flexible mail merge in Python. You will want to do this on your computer, because the authorization step involves using your operating system keychain.

Continue

yagmail

The first hurdle is to securely authorize your Python program to access your email account. You're a Gmail user, so you search for a Gmail package for Python and find yagmail.

Following the installation instructions on the project GitHub page, you run pip3 install yagmail[all] from the command line to install yagmail.

Continue

Continuing to follow the instructions, you run

import yagmail
yagmail.register('mygmailusername')

and enter the password for the Gmail account in the resulting password prompt. This stores the password in the operating system keychain so you don't have to keep entering it. (Note: if you're using dual authentication on your Google account, you'll need to generate and enter a special app password instead of your regular password; see this info page for instructions. I found that I also needed to be logged into my Google account on my system, which is in System Preferences > Internet Accounts on macOS.)

Continue

Now you can set up an SMTP object for sending messages.

yag = yagmail.SMTP("mygmailusername@gmail.com")

In the documentation, you read that this object has a send method whose parameter list includes to, subject, and contents. you want to call this method once for each recipient, and for that you use a for loopwhile loopif statement.

CSV

Before sending the message, you have to figure out to store the data for each recipient and how to insert that data into the message. One easy solution to the former problem is to store the data in a spreadsheet. You decide to skip the spreadsheet software since the situation is so simple. Instead, you make a file called mail-merge-data.csv, open it in a text editor, and insert the contents

Name,Email,Status
Viorica,virica@example.com,pending
Sidra,sidra_tiwana@example.com,completed
Alfonso,alfonso.serrano@example.com,pending

You save the file and proceed to figuring out how to load it into Python.

Continue

Pandas

You google "enter CSV in Python" and scan the first several search results. The first couple show examples with a dozen or so lines of code, which seems more complicated than necessary. Going back to the search results, you see a function called pandas.read_csv, and you remember that Pandas is the recommended package for handling spreadsheet data in Python. So you do

import pandas as pd
mailData = pd.read_csv("mail-merge-data.csv")

You check type(mailData) and see that mailData is a DataFrame, which is the general Pandas type for tabular data.

Continue

Now you have to figure out how to loop over the rows of a DataFrame. You search the web for "how to loop over rows of pandas dataframe" and discover the method itertuples iteritems items (look it up!).

You do list(mailData.itertuples())[0] to get an example row from the DataFrame, and you call dir on it to look for the right method for extracting each column value. You see that Name, Email, and Status are attributes of the row, so you can access them using dot syntax (like row.Email).

Continue

Finally, you need to insert information from each DataFrame row into the message. Fortunately, you alreday know a great way to do this: f-stringsdictionarieslists!

It will be a bit awkward to type the whole message into the line where you call yag.send, so instead you write a function that takes row as a parameter and returns the message.

def message(row):
    return f"""
    Dear {row.Name},

    Thanks for participating! Your status is {row.Status}.

    Yours,
    Roza
    """

Solution. We supply the Email attribute of row to the to argument, and message(row) to contents:

for row in mailData.itertuples():
    yag.send(to=row.Email,
             subject="Your status",
             contents = message(row))

Continue

Congratulations! You have finished the Data Gymnasia Programming with Python course.