Python 自动发送邮件 - ProNovia 提醒 - 常规

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

import openpyxl
import pandas as pd
import os

# 邮件发送信息
sender = 'PTCN.ETS@cn.bosch.com'
mail_user = ''  # 请输入 NT 账号
mail_pwd = ''  # 请输入 NT 密码
subtables_path = r'C:\Users\LOH2HZ\Desktop\2023.6 email sending\SubTables'  # 请输入子表文件夹路径

# 读取邮件信息
wb = openpyxl.load_workbook('mail_info.xlsx')
ws = wb['Sheet1']
cc = ws['C2'].value.split(',') if ws['C2'].value else []

# 读取收件人和抄送人信息
df = pd.read_excel('Project manager email address_test.xlsx')
receiverList = df['Email address'].astype(str).tolist()
cc.extend(df['CC'].astype(str).tolist())
receiverList.extend(cc)

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

# 循环遍历 DataFrame
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')
    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())
    else:
        print(f'Subtable for {pm_name} not found')

smtpObj.quit()  # 关闭连接

使用方法：

填写邮件发送信息（NT 账号、NT 密码、子表文件夹路径）。
确保 mail_info.xlsx 和 Project manager email address_test.xlsx 文件存在于当前目录。
运行 Python 脚本，自动发送邮件。

注意：

请确保填写正确的邮件发送信息和文件路径。
此脚本使用 HTML 格式发送邮件，建议使用支持 HTML 格式的邮件客户端查看邮件。
可以根据实际需求修改邮件内容和附件。
请勿将此脚本用于发送垃圾邮件或恶意邮件。