Project Description

The function of this project is to use python to automatically read the mailbox list in the excel file, and send emails to the target mailbox in batches for customer mining. The item is sent one-to-one, and the other party will not see the email sent in groups (meaning that what the other party sees is that you sent it to the recipient individually, not in a group, that is, the recipient is only him). This project can use multiple mailboxes for sending. When the failure rate of a certain mailbox is higher than the set threshold, it will automatically switch to the next mailbox for sending. For emails that failed to be sent, after switching to the next mailbox, the email that failed to be sent in the previous mailbox will be sent first, and then the new email will continue to be sent. The tasks that have been sent will be automatically displayed in another table in the excel workbook. Before creating a new sending task each time, the system will check the mailboxes in the excel sheet that have been sent, and if it has already been sent, it will not repeat the sending. Items can be sent with or without attachments.

Instructions

1. Before using, be sure to change the path of the attachment in the project to the path of your own attachment
2. This project uses the SMTP service of the mailbox. Before using your own mailbox to send, you must first log in to the mailbox, open the smtp service, and obtain the authorization code of the mailbox (equivalent to a password, which is used to log in on a third-party client. Authorization code). If not, please refer to the link How to open the smtp service of qq mailbox and obtain the authorization code
3. After obtaining the authorization code, fill in your email address and authorization code in the code
4. Fill in the header and mailbox in excel
5. Install the program module if it is not installed
6. Start running the program.

project structure

complete project code

#Version 6:
#  coding:utf-8
import smtplib
from email.mime.application import MIMEApplication
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.utils import formatdate
from email.header import Header
import sys
from importlib import reload
import time
import numpy as np
import xlrd
import random
from openpyxl import load_workbook

