Python PyWin Attach an Excel file to Outlook
Attach an Excel file to Outlook
Another simple scenario where COM is helpful is when you want to attach a file to an email and send to a distribution list. This example shows how to do some data manipulation, open up a Outlook email, attach a file and leave it open for additional text before sending.
Here%u2019s the full example:
import win32com.client as win32
import pandas as pd
from pathlib import Path
from datetime import date
to_email = """
Lincoln, Abraham <honest_abe@example.com>; chris@example.com
"""
cc_email = """
Franklin, Benjamin <benny@example.com>
"""
# Read in the remote data file
df = pd.read_csv("https://github.com/chris1610/pbpython/blob/master/data/sample-sales-tax.csv?raw=True")
# Define the full path for the output file
out_file = Path.cwd() / "tax_summary.xlsx"
# Do some summary calcs
# In the real world, this would likely be much more involved
df_summary = df.groupby('category')['ext price', 'Tax amount'].sum()
# Save the file as Excel
df_summary.to_excel(out_file)
# Open up an outlook email
outlook = win32.gencache.EnsureDispatch('Outlook.Application')
new_mail = outlook.CreateItem(0)
# Label the subject
new_mail.Subject = "{:%m/%d} Report Update".format(date.today())
# Add the to and cc list
new_mail.To = to_email
new_mail.CC = cc_email
# Attach the file
attachment1 = out_file
# The file needs to be a string not a path object
new_mail.Attachments.Add(Source=str(attachment1))
# Display the email
new_mail.Display(True)
This example gets a little more involved but the basic concepts are the same. We need to create our object (Outlook in this case) and create a new email. One of the challenging aspects of working with COM is that there is not a very consistent API. It is not intuitive that you create an email like this: new_mail = outlook.CreateItem(0) It generally takes a little searching to figure out the exact API for the specific problem. Google and stackoverflow are your friends.
Once the email object is created, you can add the recipient and CC list as well as attach the file. When it is all said and done, it looks like this:
The email is open and you can add additional information and send it. In this example, I chose not to close out Outlook and let python handle those details.
Comments