Py automated office—Word document replacement, Excel table reading, Pdf file generation and Email automatic email sending practical cases

Imagine that now you have a Word invitation template, and then you have a customer list with basic information such as the customer's name, contact information, email address, etc., and then your boss now needs to replace the names in the invitation template, Then generate the Word invitation letter template into Pdf format, then edit the unified invitation words (email body), and then send the invitation letter attachment to the customer's mailbox in sequence. What would you do? Under normal circumstances, we must copy and paste the customer name in the Excel table, and then replace it one by one in Word documents, then convert Word to Pdf format, and then copy the email address in the Excel table to send the edited email. It is normal, and then attach Attach the invitation letter and click send. Do some calculations. If the process is fast and the passion is high, it will take about 1 minute or more. It’s okay if there are only a few dozen customers, and it can be done in an hour. But what if there are hundreds, thousands, or even tens of thousands of customers? I would probably faint in the office crying.

But don’t panic, Python automates office, a set of combos, uses Python to automate office - Word document replacement, Excel table reading, Pdf file generation and Email automatic mail Send one-stop service arrangement, let’s take a look below!

Implementation process

1) Replace the Word template to generate the corresponding invitation letter

Here we take the above Word template as an example and write a function to replace 8a11bc632ea32a57b3e3693c7987c420 in the template with the customer's name, all in one step.

def get_invitation(name):
    doc = docx.Document("template.docx")
    for para in doc.paragraphs:
        if &#39;<name>&#39; in para.text:
            for run in para.runs:
                if &#39;<name>&#39; in run.text:
                    run.text = run.text.replace(&#39;<name>&#39;, name)
        doc.save(f&#39;./邀请函/{name}.docx&#39;)

The above code needs to understand the structure of the Word document. A document has multiple paragraphs, which are obtained using doc.paragraphs; the text in the paragraph is obtained using para.text; there may be multiple different styles in a paragraph. Text, these different styles are called runs. A paragraph contains multiple runs, which can be obtained using para.runs. The specific text in a run can be obtained using run.text. Now that you understand this, look at the above code. Is it much clearer?

2) Convert Word invitation letter to Pdf format

This is much simpler. In Python automated office, it can be achieved with one line of code , and the speed is very fast.

from docx2pdf import convert
convert(f"./邀请函/{name}.docx")

Use the convert() function to convert a file in docx format into a Pdf document with the same name.

3) Read the name and email in the Excel table

You need to use the openpyxl library here. Of course, there are still many libraries about Excel. , here we take this library as an example, the code is as follows:

def get_username_email():
    workbook = openpyxl.load_workbook("names.xlsx")
    worksheet = workbook.active
    for index, row in enumerate(worksheet.rows):
        if index > 0:
            name = row[0].value  # 获取表格第一列的姓名
            email = row[3].value  # 获取表格第四列的邮箱
            # print(name, email)
            # print(f"{name}邀请函正在生成...")
            # get_invitation(name)
            send_email(name, email)

上面的代码，理解起来应该并不难，读取Excel中的姓名和邮箱，之后传到get_invitation()生成邀请函，之后传给send_email()函数中自动发送邮件。实际上，这两部是分开进行的，这里是先执行get_invitation()函数，先生成邀请函，之后再将该函数注释掉，再执行发送邮件函数，

4）自动发送邮件

关于自动发送邮件，历史文章中也曾经发布过好几篇了，这里继续用上了，一开始我也觉得挺难的，后来发现也没有想的那么复杂，代码如下：

smtp = smtplib.SMTP(host="smtp.qq.com", port=587)
# smtp.login(邮箱, 授权码)
smtp.login('235977@qq.com', "ruybefkipoo")


def send_email(name, email):
    msg = MIMEMultipart()
    msg["subject"] = f"您好，{name}，您的邀请函！"
    msg["from"] = "2352180977@qq.com"
    msg["to"] = email

    html_content = f"""
    <html>
        <body>
                <p>您好：{name}<br>
                    <b>欢迎加入Python进阶者学习交流群，请在附件中查收您的门票~</b><br>
                    点击这里了解更多：<a href="https://www.pdcfighting.com">演唱会主页</a>
                </p>
        </body>
    </html>
    """
    html_part = MIMEText(html_content, "html")
    msg.attach(html_part)
    with open(f"./邀请函/{name}.pdf", "rb") as f:
        doc_part = MIMEApplication(f.read())
        doc_part.add_header("Content-Disposition", "attachment", filename=name)
        # 把附件添加到邮件中
        msg.attach(doc_part)
        # 发送前面准备好的邮件
        smtp.send_message(msg)
        # 如果放到外边登录，这里就不用退出服务器连接，所以注释掉了
        # smtp.quit()

