一、场景描述
1.所有员工的工资条存放在一个excel表中，如下图格式
2.需要实现自动把工资条发送到员工邮箱，并且工资条为表格形式的，如下图
二、编写Python脚本，使用openpyxl 和 smtplib 模块完成，发送邮件为HTML格式的
# -*- coding:utf-8 -*-# created by xxxfrom openpyxl import load_workbookimport smtplibfrom email.mime.text import MIMEText # 邮件正文from email.header import Header # 邮件头# 加载excel文件，data_only=True(excel中工资为函数计算的值，邮件中要把公式转换为计算值)wb = load_workbook("5月工资.xlsx",data_only=True)sheet = wb.active# 登录网易邮箱smtp_obj = smtplib.SMTP_SSL("", 465)smtp_obj.login("xxx@", "xxxxxx")# 循环excelcount = 0table_col_html = '<thead>' # 表格表头for row in sheet.iter_rows(min_row=1,max_row=26):count += 1if count == 1 : # first rowfor col in row:table_col_html += f"<th>{col.value}</th>"table_col_html += "</thead>"continueelse:row_text = "<tr>" # 表格开始一行for cell in row:row_text += f"<td>{cell.value}</td>"row_text += "</tr>" # 表格结束一行name = row[2]staff_email = row[1].valueprint(staff_email,name.value)#邮件内容mail_body_context = f'''<h3>{name.value},你好:</h3><p>请查收你的工资条。。。。</p><table border="1px solid black"> {table_col_html}{row_text}</table>'''msg_body = MIMEText(mail_body_context,"html","utf-8")#判断每发送10封重新登录一次（本脚本测试得知网易邮箱单次登录最多发送11封邮件，并且不适用于qq邮箱 qq邮箱会检测为垃圾邮件）if count % 10 == 0:smtp_obj = smtplib.SMTP_SSL("", 465)smtp_obj.login("xxx@", "xxxxxx")msg_body["From"] = Header("人事部", "utf-8") # 发送者msg_body["To"] = Header("员工", "utf-8") # 接收者msg_body["Subject"] = Header("5月工资", "utf-8") # 主题# 发邮件smtp_obj.sendmail("xxx@", [staff_email,],msg_body.as_string() )print(f"成功发送工资条到{staff_email}- {name.value}....")
