Automate University Admission Emails with Python: From Excel to Word and QQ Mail

Preface

Hello, I’m J. After the college entrance exam, universities need to send admission letters to many students. This article shows how to use Python to read exam scores from an Excel file, generate personalized admission letters with a Word template, and automatically send them via QQ email.

Preparation

Place the three files in the same folder:

mail_merge.ipynb – the Python script

123_mail_merge_data_source.xls – the Excel file with student names, emails, and scores

mail_merge_template.docx – the Word template for the admission letter

Step 1: Import Modules

Install the required libraries:

# Install modules
pip install docxtpl
pip install zmail

Then import them in the script:

# Import template library
from docxtpl import DocxTemplate
import pandas as pd
# Import email library
import zmail
import time

Step 2: Mail Merge

Read the Excel data, loop through each student, render the Word template, and save the file using the student’s name:

# Read data source
df = pd.read_excel('123_mail_merge_data_source.xls')
print(df)

# Generate files and send emails
for i in range(len(df)):
    contxt = dict(df.iloc[i])
    filename = contxt['姓名']
    youxiang = contxt['邮箱']
    tpl = DocxTemplate('mail_merge_template.docx')
    tpl.render(contxt)
    tpl.save(filename + '.docx')

Step 3: Configure QQ SMTP

Open QQ Mail → Settings → Account → SMTP Service and enable it. If a second‑generation password is required, follow the verification steps to obtain the authorization code.

Step 4: Send Emails

Compose the email content and attach the generated Word file:

# Set email subject
subject = 'Admission Letter'
# Set email body
content = 'You have been admitted to our university. Please keep the attached letter and report on time!'
# Set attachment
attachment = filename + '.docx'
# Build email dict
mail = {'subject': subject, 'content_text': content, 'attachments': attachment}
# Connect to QQ SMTP
server = zmail.server('[email protected]', 'your_authorization_code')
# Send email
server.send_mail(youxiang, mail)
time.sleep(2)
print('Email ' + str(i+1) + ' sent successfully')

After sending, you can verify the email in your QQ inbox.

Full Script

# Import libraries
from docxtpl import DocxTemplate
import pandas as pd
import zmail
import time

# Read data source
df = pd.read_excel('123_mail_merge_data_source.xls')
print(df)

# Process each record
for i in range(len(df)):
    contxt = dict(df.iloc[i])
    filename = contxt['姓名']
    youxiang = contxt['邮箱']
    tpl = DocxTemplate('mail_merge_template.docx')
    tpl.render(contxt)
    tpl.save(filename + '.docx')
    subject = 'Admission Letter'
    content = 'You have been admitted to our university. Please keep the attached letter and report on time!'
    attachment = filename + '.docx'
    mail = {'subject': subject, 'content_text': content, 'attachments': attachment}
    server = zmail.server('[email protected]', 'your_authorization_code')
    server.send_mail(youxiang, mail)
    time.sleep(2)
    print('Email ' + str(i+1) + ' sent successfully')
print('All emails have been sent successfully')