发工资啦！教你用Python实现邮箱自动群发工资条

一、excel的内容

二、效果

三、需要用的库：

openpyxl
smptlib
email.mime.text
email.header

四、实现步骤

4.1 获取excel表的数据

wb = load_workbook(\'数据表.xlsx\')
sheet = wb.active
for row in sheet:
	for cell in row:
        print(cell.value)

4.2 编写邮件内容

使用字符串拼接成html

for row in sheet:
        tbody = \'<tr>\'
        cnt += 1
        if cnt == 1:
            for cell in row:
                thead += f\'<th>{cell.value}</th>\'
            thead += \'</thead>\'
        else:
            for cell in row:
                tbody += f\'<td>{cell.value}</td>\'
            tbody += \'</tr>\'
        name = row[0].value
        mail = row[1].value
        #  2.编写邮件内容
        content = f\'\'\'
            <h3>{name},你好</h3>
            <p>请查收你在2025年 5月1日 - 5月31 日的工资</p>
            <table border=\'1px solid black\'>
            {thead}
            {tbody}
            </table>
        \'\'\'

4.3 发送邮件

# 发送邮件
class Test:
    def ck_log(self):
        pass

    def send_email(self, econtent, ename, mail):
        host = \'smtp.qq.com\'
        user = \'你的邮箱\'
        password = \'你的授权码\'
        receivers = [mail]
        subject = \'员工工资表\'
        msg = MIMEText(econtent, \'html\', \'utf-8\')
        msg[\'From\'] = Header(\'有限公司\')
        msg[\'To\'] = Header(ename)
        msg[\'Subject\'] = Header(subject, \'utf-8\')

        try:
            obj = smtplib.SMTP_SSL(host, 465)
            obj.login(user, password)
            obj.sendmail(user, receivers, msg.as_string())
            print(\"邮件发送成功！\")
        except smtplib.SMTPException as e:
            print(\"Error:　无法发送邮件\")
            print(e)

五、所有代码

from openpyxl import load_workbook
import smtplib
from email.mime.text import MIMEText
from email.header import Header

\'\'\'
1.获取excel表的数据
2.编写邮件内容
3.发送邮件
\'\'\'


# 发送邮件
class Test:
    def ck_log(self):
        pass

    def send_email(self, econtent, ename, mail):
        host = \'smtp.qq.com\'
        user = \'1479898695@qq.com\'
        password = \'bijoplffwqqlbaci\'
        receivers = [mail]
        subject = \'员工工资表\'
        msg = MIMEText(econtent, \'html\', \'utf-8\')
        msg[\'From\'] = Header(\'有限公司\')
        msg[\'To\'] = Header(ename)
        msg[\'Subject\'] = Header(subject, \'utf-8\')

        try:
            obj = smtplib.SMTP_SSL(host, 465)
            obj.login(user, password)
            obj.sendmail(user, receivers, msg.as_string())
            print(\"邮件发送成功！\")
        except smtplib.SMTPException as e:
            print(\"Error:　无法发送邮件\")
            print(e)


if __name__ == \'__main__\':
    wb = load_workbook(\'数据表.xlsx\')
    o = Test()
    cnt = 0
    sheet = wb.active
    thead = \'<thead>\'
    #  1.获取excel表的数据
    for row in sheet:
        tbody = \'<tr>\'
        cnt += 1
        if cnt == 1:
            for cell in row:
                thead += f\'<th>{cell.value}</th>\'
            thead += \'</thead>\'
        else:
            for cell in row:
                tbody += f\'<td>{cell.value}</td>\'
            tbody += \'</tr>\'
        name = row[0].value
        mail = row[1].value
        #  2.编写邮件内容
        content = f\'\'\'
            <h3>{name},你好</h3>
            <p>请查收你在2025年 5月1日 - 5月31 日的工资</p>
            <table border=\'1px solid black\'>
            {thead}
            {tbody}
            </table>
        \'\'\'
        #  3.发送邮件
        if cnt == 3:
            print(\'content:\', content)
            print(name, mail)
            o.send_email(content, name, mail)