def main(workbook, file_list, attachment_path, person_mail, have_attachment):

    """
    :param file_list: from excel read company information
    :param attachment_path:  The absolute path of the attachment
    :param person_mail:  Personal email account information
    :param have_attachment:  true Delegates need to send with attachments; false Delegate send without attachment
    :return:
    """

    #configuration parameters
    encoding = 'utf-8'
    global failTimes
    global staus_index
    global fail_sended
    result = 1     # 1 means normal end of the program; -1 means abnormal end
    sta_list=[]   #Initialize the sending status, and initialize it every time you change the mailbox. 0 means the sending is successful, and 1 means the sending fails.
    jishu=10
    fail_count_rate=0.5


    # Account login information and mail server information
    smtpHost = 'smtp.' + person_mail['username'].split('@')[1]
    #smtpPort = '25'
    sslPort = person_mail['sslPort']
    username = person_mail['username']
    password = person_mail['password']
    fromMail = person_mail['username']

    # Set the default character set to UTF8, otherwise sometimes there will be problems with transcoding
    default_encoding = 'utf-8'
    if sys.getdefaultencoding() != default_encoding:
        reload(sys)
        sys.setdefaultencoding(default_encoding)

    # login mail server
    smtp = login_mail_server(smtpHost, sslPort, username, password)
    if not smtp: #If the login fails, return -1 and change to another mailbox
        result=-1
        return result, staus_index;


    # Send the email that failed last time, and resend it again. ===== Clear the failure list after posting, and continue to accumulate new failure lists ================================== ============================ to be written ===========
    for j in range(1, len(fail_sended)):
        time.sleep(random.randint(2,6))

        # Construct mail destination
        company, area_company, to_email_list = getemail(fail_sended, j)

        # Construct the subject of the email, "+"[From China Mobile] Beidou high-precision positioning account and terminal service + the price is relatively the lowest in the country + China Mobile Communications Group Co., Ltd. + RTK positioning technology"
        subject = "XXXXX" +company+"，XXXX"

        # Construct the email body
        content = get_mail_text(have_attachment,company, area_company)


        mail_content = MIMEText(content.encode(encoding), 'html', encoding)
        # Construct email attachments
        if have_attachment:
            # Construct email attachments
            att1 = MIMEApplication(open(attachment_path, 'rb').read())
            att1['Content-Type'] = 'application/octet-stream'
            # The filename here can be written arbitrarily, what to write, what name will be displayed in the attachment, here is the real file name
            att1.add_header('Content-Disposition', 'attachment', filename=attachment_path.split("\\")[-1])

        # msg represents the mail object
        msg = MIMEMultipart()
        # Bind email subject and source email to email object
        msg['Subject'] = Header(subject, encoding)
        msg['From'] = Header(fromMail)

        # Bind the mail destination to the mail object
        msg['To'] = Header(';'.join(to_email_list), encoding)
        # Bind the mail body to the mail object
        msg.attach(mail_content)

        # Bind email attachments to email objects
        if have_attachment:
            msg.attach(att1)

        try:
            # send email
            smtp.sendmail(fromMail, to_email_list, msg.as_string())
            print("Sending the last batch failed to send the company->"+str(j) + ": ( " + fromMail + ")" + str(company) + "->" + str(len(to_email_list)) + "letter" + "->" + str(to_email_list))

        except Exception as e:

            print("failed to send again->+"+str(j + 1) + ": ( " + fromMail + ")" + str(company) + ':Failed to send,Abnormal:' + str(e))
        del msg
    print("Failed to send data last time->retransmission complete")

    sended_company=get_leiji_sended_company_xls(workbook)


    #Clear the last failed data and continue to accumulate new failed data
    fail_sended=[file_list[0]]
    failTimes=0
    # Pick up where you left off and continue sending emails
    for i in range(staus_index+1, len(file_list) ): # Starting from 1, 0 is the header.   i=1 is the first company

        time.sleep(random.randint(2,6))

        # Construct mail destination
        company, area_company, to_email_list = getemail(file_list, i)

        # Check if it has been sent previously. If sent, skip and continue to the next company
        if company in sended_company:
            print(str(staus_index + 1) + ": ( " + fromMail + ")" + str(company) + "->(Repeated sending) The previous task has already been sent" )
            continue


        # Construct the email subject,
        subject = "XXXXX" +company+"，XXXX"

        # Construct the email body
        content = get_mail_text(have_attachment,company, area_company)
        mail_content = MIMEText(content.encode(encoding), 'html', encoding)
        # Construct email attachments
        if have_attachment:
            # Construct email attachments
            att1 = MIMEApplication(open(attachment_path, 'rb').read())
            att1['Content-Type'] = 'application/octet-stream'
            # The filename here can be written arbitrarily, what to write, what name will be displayed in the attachment, here is the real file name
            att1.add_header('Content-Disposition', 'attachment', filename=attachment_path.split("\\")[-1])

        # msg represents the mail object
        msg = MIMEMultipart()
        # Bind email subject and source email to email object
        msg['Subject'] = Header(subject, encoding)
        msg['From'] = Header(fromMail)

        #This line of code is invalid because the empty mailbox company has been removed in advance
        if len(to_email_list)==0:
            print(company+'no mailbox')
            # Here you need to update the record parameters
            staus_index = i
            continue
        # Bind the mail destination to the mail object
        msg['To'] = Header(';'.join(to_email_list), encoding)
        # Bind the mail body to the mail object
        msg.attach(mail_content)

        #Bind email attachments to email objects
        if have_attachment:
            msg.attach(att1)

        try:
            # send email
            smtp.sendmail(fromMail, to_email_list, msg.as_string())

            sta_list.append(0)
            print(str(staus_index+1) + ": ( "+fromMail +")"+ str(company) + "->" + str(len(to_email_list)) + "letter" + "->" + str(to_email_list))
        except Exception as e:
            print(str(staus_index+1) + ": ( "+fromMail +")"+str(company) + ':Failed to send,Abnormal:'+str(e))
            sta_list.append(1)

            fail_sended.append(file_list[i])  # Save companies that failed to send
            failTimes+=1
            # failedSend_list.append(file_list[i])  # Save companies that failed to send


        staus_index = i

        del msg
        # Save the companies that have sent successfully, including individual sending exceptions, the number of exceptions=jishu*fail_count_rate
        saveDataTo_leijiSended(workbook, file_list[i])

        if fail_rate(sta_list,jishu)>fail_count_rate:
            result = -1
            return result, staus_index;


    print('Sent')
    smtp.close()
    return result, staus_index;

