Python automated office: automatic identification of early warning information and automatic mass mailboxing, bidding farewell to manual cumbersomeness

In the fast-paced modern workplace, project delays are one of the inevitable challenges. When a project cannot be completed on time, it is critical to notify the relevant parties in a timely manner and explain why. However, manually sending a large number of extension notification emails is not only inefficient, but also error-prone. Luckily, Python provides a solution.

As a powerful programming language, Python plays a pivotal role in the field of automated office. With Python, we can write scripts that automate some repetitive and tedious tasks, thus improving work efficiency. In the case of project delays, we can use Python to write a program that automates mass mailing and easily solves the notification problem.

Recently, after receiving the demand of a foreign trade company, the batch order delay early warning, when the actual production progress is delayed in the planned schedule, the order number, customer, delay information and other information will be automatically sent to the relevant person in charge.

First of all, I used Python email sending libraries, such as smtplib and email, to build the email sending function. By configuring parameters such as SMTP server, sender mailbox, and recipient mailbox, we can automate the sending of emails. At the same time, we can also use Python template engines, such as Jinja2, to dynamically generate email content, so that each email can be personalized according to the specific situation of the project.

Next, I combine information about order delays into a single data source, such as an Excel sheet or a database. Through Python's data processing libraries, such as pandas, etc., we can easily read and process this data. Then, we can write a loop to iterate through each row of data in the data source, dynamically generate the message content and send it based on the recipient's mailbox and project delay information in each row of data.

Finally, we can package the entire program into an executable file or script and integrate it into the company's office automation system. When the project is delayed, you only need to run the program to automatically complete the mass mailing work, which greatly reduces the burden of manual operation.

Core code

import pandas as pd
import datetime
import smtplib
# 处理邮件内容的库，email.mine
from email.mime.text import MIMEText
import logging
import os




logging.basicConfig(filename='E:\code\Plan_m\log.log', level=logging.INFO,filemode = 'a', format = '【%(asctime)s】 【%(levelname)s】 >>>  %(message)s', datefmt = '%Y-%m-%d %H:%M')




# 邮箱属性配置
def send_email(text_msg): 
    # 邮箱服务端
    mailserver = 'smtp.163.com'
    # 发件人-填写自己的邮箱
    userName_SendMail = '[email protected]'
    # 邮箱发件授权码-为发件人生成的授权码，详见下文
    userName_AuthCode = 'BOEFGGZHRAHEXGRN'
    # 定义邮件的接收者-我随便写的，若收件人较多，可用列表表示
    per_email = email_dict.get(text_msg.get("跟进人","None"),"None")
    print(per_email)
    received_mail = ['[email protected]'] # 加入祖总 刘总 张总邮箱
    # final_received_mail = received_mail+per_email
    # print(final_received_mail)
    # 发送一封简单的邮件，处理邮件内容
    content = f'你有一个工厂订单即将预期，请及时处理。\n 工厂：{text_msg.get("工厂",None)},客户：{text_msg.get("客户",None)},样品单号：{text_msg.get("样品单号",None)},到期时间：{text_msg.get("计划完成时间",None)},样品明细：{text_msg.get("样品明细",None)},责任人：{text_msg.get("跟进人",None)}'
    print(content)
    # 纯文本形式的邮件内容的定义，通过MIMEText进行操作，plain为默认的文本的展示形式
    email = MIMEText(content, 'plain', 'utf-8')
    email['Subject'] = '即将延期的批量样品单提醒'  # 定义邮件主题
    email['From'] = "通知机器人"  # 发件人
    email['To'] = ','.join(received_mail)  # 收件人（可以添加多个，若只有一个收件人，可直接写邮箱号）
    
    
    # 发送邮件
    
    # QQ邮箱的端口号是465，其他邮箱的端口号可自行百度，非QQ邮箱，一般使用SMTP即可，不需要有SSL
    smtp = smtplib.SMTP_SSL(mailserver, port=465)
    smtp.login(userName_SendMail, userName_AuthCode)
    smtp.sendmail(userName_SendMail, ','.join(received_mail), email.as_string())
    
    smtp.quit()
    logging.info('恭喜，邮件发送成功了')




today = datetime.date.today()
data_dir = 'E:\code\Plan_m\批量样品跟进'
for excl in os.listdir(data_dir):
    try:
        excl_path = os.path.join(data_dir,excl)
        data = pd.read_excel(excl_path)
        data['计划完成时间'] = pd.to_datetime(data['计划完成时间'])
        for i,row in data.iterrows():
            try:
                diff_day = (row['计划完成时间']-pd.Timestamp(today)).days
                # print(dict(row))
                logging.info(diff_day)
                if diff_day<=3:
                    send_email(dict(row))
                else:
                    logging.info("未延期")
            except Exception as e:
                logging.info(e)
                continue
                
    except Exception as e:
        logging.info(excl_path)
        logging.info(e)
        continue
# data = pd.read_excel("E:\Plan_m\批量样品跟进计划表.xlsx")
# # data['下单时间'] = pd.to_datetime(data['下单时间'])           

163 mailbox security code setting [the password in the python code uses this instead of the email password]

The result of the code run