Python读取Excel发邮件遇报错：'list' object has no attribute 'encode'求解决

报错原因 #

原代码存在两个核心问题：

1. 错误获取收件人数据：list(email_df)取的是DataFrame的列名，而非Excel单元格里的邮箱地址
2. 参数类型不匹配：msg['To']邮件头需要分号分隔的字符串，而smtplib.sendmail的收件人参数需要邮箱列表，原代码把列表赋值给msg['To']，导致邮件头编码时触发AttributeError

场景1：读取A1单元格中分号分隔的邮箱 #

适用于所有收件人邮箱用分号存放在A1单元格的情况，代码如下：

import smtplib
from email.mime.multipart import MIMEMultipart
import pandas

sender = 'email@email.com'
smtp_server = 'relay.xxx.com'

# 读取A1单元格内容，header=None避免把单元格值识别为列名
email_df = pandas.read_excel("E-mails.xlsx", sheet_name="Emails", nrows=1, usecols="A", header=None)
# 提取A1单元格的原始邮箱字符串
raw_emails = email_df.iloc[0, 0]
# 分割为邮箱列表，去除空值和多余空格
receivers_list = [email.strip() for email in raw_emails.split(';') if email.strip()]
# 生成邮件头所需的分号分隔字符串
receivers_header = '; '.join(receivers_list)

msg = MIMEMultipart('related')
msg['Subject'] = 'Test mail'
msg['From'] = sender
msg['To'] = receivers_header  # 邮件头用字符串格式

with smtplib.SMTP(smtp_server) as server:
    # sendmail收件人参数用列表格式
    server.sendmail(sender, receivers_list, msg.as_string().encode('utf-8'))
    print(f"Successfully sent email to: {receivers_header}")

场景2：读取A列每个单元格的单个邮箱 #

适用于A列每行存一个收件人邮箱的情况，代码如下：

import smtplib
from email.mime.multipart import MIMEMultipart
import pandas

sender = 'email@email.com'
smtp_server = 'relay.xxx.com'

# 读取A列所有非空行，若A1是列名（如"收件人"）则删除header=None
email_df = pandas.read_excel("E-mails.xlsx", sheet_name="Emails", usecols="A", header=None)
# 收集所有非空邮箱，去除多余空格
receivers_list = email_df[0].dropna().str.strip().tolist()
# 生成邮件头所需的分号分隔字符串
receivers_header = '; '.join(receivers_list)

msg = MIMEMultipart('related')
msg['Subject'] = 'Test mail'
msg['From'] = sender
msg['To'] = receivers_header

with smtplib.SMTP(smtp_server) as server:
    server.sendmail(sender, receivers_list, msg.as_string().encode('utf-8'))
    print(f"Successfully sent email to: {receivers_header}")

关键注意事项 #

• 若Excel中A1是列名（而非邮箱），场景2代码需删除header=None，并将email_df[0]改为email_df['你的列名']
• strip()和dropna()用于清理无效数据，避免空邮箱导致发送失败
• msg.as_string().encode('utf-8')确保邮件内容编码正确，避免乱码

内容的提问来源于stack exchange，提问作者Dexsana