【自动邮件】如何利用python自动发邮件(附件excel发送)

背景： 自动发送邮件可以将常规化得需求自动化，解放双手做更多有意义的事情

以下即为代码：

# -*- coding: utf-8 -*-
"""
Created on Wed Jul  3 12:22:58 2019

@author: wuxian
"""
import smtplib
import email.mime.multipart
import email.mime.text
import email.mime.base
import os.path
import xlwt
import time
import schedule
import sys
import os
with open(r'.\Desktop\PID.txt','a') as file:
    file.write(time.strftime('%Y-%m-%d %H:%M:%S',time.localtime())+'\t')
    file.write(str(sys.argv[0]).split('\\')[-1]+'\t')
    file.write('PID:'+str(os.getpid())+'\t')
    file.write('PPID:'+str(os.getppid())+'\n')
#open-'a':写入打开，如果文件存在则添加到尾部
# =============================================================================
#连接数据库
# =============================================================================
import pymysql
def dbreadsql(v_sql):
    conn = pymysql.connect(host=None, port=3306,
                       user='root', passwd='wx123456', db= 'testwuxian',
                       charset='utf8')
    cur = conn.cursor()
    cur.execute(v_sql)
    rows = cur.fetchall()
    cur.close()
    conn.close()
    return  rows


sql0 = "select * from testwuxian.register_info"
#sql1 = "select * from testwuxian.register_detail"
sql2 = "select * from testwuxian.search_info"
sql3 = "select * from testwuxian.search_detail"


rows0 = dbreadsql(sql0)
#rows1 = dbreadsql(sql1)
rows2 = dbreadsql(sql2)
rows3 = dbreadsql(sql3)


# 生成excel文件名
def mailsend(rows0,rows2,rows3):
    v_file_name = 'COCOK生产库数据.xls'
    # 生成excel文件
    book = xlwt.Workbook()#
    sheet1 = book.add_sheet('注册人数', cell_overwrite_ok=True)
#    sheet2 = book.add_sheet('注册明细', cell_overwrite_ok=True)
    sheet3 = book.add_sheet('关键字搜索次数', cell_overwrite_ok=True)
    sheet4 = book.add_sheet('关键字搜索明细', cell_overwrite_ok=True)
    #表头标题
    sheet1.write(0, 0, '注册日期')
    sheet1.write(0, 1, '注册人数')
    
#    sheet2.write(0, 0, '注册日期')
#    sheet2.write(0, 1, '手机号')
#    sheet2.write(0, 2, '设备型号')
#    sheet2.write(0, 3, '神策标识码') 
    
    sheet3.write(0, 0, '搜索日期')
    sheet3.write(0, 1, '搜索总次数')
    
    sheet4.write(0, 0, '搜索日期')
    sheet4.write(0, 1, '搜索关键字')
    sheet4.write(0, 2, '搜索次数')

    # 每一列写入excel文件，不然数据会全在一个单元格中
    for i in range(len(rows0)):
        for j in range(2):
            # print (rows[i][j])-
            # print ("--------")
            sheet1.write(i + 1, j, rows0[i][j])

#    for i in range(len(rows1)):
#        for j in range(4):
#            # print (rows[i][j])-
#            # print ("--------")
#            sheet2.write(i + 1, j, rows1[i][j])            
            
    for i in range(len(rows2)):
        for j in range(2):
            # print (rows[i][j])-
            # print ("--------")
            sheet3.write(i + 1, j, rows2[i][j])   
            
    for i in range(len(rows3)):
        for j in range(3):
            # print (rows[i][j])-
            # print ("--------")
            sheet4.write(i + 1, j, rows3[i][j])               
    
    book.save("C:/Users/wuxian/Desktop/" + v_file_name)
    
    # 邮件信息
    From = "发送者邮箱"
    To =  "收件人邮箱"
    Cc = "抄送人邮箱"
    file_name = v_file_name
    server = smtplib.SMTP_SSL("smtp.aliyun.com",465)
    server.login("邮箱", "密码")  # 仅smtp服务器需要验证时
    
    # 构造MIMEMultipart对象做为根容器
    main_msg = email.mime.multipart.MIMEMultipart()
    
    # 构造MIMEText对象做为邮件显示内容并附加到根容器
    text_msg = email.mime.text.MIMEText("Hi All,\n \n  正文部分内容，请查收，谢谢。\n \n Best Wishes!")
    main_msg.attach(text_msg)
    
    
    # 构造MIMEBase对象做为文件附件内容并附加到根容器
    maintype,subtype = 'ershoufanglianjia','.xls'
    
    # 读入文件内容并格式化
    data = open("C:/Users/wuxian/Desktop/" + file_name, 'rb')
    file_msg = email.mime.base.MIMEBase(maintype, subtype)
    file_msg.set_payload(data.read())
    data.close()
    email.encoders.encode_base64(file_msg)
    
    # 设置附件头
    basename = os.path.basename("./Desktop/" + file_name)
    file_msg.add_header('Content-Disposition',
                        # 附件如果有中文会出现乱码问题，加入gbk
                        'attachment', filename=('gbk', '', basename))
    main_msg.attach(file_msg)
    
   
    # 设置根容器属性
    main_msg['From'] = From
    main_msg['To'] = To
    main_msg['Cc'] = Cc
    main_msg['Subject'] = "邮件主题"
    main_msg['Date'] = email.utils.formatdate()
    
    # 得到格式化后的完整文本
    fullText = main_msg.as_string()
# 用smtp发送邮件
    try:
        server.sendmail(From, To.split(',') + Cc.split(','), fullText)
        print("发送成功")
    except Exception as e:
        print("发送失败")
        print(str(e))
    finally:
        server.quit()         
mailsend(rows0,rows2,rows3)  
  
#若是需要定时发送则使用以下代码：
#schedule.every().day.at('8:00').do(mailsend) 
#while True:
#    schedule.run_pending()
#    time.sleep(60)