1. Introduction to this paper

This is a good example of Python automation. For small businesses, it’s ok to manually send emails one by one. It would be a waste of time for us to send paychecks to companies with hundreds, thousands, or even larger numbers. With this in mind, I wrote a program that implements Python’s automatic group payslip functionality.

The original data source is as follows:

​

The final effect is as follows:

​

This must be fun for you, right? So what should an automated program do? And then we look down.

2. Process analysis

In fact, such a code, summed up, there are only the following 5 steps: (1) import related modules; ② Read Excel form; ③ Login mailbox; (4) Prepare the body of the email to be sent; ⑤ Send emails;

1) Import relevant modules

Import yagmail # Import keyring # From openPyXL import load_workbook # Import yagmail # Import keyring # From Datetime import * # The module used to obtain the current system timeCopy the code

2) Read the Excel table

Wb = load_workbook(" XLSX ",data_only=True) sheet = wb. ------------------------------------------------------- for row in sheet: row_text = "" for cell in row: if cell.column == "B": The continue row_text + = f {cell. The value}, "" print (row_text) -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- print (" years: "And the date. Today () year) print (" month", the date today (). The month) print (f "{date. Today () year} - {date. Today (). The month} month")Copy the code

The results are as follows:

​

3) Log in to the mailbox

yagmail.register("[email protected]","flmyucvntuvbjbcb") pwd = keyring.get_password("yagmail","[email protected]") yag =  yagmail.SMTP(user="[email protected]",host="smtp.qq.com",password=pwd)Copy the code

** The first line of code is used to store your email address and password. The second line of code uses keyring to record your email address and password. The third line of code directly logs in to the mailbox.

I wrote an article on how to send emails in Python that explained it in detail. Below is a link to this article for your reference.

**Python email: **suo.im/60ylZ1

4) Prepare the text to be sent

Since we need to send an email with a form in the body of the message, it is necessary for us to understand a little front-end knowledge. Therefore, I finally in a chapter for you to explain.

5) Send an email

Yag.send (f"{email}",f" {date.today().year}-{date.today().month} monthly salary ",contents)Copy the code

** The first argument is the recipient’s email address; The second parameter is the header of the message; The third parameter is what was sent.

3. Special notes on HTML code

As anyone who knows how to send an email in Python knows, the body of an email can be written in HTML code. You may think you can’t do anything, but don’t worry, we don’t need to learn too much front-end code, people who have done crawlers can understand some of it.

1) Write your own simple front-end code

We can create a new HTML file directly using Pycharm, which will show the initial front-end code, and we can add a simple table!

​

The final display in the browser looks like this:

​

You can’t tell this is a table from the image above, that’s because we haven’t set a style for it, so what we need to do is set a style for the table.

​

The final display in the browser looks like this:

​

2) How to write front-end code in Python

As you can see from the figure above, the entire front end consists of a series of tags that come in pairs. Therefore, when writing front-end code in Python, you just need to add the corresponding tag in the corresponding place.

for row in sheet:
    row_text = "<tr>"
    for cell in row:
        if cell.column == "B":
            continue
        row_text += f"<td>{cell.value}</td>"
    row_text += "</tr>"
    print("\n")
    print(row_text)
Copy the code

The results are as follows:

​

4. Complete code

For the integrity of the article, I put my code at the end of the article. But limited to the length of the article, FINALLY I only paste a picture, detailed code, you can go to the end of the article to obtain.

​