Python 批量发送邮件带附件，根据Excel表格信息定制内容

import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.application import MIMEApplication

import openpyxl


wb = openpyxl.load_workbook('mail_info.xlsx') # 打开Excel文件
ws = wb['Sheet1'] # 选择指定的表格

sender='PTCN.ETS@cn.bosch.com'  # 公邮
mail_user = ws['A2'].value # 读取指定单元格的值  nt 账号
mail_pwd = ws['B2'].value  # nt pwd


smtpObj = smtplib.SMTP('rb-smtp-auth.rbesz01.com','25')
smtpObj.starttls()
smtpObj.login(mail_user,mail_pwd)


receiverList=[]
cc=[]

import pandas as pd
df = pd.read_excel('Project manager email address_test.xlsx')
receiverList = df['Email address'].astype(str).tolist() # convert to string
cc = df['CC'].astype(str).tolist() # convert to string

msg = MIMEMultipart()
msg['Subject'] = 'Open ProNovia E-mail reminder'
msg['From'] = sender
msg['To'] = ','.join(receiverList) #need to be a string split by ','
msg['Cc'] = ','.join(cc)

receiverList.extend(cc) # add cclist to receiverlist


import os

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='''
    Dear PJMs & VS managers,<br>
    <br>
    This is a warm E-mail reminder about open ProNovia process of new components in your project.<br>
    <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<strong>·</strong>&nbsp;You can check your ProNovia status and push relevant associates to fast release components.<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(LINK:<a href='file://bosch.com/DfsRB/DfsCN/LOC/Hz/PTCN/Engineering/PT-BE-ETS-HZ_Act/EIS_Report/Pronovia/Reference/How%20to%20check%20components%20are%20stuck%20in%20which%20department.pdf'>How to check components are stuck in which department</a>)<br>
    <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<strong>·</strong>&nbsp;You can issue a CR to discontinue components if they are not needed any more or project cancelled.<br>
    <br>
    Let's make our effort together to shorten the components release duration!<br>
    <br>Thanks.<br>
    <br>PT-BE/ETS1-Hz Team<br>
    '''

    puretext = MIMEText(content,'html','utf-8') # plain html
    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()  # 发送完所有的邮件后关闭连接

这段代码可以根据实际需求进行修改。以下是一些可能的修改选项：

修改邮件标题和内容：根据需要修改邮件的标题和内容，可以使用HTML格式进行排版。
修改附件：可以根据需要添加或删除附件。可以使用msg.attach()方法添加附件，并使用MIMEApplication类指定附件的路径和名称。
修改收件人和抄送人列表：可以根据需要修改收件人和抄送人列表。可以使用msg['To']和msg['Cc']分别指定收件人和抄送人的邮箱地址。
使用其他邮件服务器：可以根据需要使用其他邮件服务器。可以修改smtpObj = smtplib.SMTP('rb-smtp-auth.rbesz01.com','25')中的主机和端口号。
修改子表文件夹路径：可以根据需要修改子表文件夹的路径。可以修改subtables_path = r'C:\Users\LOH2HZ\Desktop\2023.6 email sending\SubTables'中的路径。
修改邮件发送逻辑：可以根据需要修改邮件的发送逻辑。可以根据具体需求编写循环来发送邮件给每个收件人。

请根据实际需求进行相应的修改，并测试代码以确保其正常工作。