如何为excel文件中的每个人发送电子邮件

Question

所以我有一个文件em.xlsx，其中我有名称&电子邮件列，当Name与目录

中的文件名匹配时，我想发送电子邮件。我该怎么做呢?到目前为止，我有下面的代码，但它实际上没有返回任何

import glob
import pandas as pd
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
import os
from collections import ChainMap
# Spreadsheet with emails and  names
email_list = pd.read_excel(r'C:UsersarabwOneDriveBureauhtmlem.xlsx')
folder_path= "C:/Users/arabw/OneDrive/Bureau/html/"
my_files=[{each_file.split(".")[0]:each_file} for each_file in os.listdir(folder_path) if each_file.endswith(".csv")]
my_files_dict = dict(ChainMap(*my_files))
# getting the names and the emails
names = email_list['Name']
emails = email_list['Email']

for i in range(len(emails)): # iterate through the records
# for every record get the name and the email addresses
name = names[i]
email = emails[i]
if my_files_dict.get(name):
print(f"file found:{my_files_dict.get(name)}") # attach this file : my_files_dict.get(name)
#Some  help needed from here I believe
while name == os.path:
smtp_ssl_host = 'xxxxx'
smtp_ssl_port = 465
email_from = "xxxxx"
email_pass = "xxxxx"
email_to = email
msg2 = MIMEMultipart()
msg2['Subject'] = "Present Record(s)"
msg2['From'] = email_from
msg2['To'] = email
fo=open(my_files_dict.get(name),'rb')
attach = email.mime.application.MIMEApplication(fo.read(),_subtype="xlsx")
fo.close()
attach.add_header('Content-Disposition','attachment',my_files_dict.get(name))
msg.attach(attach)
s2 = smtplib.SMTP_SSL(smtp_ssl_host, smtp_ssl_port)
s2.login(email_from, email_pass)  
s2.send_message(msg)
s2.quit()

Answer 1

以下代码通过了我的测试。希望能帮到你。我不知道while name == os.path:是什么意思，所以我就忽略它了。

import glob
import pandas as pd
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.mime.application import MIMEApplication
import email
import os
from collections import ChainMap
# Spreadsheet with emails and  names
email_list = pd.read_excel('email.xlsx')
folder_path= './Files/A'
my_files=[{each_file.split(".")[0]:each_file} for each_file in os.listdir(folder_path) if each_file.endswith(".csv")]
my_files_dict = dict(ChainMap(*my_files))
# getting the names and the emails
names = email_list['Name']
emails = email_list['Email']
for i in range(len(emails)): # iterate through the records
# for every record get the name and the email addresses
name = names[i]
email_item = emails[i]
file_name = my_files_dict.get(name)
if file_name:
print(f"file found:{file_name}") # attach this file : my_files_dict.get(name)
#Some  help needed from here I believe
# while name == os.path:
smtp_ssl_host = 'smtp.xxx.com'
smtp_ssl_port = 465
email_from = "xxxxx"
email_pass = "xxxxx"
email_to = email_item
msg = MIMEMultipart()
msg['Subject'] = "Present Record(s)"
msg['From'] = email_from
msg['To'] = email_item
fo=open(os.path.join(folder_path, file_name), 'rb')
attach = MIMEApplication(fo.read(), _subtype="xlsx")
fo.close()
attach.add_header('Content-Disposition', 'attachment', filename=file_name)
msg.attach(attach)
smtp = smtplib.SMTP_SSL(smtp_ssl_host, smtp_ssl_port)
try:
smtp.login(email_from, email_pass)
smtp.sendmail(email_from, email_to, msg.as_string())
except smtplib.SMTPException as e:
print("send fail", e)
else:
print("success")
finally:
try:
smtp.quit()
except smtplib.SMTPException:
print("quit fail")
else:
print("quit success")