import smtplib
import time
from email.header import Header  # 邮件头
from email.mime.text import MIMEText  # 邮件正文
from openpyxl import load_workbook

Month = int(time.strftime("%m", time.localtime())) - 1
Year = time.strftime("%Y", time.localtime())

# 加载excel文件
wb = load_workbook("filenae.xlsx", data_only=True)
sheet = wb.active

# 登录邮件服务器
smtp_obj = smtplib.SMTP_SSL("smtp.qq.com", 465)  # 发件⼈邮箱中的SMTP服务器，端⼝是465
smtp_obj.login("********@qq.com", "**************")  # 括号中对应的是发件⼈邮箱账号、邮箱密码。 需要配置邮箱SMTP服务

# 循环excel
count = 0
table_header = '<thead>'
for row in sheet.iter_rows(min_col=2):
    count += 1
    if count == 1:  # first row
        for col in row:
            table_header += f'<th>{col.value}</th>'
        table_header += '</thead>'
        continue
    else:

        row_text = '<tr>'
        for col in row:
            row_text += f'<td>{col.value}</td>'
        row_text += "</tr>"
        name = row[0]
        staff_email = row[-1].value

        # 设置邮件头信息
        mail_body_context = f'''
            <h3>{name.value},你好：</h3>
            <p >请查收{Year}年{Month}月的工资条。</p>
            <table style="text-align: center; width: 800px;" border='1px solid black', border-collapse: collapse, cellpadding="0", cellspacing="0">
            {table_header}
            {row_text}
            </table>
        '''
        msg_body = MIMEText(mail_body_context, "html", "utf-8")
        msg_body["From"] = Header("公司名称", "utf-8")  # 发送者
        msg_body["To"] = Header(f"{name.value}", "utf-8")  # 接收者
        msg_body["Subject"] = Header(f"公司名称{Year}年{Month}月工资", "utf-8")  # 主题

        # 发送
        smtp_obj.sendmail("********@qq.com", [staff_email, ], msg_body.as_string())
        print(f"给{name.value}的邮件发送成功！")

文件格式如下：（可以调整）