Making Dynamic Subject Titles & Dates Using Python and Excel-CodePudding

I'm trying to automate sending some emails, but I need the subject to be dynamic based on a cell reference in Excel (For purposes of representation, let's just say that the file path is Document\Email_Excel, the tab is called "Overview" and the cell where I make the DYNAMIC NAME changes is A1, while DYNAMIC DATE is B2. Below is the Python code I'm using to execute the email:

from email import message
import win32com.client as client
outlook = client.dispatch("Outlook.Application")
message = outlook.CreateItem(0)
message.Display()
message.To = "EMAIL LIST"
message.Subject = "DYNAMIC NAME, DYNAMIC DATE Data"
html_body = """
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Document</title>
</head>
<body>
<p>Hello,</p>
<p>Could you please send over the DYNAMIC DATE information?</p>
<p>If you already sent over the information required, please disregard this message.</p>
<p>Thank you.</p>
</body>
</html>
"""
message.HTMLBody = html_body

I am new to python so please let me know if I can provide any more information.

Thank you.

CodePudding user response：

One way to do this is using openpyxl. You can append the following to the code in your question:

from openpyxl import load_workbook
wb = load_workbook(filename = 'Document\Email_Excel.xlsx')
ws = wb['Overview']
dynamicName, dynamicDate = str(ws['A1'].value), str(ws['B2'].value)

message.Subject = message.Subject.replace('DYNAMIC NAME', dynamicName).replace('DYNAMIC DATE', dynamicDate)
message.HTMLBody = message.HTMLBody.replace('DYNAMIC NAME', dynamicName).replace('DYNAMIC DATE', dynamicDate)