【自動郵件】如何利用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)