导入所需的模块

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

定义发送邮件的账号和密码

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

定义收件人列表的 Excel 文件路径和工作表名称

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

读取收件人列表

df = pd.read_excel(recipients_path, sheet_name=recipients_sheet)

连接 SMTP 服务器

smtpObj = smtplib.SMTP('smtp.example.com', 587) smtpObj.starttls() 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'] pm_email = row['Email address']

if pd.isna(pm_name) or pd.isna(pm_email):
    print(f'Missing information for row {i}')
    continue

# 创建邮件
msg = MIMEMultipart()
msg['Subject'] = 'Open ProNovia E-mail reminder' # 主题
msg['From'] = sender # 发件人
msg['To'] = pm_email # 收件人

# 正文
content='''

'''

puretext = MIMEText(content,'html','utf-8') # 纯文本
msg.attach(puretext)

# 附件
excel_path = os.path.join(subtables_path, f'{pm_name}.xlsx')
if os.path.exists(excel_path):
    xlsxpart = MIMEApplication(open(excel_path, 'rb').read())
    xlsxpart.add_header('Content-Disposition', 'attachment', filename=f'{pm_name}.xlsx')
    msg.attach(xlsxpart)
    smtpObj.sendmail(sender, pm_email, msg.as_string()) # 发送邮件给该 Project manager
else:
    print(f'Subtable for {pm_name} not found')

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

smtpObj.quit()