- 使用Python自动发送邮件（群发，加密等）
- 在工作中很多公司没有购买专业的OA系统，发工资条，发通知，有时很不方便，要么专人做这个事，一个一个发 ，太麻烦，耗费很多工时。这个程序只需要整理发送信息的excel就可以实现群发功能，还可以加密，大大缩减相关人员的工时。
# coding: utf-8
import tkinter as tk
from tkinter import filedialog
from datetime import datetime
import pandas as pd
import numpy as np
# import matplotlib.pyplot as plt
import os, calendar, time, copy, openpyxl, re, datetime
from openpyxl import load_workbook
import win32com.client, hashlib, logging, smtplib
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
# email 用于构建邮件内容
from email.header import Header
# 发送附件
from email.mime.application import MIMEApplication
# 发送多个部分
from email.mime.multipart import MIMEMultipart
# 专门发送文本的！MIMEText
from email.mime.text import MIMEText
logging.basicConfig(level=logging.DEBUG,
format='%(asctime)s line:%(lineno)d %(levelname)s : %(message)s',
datefmt=' %Y-%m-%d %H:%M:%S',
filename='邮件发送程序报错日志.log',
filemode='w')
todayDate = datetime.datetime.now().strftime("%Y-%m-%d")
str_email = input('请输入发件人邮箱：')
password = input('请输入发件人邮箱密码：')
sheet_name = input('请输入发送内容的类型（例如：排班考勤，社保公积金，等等）：')
class Config():
def __init__(self):
# 发信方的信息：发信邮箱，QQ 邮箱授权码
self.from_addr = '%s' % str_email
self.password = '%s' % password
# 发信服务器
self.smtp_server = 'smtp.exmail.qq.com'
self.port = 465
email_conf = Config()
wd = tk.Tk()
wd.withdraw()
print('请选择需要拆分的数据')
data_path = filedialog.askopenfilename()
print('请选择需要发送邮件的名单表')
send_path = filedialog.askopenfilename()
pd_info = pd.read_excel(data_path, header=0)
title_names = [i for i in pd_info.columns.values]
all_index = list(set(i for i in pd_info[u'拆分项']))
if u'工号\n（必填项）' in title_names:
pd_data = pd.read_excel(data_path, index_col=1, header=1, converters={u'工号\n（必填项）': str})
pd_data = pd_data.fillna(value='')
pd_data.set_index(u'拆分项', drop=True, append=False, inplace=True)
elif u'员工编码' in title_names:
pd_data = pd.read_excel(data_path, index_col=1, header=1, converters={u'员工编码': str})
pd_data = pd_data.fillna(value='')
pd_data.set_index(u'拆分项', drop=True, append=False, inplace=True)
elif u'工号' in title_names:
pd_data = pd.read_excel(data_path, index_col=1, header=1, converters={u'工号': str})
pd_data = pd_data.fillna(value='')
pd_data.set_index(u'拆分项', drop=True, append=False, inplace=True)
elif u'证件号' in title_names:
pd_data = pd.read_excel(data_path, index_col=1, header=1, converters={u'证件号': str})
pd_data = pd_data.fillna(value='')
pd_data.set_index(u'拆分项', drop=True, append=False, inplace=True)
else:
pd_data = pd.read_excel(data_path, header=0)
pd_data = pd_data.fillna(value='')
# pd_data.set_index(u'拆分项', drop=True, append=False, inplace=True)
try:
pd_emails = pd.read_excel(send_path, sheet_name=u'邮箱配置', converters={u'邮件加密密码': str})
pd_emails = pd_emails.fillna(value='')
# pd_emails.set_index(u'拆分项', drop=True, append=False, inplace=True)
except:
logging.info('读取邮件发送表中邮箱配置表失败')
try:
pd_msg = pd.read_excel(send_path, sheet_name=u'邮件正文配置')
pd_msg = pd_msg.fillna(value='')
# pd_msg.set_index(u'拆分项', drop=True, append=False, inplace=True)
except:
logging.info('读取邮件发送表中邮箱配置表失败')
# excel加密或更换密码
def pwd_xlsx(old_filename, new_filename, pwd_str, pw_str=''):
'''
:param old_filename: 需要加密Excel, 绝对路径
:param new_filename: 加密后的Excel，绝对路径
:param pwd_str: 加密密码
:param pw_str: 打开文件密码，默认无密码
:return:无
'''
xcl = win32com.client.Dispatch("Excel.Application")
# pw_str为打开密码, 若无 访问密码, 则设为 ''
wb = xcl.Workbooks.Open(old_filename, False, False, None, pw_str)
xcl.DisplayAlerts = False
# 保存时可设置访问密码.
wb.SaveAs(new_filename, None, pwd_str, '')
xcl.Quit()
os.remove(old_filename)
# 邮件加密
def general_excel(data, department, sheet_name, pwd):
excel_path = os.path.join(os.getcwd(), '%s.xlsx' % department)
pwd_excel = os.path.join(os.getcwd(), '%s.xlsx' % (department + sheet_name + todayDate))
writer = pd.ExcelWriter(excel_path)
data.to_excel(writer, '%s' % sheet_name, index=False)
writer.save()
pwd_xlsx(excel_path, pwd_excel, pwd)
try:
os.remove(excel_path)
except Exception as e:
logging.info(e)
return pwd_excel
def send_email(address: list, cc_addr: list, pwd_excel: list, msg:str, pwd:str):
logging.info(" send_email working......")
# 邮件标题title
title = "%s" % sheet_name
# 邮件正文内容
text = "%s" % msg + '\n' + "您的附件密码为：" + pwd
# # 邮箱正文内容，第一个参数为内容，第二个参数为格式(plain 为纯文本)，第三个参数为编码
# msg = MIMEText(msg, 'plain', 'utf-8')
# 构建邮件体
msg = MIMEMultipart()
msg['From'] = email_conf.from_addr
msg['To'] = ','.join(address)
msg['Cc'] = ','.join(cc_addr)
msg['Subject'] = Header(title, 'utf-8')
# 构建正文
part_text = MIMEText(text)
msg.attach(part_text)
if len(pwd_excel) > 0:
for i in pwd_excel:
# 构建邮件附件
part_attach = MIMEApplication(open(i, 'rb').read()) # 打开附件
filename = i.split("\\")[-1]
part_attach.add_header('Content-Disposition', 'attachment', filename=filename) # 为附件命名
msg.attach(part_attach) # 添加附件
time.sleep(2)
# 开启发信服务，这里使用的是加密传输
# server = smtplib.SMTP_SSL()
server = smtplib.SMTP_SSL(host=email_conf.smtp_server)
server.connect(email_conf.smtp_server, email_conf.port)
try:
# 登录发信邮箱5
server.login(email_conf.from_addr, email_conf.password)
# 发送邮件
server.sendmail(email_conf.from_addr, address + cc_addr, msg.as_string())
logging.info("邮件发送成功,发件人:%s 收件人:%s " % (email_conf.from_addr, address + cc_addr))
print("邮件发送成功,发件人:%s 收件人:%s " % (email_conf.from_addr, address + cc_addr))
except Exception as err:
logging.info("邮件发送失败：%s" % (err))
exit()
finally:
# 关闭服务器
server.quit()
for path in pwd_excel:
os.remove(path)
def get_emails(emails):
if len(emails) > 5:
new_emails = emails.replace(';', ',').replace('；', ',').replace('。', ',').replace('，', ',')
email_list = new_emails.split(',')
lt_emails = [i for i in email_list if len(i) > 15]
return lt_emails
else:
lt_emails = []
return lt_emails
def get_index(index):
if len(index) >= 13:
new_index = index.replace(';', ',').replace('；', ',').replace('。', ',').replace('，', ',')
index_list = new_index.split(',')
lt_index = [i for i in index_list if len(i) >= 1]
return lt_index
else:
lt_index = []
return lt_index
def get_name(names):
if len(names) >= 3:
new_index = names.replace(';', ',').replace('；', ',').replace('。', ',').replace('，', ',').replace('、', ',')
index_list = new_index.split(',')
lt_name = [i for i in index_list if len(i) >= 1]
return lt_name
else:
lt_name = []
return lt_name
for item in all_index:
try:
pwd_excel = []
lt_emails = []
lt_cc_emails = []
item_data = pd_data[pd_data[u'拆分项'] == item]
item_email = pd_emails[pd_emails[u'拆分项'] == item]
pwd = pd_emails[pd_emails[u'拆分项'] == item].iat[0, 3]
msg = pd_msg[pd_msg[u'拆分项'] == item].iat[0, 1]
for i, row in item_email.iterrows():
try:
emails = row[u'收件人邮箱']
cc_emails = row[u'抄送人邮箱']
sd_emails = get_emails(emails)
sd_cc_emails = get_emails(cc_emails)
if sd_emails:
for em in sd_emails:
if em not in lt_emails:
lt_emails.append(em)
if sd_cc_emails:
for cc in sd_cc_emails:
if cc not in lt_cc_emails:
lt_cc_emails.append(cc)
except Exception as e:
logging.info(e)
pwd_file = general_excel(item_data, str(item), u'%s' % sheet_name, pwd)
pwd_excel.append(pwd_file)
if len(pwd_excel) > 0:
send_email(lt_emails, lt_cc_emails, pwd_excel, msg, pwd)
else:
pass
except Exception as e:
logging.info(e)
# break
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