def get_mail_text(have_attachment,company, area_company):

    # Each paragraph has 3 ways, each paragraph can be combined freely
    p1=[
        f"XXXXXXXXXXXXXXXXXXXX{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx",
        f"XXXXXXXXXXXXXXXXXXXX{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx",
        f"XXXXXXXXXXXXXXXXXXXX{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx",
    ]

    p2=[
         f"XXXXXXXXXXXXXXXXXXXX{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx",
        f"XXXXXXXXXXXXXXXXXXXX{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx",
        f"XXXXXXXXXXXXXXXXXXXX{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx",
        ]
    p3=[
         f"XXXXXXXXXXXXXXXXXXXX{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx",
        f"XXXXXXXXXXXXXXXXXXXX{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx",
        f"XXXXXXXXXXXXXXXXXXXX{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx",
    ]
    p4 = [
        f"XXXXXXXXXXXXXXXXXXXX{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx",
        f"XXXXXXXXXXXXXXXXXXXX{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx",
        f"XXXXXXXXXXXXXXXXXXXX{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx",
    ]
    p5=[
         f"XXXXXXXXXXXXXXXXXXXX{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx",
        f"XXXXXXXXXXXXXXXXXXXX{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx",
        f"XXXXXXXXXXXXXXXXXXXX{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx",
    ]
    p6=[
       f"XXXXXXXXXXXXXXXXXXXX{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx",
        f"XXXXXXXXXXXXXXXXXXXX{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx",
        f"XXXXXXXXXXXXXXXXXXXX{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx",
    p7=[
        f"XXXXXXXXXXXXXXXXXXXX{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx",
        f"XXXXXXXXXXXXXXXXXXXX{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx",
        f"XXXXXXXXXXXXXXXXXXXX{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx{area_company}XXXXXXXXXXXXXXXXXXXXXXXXXXx",
    ]
    p8=[]
    p9=[]


    if have_attachment:
        content="""
                          html The text of the code editing page, the content can be changed according to the variables, and can be freely combined,
                 """
    else:
        content = """
                    html The text of the code editing page, the content can be changed according to the variables, and can be freely combined,
                 """




    return content

def saveDataTo_leijiSended(workbook, filelist_row):
    ws = workbook['Cumulative sent']
    ws1 = workbook['Summary of this task']

    # Get the cumulative sent headers
    title_lieji_sended = []
    for title in ws[1]:
        title_lieji_sended.append(title.value)
    # Get the header of this sending task
    title_benci_send = []
    for title1 in ws1[1]:
        title_benci_send.append(title1.value)
    # Encapsulate the task header and current sending row data into a dictionary
    dic=dict(zip(title_benci_send,filelist_row))

    #Adjust the row of data to a new list according to the order of "cumulatively sent headers".
    t=[]
    for i in range(len(title_lieji_sended)):
        t.append(dic[title_lieji_sended[i]])
    # Finally, save the sent data of this row to the "cumulative sent" table
    ws.append(t)

# Get the cumulative sent form data as a list
def get_leiji_sended_company_xls(workbook):
    leiji_sended_file_list=[]
    # Read customer information file excel
    ws = workbook['Cumulative sent']

    # Read company information from the first line
    for row_tuple in ws.values:
        file_list_row=list(row_tuple)
        leiji_sended_file_list.append(file_list_row)

    company_index = leiji_sended_file_list[0].index("Company Name")
    sended_file_array = np.array(leiji_sended_file_list)
    sended_company = sended_file_array[:, company_index]

    return sended_company

def getemail(file_list,i):
    biaotou=file_list[0]
    email_list=[]
    if file_list[i][biaotou.index('Mail')] == '-' and file_list[i][biaotou.index('More mailboxes')] != '-':
        to_email_list=file_list[i][biaotou.index('More mailboxes')].split('；')
    if file_list[i][biaotou.index('Mail')] != '-' and file_list[i][biaotou.index('More mailboxes')] == '-':
        to_email_list = file_list[i][biaotou.index('Mail')].split('；')
    if file_list[i][biaotou.index('Mail')] != '-' and file_list[i][biaotou.index('More mailboxes')] != '-':
        email_liststr=file_list[i][biaotou.index('Mail')]+'；'+file_list[i][biaotou.index('More mailboxes')]
        to_email_list=email_liststr.split('；')
    if file_list[i][biaotou.index('Mail')] == '-' and file_list[i][biaotou.index('More mailboxes')] == '-':
        to_email_list=[]

    area_company = str(file_list[i][biaotou.index('type of enterprise')])
    company = str(file_list[i][biaotou.index('Company Name')])

    return company, area_company, to_email_list


# Be sure to maximize the number of effective lines. Originally there was content, but after deleting the line content, openpyxl will treat these lines as valid lines (lines with content). So don't just delete the content, just delete the line directly.
def load_customer_xls(workbook):
    file_list=[]
    # Read customer information file excel
    ws = workbook['Summary of this task']

    # Read company information from the first line
    for row_tuple in ws.values:
        file_list_row=list(row_tuple)
        file_list.append(file_list_row)
    return file_list

def login_mail_server(smtpHost, sslPort, username, password):
    # Connect login email
    try:

        # #Log in to the mailbox in a normal way, using the intranet
        # print('create smtp object')
        # smtp = smtplib.SMTP()
        # smtp.connect(smtpHost)
        # smtp.login(username, password)
        # print('Successful login server')

        # Pure ssl encryption, communication process encryption, email data security--login mailbox
        smtp = smtplib.SMTP_SSL(smtpHost, sslPort)
        smtp.ehlo()
        smtp.login(username, password)
        print(username+'Log in to the server successfully')
    except Exception as e:
        print(username+'Login failed: '+str(e))
        return None

    return smtp


# Calculate send failure rate
def fail_rate(sta_list,jishu):
    s=0;
    if len(sta_list)<=jishu:
        for i in range(0, len(sta_list)):
            s = s + sta_list[i]
        return s/jishu
    for i in range(0-jishu,0):
        s=s+sta_list[i]
    return s / jishu



if __name__ == '__main__':
    #Personal information
    persons = [
        {'mailtype': 'qq', 'username': 'XXX@qq.com', 'password': 'XXXb', 'sslPort': "465"},
        {'mailtype': '163', 'username': 'XXX@163.com', 'password': 'XXX', 'sslPort': "465"},
 

    ]

    # Attachment address and attachment name when sending
    attachment_path='E:\wps Shared folder\Productivity Improvement Code\python Automatically send mail items\UAV Industry Solutions.pdf'

    #Add personal information port number
    for person in persons:
        if person['mailtype'] == 'qq':
            person['sslPort'] = '465'
        if person['mailtype'] == '163':
            person['sslPort'] = '465'
        if person['mailtype'] == 'nei':
            person['sslPort'] = '465'

    target_info_file = 'youxiang.xlsx'
    wb = load_workbook(target_info_file)


    # Load customer information to list file_list
    file_list = load_customer_xls(wb)
    # Attachments are not sent by default
    have_attachment= False
    # Pointer, record the location currently sent to, counting from zero
    staus_index = 0

    # Failed to store mailboxes
    fail_sended=[file_list[0]]

    try:
        for i in range(len(persons)):  # Cycle through different mailboxes
            failTimes = 0

            result, staus_index = main(wb, file_list, attachment_path, persons[i], have_attachment)

            # Sent
            if result == 1:
                # sys.exit()
                break
            # If the sending fails and 10 consecutive companies fail to send, send it to another mailbox
            if result == -1:
                print(
                    'from the ' + str(staus_index + 1) + ' companies continue to send===========change another mailbox======================')
        if (i + 1) == len(persons):
            print('mailbox has run out')
    except Exception as e:
        # The program execution ends abnormally, saves the data, and exits.
       print("Abnormal program execution"+str(e))




    # After the normal execution of the program ends, save the data and exit.
    print("Finally, save the data to the cumulative sent")
    wb.save(target_info_file)
    # Store the sent companies in this task has been sent

epilogue

At present, the project is still in use, and it is constantly optimized and updated. In the future, the project will be packaged into a software, and the operation interface will be added to make it more versatile and more suitable for the general public. Friends who are interested in this project are welcome to contact me and optimize this project with me.