导入必要的模块

import pandas as pd import smtplib from email.mime.text import MIMEText from email.mime.multipart import MIMEMultipart from email.mime.application import MIMEApplication import os

定义发件人和发件人密码

sender = 'sender@example.com' password = 'password'

定义收件人列表的 Excel 文件路径

recipients_path = r'C:\Users\LOH2HZ\Desktop\2023.6 email sending\Recipients.xlsx'

读取收件人列表 Excel 文件

df = pd.read_excel(recipients_path)

定义 SMTP 服务器和端口

smtp_server = 'smtp.example.com' smtp_port = 587

连接 SMTP 服务器

smtpObj = smtplib.SMTP(smtp_server, smtp_port) smtpObj.ehlo() # 向服务器发送 EHLO 命令 smtpObj.starttls() # 启用 TLS 加密

登录 SMTP 服务器

smtpObj.login(sender, password)

定义子表文件夹的路径

subtables_path = r'C:\Users\LOH2HZ\Desktop\2023.6 email sending\SubTables'

遍历 Excel 文件的每一行并附加相应的子表

for i, row in df.iterrows(): pm_name = row['Project manager'] # 获取 Project manager 的名字 pm_email = row['Email address'] # 获取 Project manager 的邮件地址

# 判断 Project manager 的信息是否完整
if pd.isna(pm_name) or pd.isna(pm_email):
    print(f'Missing information for row {i}')
    continue

# 创建一个 MIMEMultipart 对象作为邮件的主体
msg = MIMEMultipart()
msg['Subject'] = 'Open ProNovia E-mail reminder'  # 设置邮件主题
msg['From'] = sender  # 设置发件人
msg['To'] = pm_email  # 设置收件人

content = '''

'''  # 设置邮件正文

puretext = MIMEText(content, 'html', 'utf-8')  # 创建一个 MIMEText 对象表示纯文本 HTML 邮件正文
msg.attach(puretext)  # 将纯文本邮件正文附加到邮件主体中

excel_path = os.path.join(subtables_path, f'{pm_name}.xlsx')  # 获取该 Project manager 的子表路径
if os.path.exists(excel_path):
    xlsxpart = MIMEApplication(open(excel_path, 'rb').read())  # 创建一个 MIMEApplication 对象表示 Excel 文件附件
    xlsxpart.add_header('Content-Disposition', 'attachment', filename=f'{pm_name}.xlsx')  # 设置 Excel 文件附件的信息
    msg.attach(xlsxpart)  # 将 Excel 文件附件附加到邮件主体中
    smtpObj.sendmail(sender, pm_email, msg.as_string())  # 发送邮件给该 Project manager
else:
    print(f'Subtable for {pm_name} not found')

发送完所有的邮件后关闭连接

smtpObj.quit()