from openpyxl import load_workbook
import smtplib
from email.mime.text import MIMEText # 邮件正文
from email.header import Header # 邮件头
# 加载excel 文件
wb = load_workbook("工资表.xlsx",data_only=True) # 不显示公式，只显示数据
# 登录邮件服务器
smtp_obj = smtplib.SMTP_SSL("smtp.qq.com",465) # 发件人邮箱中的SMTP服务器，端口是25
smtp_obj.login("12***9@qq.com","bouddzvqmexgbage") # 发件人邮箱账号，邮箱密码
sheet = wb.active
count = 0
for row in sheet:
count += 1
if count ==1:
row_head = "<thead>" # 开始一行
for cell in row:
row_head += f"<th>{cell.value}</th>"
row_head += "</thead>" # 结束一行
continue
row_text = "<tr>" # 开始一行
for cell in row:
row_text += f"<td>{cell.value}</td>"
row_text += "</tr>" # 结束一行
name = row[0]
staff_email = row[1].value
mail_body_context = f"""
<h3>{name.value},你好：</h3>
<p>请查收你2021-05月的工资条，如有疑问，请与****联系...</p>
<table border = "1px solod black">
{row_head}
{row_text}
</table>
"""
msg = MIMEText(mail_body_context,"html","utf-8") # "plain"纯 文本格式
msg["From"]= Header("大唐人事部","utf-8") # 发送者
msg["To"] = Header("大唐员工","utf-8") # 接收者
msg['Subject'] = Header("大唐建设集团2021-05月工资","utf-8") # 主题
# 发送
smtp_obj.sendmail("129**9@qq.com",["te*g@sina.com","te*g@163.com"],msg.as_string())
