python发邮件excel文件处理+邮件发送+实战：批量化发工资条

发送一封最简单的信语法如下：

import smtplib
from email.mime.text import MIMEText # 邮件正⽂文
from email.header import Header # 邮件头
# 登录邮件服务器器
smtp_obj = smtplib.SMTP_SSL("smtp.qq.com", 465) # 发件⼈人邮箱中的SMTP服务器，端⼝口是25
smtp_obj.login("xxxx@qq.com", "密码") # 括号中对应的是发件⼈人邮箱账号、邮箱密码
#smtp_obj.set_debuglevel(1) # 显示调试信息
# 设置邮件头信息
msg = MIMEText("Hello, ", "plain", "utf-8") #plain文本格式
msg["From"] = Header("来自美的问候","utf-8") # 发送者
msg["To"] = Header("有缘⼈人","utf-8") # 接收者
msg["Subject"] = Header("娜美的信","utf-8") # 主题
# 发送
smtp_obj.sendmail("xxxxx@qq.com", ["收件人邮箱1","收件人邮箱2"], msg.as_string())
print(f"成功发送到")

批量化发送excel表格邮箱与内容

from openpyxl import load_workbook
import smtplib
from email.mime.text import MIMEText # 邮件正⽂文
from email.header import Header # 邮件头
#加载excel文件
wb = load_workbook ("工资.xlsx")# 表格与py文件放在同一文件夹
sheet = wb.active
 
# 登录邮件服务器器
 
smtp_obj = smtplib.SMTP_SSL("smtp.qq.com", 465) # 发件⼈人邮箱中的SMTP服务器，端⼝口是25
smtp_obj.login("发件邮箱", "授权码") # 括号中对应的是发件⼈人邮箱账号、邮箱密码
 
#循环excel
for row in sheet.iter_rows(min_row=2,max_row=3):
    row_text=""
    for cell in row:
        # print(cell.value,end=",")
        row_text+=f"{cell.value}"
    name = row[2]
    staff_email=row[1].value
    print(staff_email,name.value)
    
 
    mail_body = f'''
        <h3>{name.value} ,你好:</h3>
        <p>请查收你2022-05月的工资条
        {row_text}
        </p>|
    '''
    msg = MIMEText(mail_body,"html", "utf-8") #plain文本格式
    msg["From"] = Header("人事部","utf-8") # 发送者
    msg["To"] = Header(staff_email,"utf-8") # 接收者
    msg["Subject"] = Header("邮件主题","utf-8") # 主题
    smtp_obj.sendmail("发件邮箱", [staff_email,], msg.as_string())
    print(f"成功发送到{staff_email} to {name.value}.")

表格一同发送

from openpyxl import load_workbook
import openpyxl
import smtplib
from email.mime.text import MIMEText # 邮件正⽂文
from email.header import Header # 邮件头
#加载excel文件
wb = load_workbook ("工资.xlsx",data_only=True)
sheet = wb.active
 
# 登录邮件服务器器
 
smtp_obj = smtplib.SMTP_SSL("smtp.qq.com", 465) # 发件⼈人邮箱中的SMTP服务器，端⼝口是25
smtp_obj.login("xxx@qq.com", "xxx") # 括号中对应的是发件⼈人邮箱账号、邮箱密码
 
count=0
table_col_html="<thead>"
 
#循环excel
for row in sheet.iter_rows(min_row=1,max_row=2):
    count+=1
    if count==1:
        for col in row:
            table_col_html+="<th>{col.value}</th>"
        table_col_html+="</thead>"
        continue
    else:    
        row_text="<tr>" 
        for cell in row:
            # print(cell.value,end=",")
            row_text+=f"<td>{cell.value}</td>"
        row_text+="</tr>"   
        name = row[2]
        staff_email=row[1].value
        print(staff_email,name.value)
    
 
    mail_body= '''
        <h3> {name.value},你好: </h3>
        <p>请查收你2022-05月的工资条 </p>
        <table border="1px solid black">
        {table_col_html}
        {row_text}
        </table> 
        '''
    print(mail_body)
    msg = MIMEText(mail_body,"html", "utf-8") #plain文本格式
    msg["From"] = Header("人事部","utf-8") # 发送者
    msg["To"] = Header("员工","utf-8") # 接收者
    msg["Subject"] = Header("工资条","utf-8") # 主题
    smtp_obj.sendmail("8xxxxm", [staff_email], msg.as_string())
    print(f"成功发送到{staff_email} to {name.value}.")