这里需要注意三点，其一是邮箱登录放在了函数外边，防止函数多次调用，短时间多次请求登录邮箱被封禁；其二邮箱登录里边用的是授权码，而不是你的邮箱登录密码，这里使用的是qq邮箱做示例，其他邮箱需要更改smtp服务；其三这个代码里边除了正文中引用了html写法，还携带了Pdf格式的邀请函附件，稍显复杂。关于授权码的获取，这里不再赘述了，之前历史文章页写过，网上的教程页很多，不会的话，私我就行。或者参考下面这个文章：手把手教你使用Python网络爬虫实现邮件定时发送（附源码）。

5）完整代码

以上四个步骤进行拆分了，依次完成了Word文档替换、Excel表格读取、Pdf文件生成和Email自动邮件发送任务，这里附上完整的代码。

import docx
from docx2pdf import convert
import openpyxl
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.mime.application import MIMEApplication


# 生成对应的邀请函，并转存pdf格式
def get_invitation(name):
    doc = docx.Document("template.docx")
    for para in doc.paragraphs:
        if &#39;<name>&#39; in para.text:
            for run in para.runs:
                if &#39;<name>&#39; in run.text:
                    run.text = run.text.replace(&#39;<name>&#39;, name)
        doc.save(f&#39;./邀请函/{name}.docx&#39;)
    convert(f"./邀请函/{name}.docx")


smtp = smtplib.SMTP(host="smtp.qq.com", port=587)
smtp.login('235977@qq.com', "ruybefkipoo")


def send_email(name, email):
    msg = MIMEMultipart()
    msg["subject"] = f"您好，{name}，您的邀请函！"
    msg["from"] = "2352180977@qq.com"
    msg["to"] = email

    html_content = f"""
    
        
                
您好：{name}

                    欢迎加入Python进阶者学习交流群，请在附件中查收您的门票~

                    点击这里了解更多：演唱会主页
                
        
    
    """
    html_part = MIMEText(html_content, "html")
    msg.attach(html_part)
    with open(f"./邀请函/{name}.pdf", "rb") as f:
        doc_part = MIMEApplication(f.read())
        doc_part.add_header("Content-Disposition", "attachment", filename=name)
        # 把附件添加到邮件中
        msg.attach(doc_part)
        # 发送前面准备好的邮件
        smtp.send_message(msg)
        # 如果放到外边登录，这里就不用退出服务器连接，所以注释掉了
        # smtp.quit()


def get_username_email():
    workbook = openpyxl.load_workbook("names.xlsx")
    worksheet = workbook.active
    for index, row in enumerate(worksheet.rows):
        if index > 0:
            name = row[0].value
            email = row[3].value
            # print(name, email)
            # print(f"{name}邀请函正在生成...")
            # get_invitation(name)
            send_email(name, email)


if __name__ == '__main__':
    get_username_email()
    # get_invitation('Python进阶者')

总结

这篇文章基于Python自动化办公，主要介绍了使用Python相关库，依次完成Word文档替换、Excel表格读取、Pdf文件生成和Email自动邮件发送任务。程序运行之后，邀请函会自动生成，然后邮件会自动发送，速度也非常快，给几百个、上千个客户发送邀请函就不害怕了，如果有上万个客户，可能需要借助第三方平台辅助了，毕竟一般的普通邮箱，每日发送邮箱数是有限制的。

人生苦短，我用Python！感谢【麦叔】提供的素材和代码，亲测有效，这里的介绍只是冰山一角，更多内容可以前往《Python办公效率手册》中获取。

需要源数据的小伙伴可以添加我为好友，私我进行获取，或者前往我的github获取：

https://github.com/cassieeric/Python-office-automation

The above is the detailed content of Py automated office—Word document replacement, Excel table reading, Pdf file generation and Email automatic email sending practical cases. For more information, please follow other related articles on the PHP Chinese website!