Phython 根据列名提取excel表格，实现邮件批量发送（SMTP）

公司使用阿里邮箱服务，但是做市场推广又没办法一次性导入成千上万联系人及联系方式，故想出调用SMTP（Simple Mail Transfer Protocol）来提取excel里面固定列名的数据，提取出姓氏跟姓名，再插入一个html 文本框作为邮件body部分。

import tkinter as tk
from tkinter import filedialog, messagebox
import pandas as pd
import smtplib
import email
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.header import Header

# 定义发件人密码（直接在代码中填写）
SENDER_PASSWORD = 'REPLACE WITH YOUR AUTHENTIFICATION PASSWORD'  # 生成的应用专用密码

def send_emails(sender_email, recipients_file, subject, html_content):
    try:
        recipients_df = pd.read_excel(recipients_file)
        for index, row in recipients_df.iterrows():
            recipient = row['Email']
            contact_name = row['Contact Name']
            
            # 替换邮件内容中的 XXX
            personalized_html = html_content.replace('XXX', contact_name)
            
            msg = MIMEMultipart('alternative')
            msg['Subject'] = Header(subject, 'utf-8').encode()
            msg['From'] = sender_email
            msg['To'] = recipient
            msg['Reply-to'] = sender_email
            msg['Message-id'] = email.utils.make_msgid()
            msg['Date'] = email.utils.formatdate()

            # 添加 HTML 内容
            html_part = MIMEText(personalized_html, 'html', 'utf-8')
            msg.attach(html_part)

            with smtplib.SMTP_SSL('smtp.qiye.aliyun.com', 465) as client:
                client.set_debuglevel(1)  # Set to 1 for debugging
                client.login(sender_email, SENDER_PASSWORD)
                client.sendmail(sender_email, recipient, msg.as_string())

        messagebox.showinfo("成功", "所有邮件已成功发送！")
    except smtplib.SMTPConnectError as e:
        messagebox.showerror("错误", f"邮件发送失败，连接失败: {e}")
    except smtplib.SMTPAuthenticationError as e:
        messagebox.showerror("错误", f"邮件发送失败，认证错误: {e}")
    except smtplib.SMTPSenderRefused as e:
        messagebox.showerror("错误", f"邮件发送失败，发件人被拒绝: {e}")
    except smtplib.SMTPRecipientsRefused as e:
        messagebox.showerror("错误", f"邮件发送失败，收件人被拒绝: {e}")
    except smtplib.SMTPDataError as e:
        messagebox.showerror("错误", f"邮件发送失败，数据接收拒绝: {e}")
    except smtplib.SMTPException as e:
        messagebox.showerror("错误", f"邮件发送失败: {e}")
    except Exception as e:
        messagebox.showerror("错误", f"邮件发送异常: {str(e)}")

def browse_file():
    filename = filedialog.askopenfilename(filetypes=[("Excel files", "*.xlsx")])
    if filename:
        file_path.set(filename)

def send_button_clicked():
    send_emails(
        sender_email.get(),
        file_path.get(),
        subject_entry.get(),
        text_content.get("1.0", tk.END).strip()
    )

app = tk.Tk()
app.title("邮件发送器")

tk.Label(app, text="发件人邮箱").pack()
sender_email = tk.StringVar()
tk.Entry(app, textvariable=sender_email).pack()

tk.Label(app, text="邮件主题").pack()
subject_entry = tk.Entry(app)
subject_entry.pack()

tk.Label(app, text="收件人Excel文件").pack()
file_path = tk.StringVar()
tk.Entry(app, textvariable=file_path).pack()
tk.Button(app, text="浏览", command=browse_file).pack()

tk.Label(app, text="自定义HTML内容 (使用 XXX 替换联系人姓名)").pack()
text_content = tk.Text(app, height=10, width=50)
text_content.pack()

tk.Button(app, text="发送邮件", command=send_button_clicked).pack()

app.mainloop()

另外貌似可以通过一些骚操作获取收信方的行为，比如打开邮件时间，日期，打开邮件国家。
原理就是在邮件中插入一个透明像素图片，用户在收取邮件时会自动下载该图片。服务器收到这个GET之后，就可以获取到用户的IP地址，访问时间，停留时间；
甚至，我们可以对每一封邮件里插入的像素点生成唯一的参数，从而实现判断XXX@XXX.com这个用户是否查看了邮件。