Automate Bonus Emails with Python: Send Personalized Attachments in Minutes

Requirement Overview

The company needs to email bonus statements: first, send each department manager the corresponding department bonus Excel file, then, for the upgraded requirement, also email every employee their personal bonus amount using the email addresses listed in an Excel sheet.

Logic Flow

Iterate through 邮件地址.xlsx to obtain department names and manager email addresses.

For each department, locate the corresponding 奖金_部门名称.xlsx file and attach it to the manager's email.

Iterate through the same Excel to send individual emails containing employee ID, name, and bonus amount.

Code Implementation

Import required libraries:

from openpyxl import load_workbook
import yagmail
import keyring

password = keyring.get_password('yagmail', 'username')
# Set the password beforehand with keyring.set_password('yagmail', username, password)

Read the address file:

path = r'C:\xxx'  # Adjust to actual path
workbook = load_workbook(path + r'\邮件地址.xlsx')
sheet = workbook.active

n = 0  # Skip header
for row in sheet.rows:
    if n:
        department = row[0].value
        dep_address = row[1].value
        print(department, dep_address)
    n += 1

Send department‑level emails:

mail_dep = yagmail.SMTP(user='[email protected]', password=password, host='smtp.qq.com')
contents = [f'您好，请查收{department}的奖金情况', path + f'\奖金发放\奖金_{department}']
mail_dep.send(dep_address, f'{department}奖金情况', contents)

Upgrade to send individual employee emails:

n = 0
for row in sheet.rows:
    if n:
        department = row[0].value
        dep_address = row[1].value
        mail_dep = yagmail.SMTP(user='[email protected]', password=password, host='smtp.qq.com')
        workbook_new = load_workbook(path + f'\奖金发放\奖金_{department}')
        sheet_new = workbook_new.active
        m = 0
        for i in sheet_new.rows:
            if m:
                id = i[0].value
                name = i[1].value
                address = i[2].value
                money = i[3].value
                mail = yagmail.SMTP(user='[email protected]', password=password, host='smtp.qq.com')
                contents = [f'尊敬的 {department} {name} 您好，您的工号是 {id}，您2020年的奖金为 {money}']
                mail.send(address, f'{department}-{name}奖金情况', contents)
            m += 1
        contents_dep = [f'您好，请查收{department}的奖金情况', path + f'\奖金发放\奖金_{department}']
        mail_dep.send(dep_address, f'{department}奖金情况', contents_dep)
    n += 1

Images illustrating the Excel files and address list are omitted here for brevity but were included in the original article.

With roughly 30 lines of Python, the entire bonus distribution process becomes fully automated, eliminating manual effort.