Python-based automation of Outlook using data from an Excel file for the mail body and adding a signature

user_19716032 0
Viewed 54

I'm trying to write a Python script code wherein I’ll send email notifications to my team members on a daily basis.

There are two excel sheets, let's say abc.xlsx and def.xlsx.

I already have a script that updates these files and saves them. (These files abc and def are deleted and recreated with the same name but with updated information.)

Now my goal is to attach the file abc as an attachment in the mail and add the contents of def.xlsx in the email body.

I’m trying to achieve this:

Hello All,
Please find the pending lists here as follows:

///The info from def.xlsx sheet comes here///

Thanks and regards!

/// my outlook signature///

Here is my code:

import win32com.client as win32
import pandas as pd

# reading a file, which needs to be on mail body
df1 = pd.read_excel('def.xlsx')

html_table = df1.to_html(index=False)

outlook = win32.gencache.EnsureDispatch('Outlook.Application')
mail = outlook.CreateItem(0)
mail.To = 'mail@me.com'
mail.CC = 'mail@me.com'
mail.Subject = 'Test mail'

# path to signature should be User\AppData\Roaming\Microsoft\Signatures\signature.htm
pathToIMage = r'path_to_my_signature'
attachment = mail.Attachments.Add(pathToIMage)
attachment.PropertyAccessor.SetProperty("http://schemas.microsoft.com/mapi/proptag/0x3712001F", "MyId1")

#  modify the mail body as per need
mail.Attachments.Add(Source="C:\..abc.xlsx")
body = "<p>Hi All, Please find the updates pending updates below:" + html_table + " <br>Thanks and regards <p> <figure><img src=""cid:MyId1""</figure>"
mail.HTMLBody = (body)
mail.Send()

Example: This type of output I'm expecting

Challenges:

  1. My signature will be a corrupted image with a "x" in it in the test email.
  2. My Excel sheet, which has to be on the body, won't have the same format.

I’ve copied all the codes from Stack overflow only. I did some of my research, but I'm not getting the expected output.

html-email outlook excel email python
2 Answers

First, you may try setting the BodyFormat property before setting up the HTMLBody property.

Second, to get the signature added to the message body you need to call the Display method before setting up the HTMLBody property.

Third, the <figure> element is not supported in Outlook because Word is used as an email editor and applies its own business rules to message bodies.

Fourth, the HTMLBody property returns or sets a string which represents the message body, it is expected to get or set a full-fledged well-formed HTML document. Try to set up a well-formed HTML document and then set up a property.

If you need to preserve formatting from Excel you may copy the table to the clipboard and then paste it using the Word object model.

Be aware, The Outlook object model supports three main ways of customizing the message body:

  1. The Body property returns or sets a string representing the clear-text body of the Outlook item.
  2. The HTMLBody property of the MailItem class returns or sets a string representing the HTML body of the specified item. Setting the HTMLBody property will always update the Body property immediately. For example:
     Sub CreateHTMLMail() 
       'Creates a new e-mail item and modifies its properties. 
       Dim objMail As Outlook.MailItem 
       'Create e-mail item 
       Set objMail = Application.CreateItem(olMailItem) 
       With objMail 
        'Set body format to HTML 
        .BodyFormat = olFormatHTML 
        .HTMLBody = "<HTML><BODY>Enter the message <a href="http://google.com">text</a> here. </BODY></HTML>" 
        .Display 
       End With 
     End Sub
  1. The Word object model can be used for dealing with message bodies. See Chapter 17: Working with Item Bodies for more information.

Note, the MailItem.BodyFormat property allows you to programmatically change the editor that is used for the body of an item.

user_16033510

I modified it. I'm still working on Challenge 2. I'll just go through the documentation that has been recommended and will share my final script.

import win32com.client as win32
import pandas as pd
import os
import codecs

df1 = pd.read_excel('mail_body.xlsx')
html_table = df1.to_html(index=False)

# below is the coding logic for signature
sig_files_path = 'AppData\Roaming\Microsoft\Signatures\\' + 'signature_file_name' + '_files\\'
sig_html_path = 'AppData\Roaming\Microsoft\Signatures\\' + 'signature_file_name' + '.htm'

signature_path = os.path.join((os.environ['USERPROFILE']), sig_files_path)
html_doc = os.path.join((os.environ['USERPROFILE']), sig_html_path)
html_doc = html_doc.replace('\\\\', '\\')

html_file = codecs.open(html_doc, 'r', 'utf-8', errors='ignore')
signature_code = html_file.read()

signature_code = signature_code.replace(('signature_file_name' + '_files/'), signature_path)
html_file.close()

outlook = win32.gencache.EnsureDispatch('Outlook.Application')
mail = outlook.CreateItem(0)
mail.To = 'mail@me.com'
mail.CC = 'mail@me.com'
mail.Subject = 'TEST EMAIL'
mail.Attachments.Add(Source=r"C:\..abc.xlsx")

#  modify the mail body as per need
mail.BodyFormat = 2
body = "<p>Hi All, Please find the updates pending updates below:" + html_table + " <br>Thanks and regards <br><br>"
mail.Display()
mail.HTMLBody = body + signature_code
mail.Send()
user_197160320
Related