Log Book —Guide to Excel & Outlook email Delivery Automation via PythonThis article is divided into 2 parts, first part deals with the generation of image/PDF from an excel and next part attaching the same in an outlook email and sending it out.
It also has an added bonus on macros and handling different mail boxes.
Dip Ranjan ChatterjeeBlockedUnblockFollowFollowingJun 30In our day to day activities we often come across tasks which involve sending out a huge volume of mailers, this project is an outcome of such an ask.
To begin with we will note the steps which are required for this automation process and then try to automate each of those steps.
The steps required for this automation are as follows:1.
Generate image/pdf from an excel.
Create an email with the image/pdf as an attachment.
Send out the emails from outlook.
Well this looks simple enough, so lets start.
Just try to focus on the code here as of now, I will attach a GitHub gist at the end.
Step 1 — image/PDF creationWe have an excel containing the data of all the battles of GOT and a simple graph showing the count of battles by region.
In this step our goal is to create a PDF/image of the graph below from the excel.
You can download the excel from here.
Graph which we will copy as imageFirst we will connect to the Excel application and load our worksheet, Python has many options for natively creating common Microsoft Office file types including Excel, Word and PowerPoint.
In some cases, however, it may be too difficult to use the pure python approach to solve a problem.
Fortunately, python has the “Python for Windows Extensions” package known as pywin32 that allows us to easily access Window’s Component Object Model (COM) and control Microsoft applications via python.
xlApp = win32.
Application')wb = xlApp.
Open(file_location + filename)ws = wb.
Worksheets('Graph') # name of the sheet in which graph is present.
Once that is done we will give the cell reference of the portion of the sheet which we want to convert to an image.
The image capabilities are provided by the PIL package.
The below code copies the cell reference as an image to clipboard and saves it.
You can adjust the image quality using the ‘quality’ parameter and note than the image quality is only applicable for files of JPEG type.
The default image quality is set at 75 and as you increase or decrease the value so will the size of the image change.
You can refer to the official documentation of the PIL library for details.
win32c = win32.
xlBitmap) # give the cells for which you need the imageimg = ImageGrab.
save(file_location + 'Graph.
jpeg',quality=55) # image quality and file size is directly linkedTill this we have saved our image.
Now if you want to convert the image to a pdf you can use the img2pdf library.
The below code reads the image which we have saved above and saves it as a PDF.
You can skip this if all you need is an image.
#storing pdf path pdf_path = file_location + "Graph.
pdf"#opening image image = Image.
open(image_path)#converting into chunks using img2pdf pdf_bytes = img2pdf.
filename)#opening or creating pdf file file = open(pdf_path, "wb")#writing pdf files with chunks file.
write(pdf_bytes)#closing image file image.
close()#closing pdf file file.
close()After completing this step we have an image and a PDF:Image & PDF createdWe are ideally done with our first step, but I would like to add a small bonus.
Often instead of an regular excel we have a macro based one and the need is to run the macro to refresh the data before taking the image, that too is possible.
All you need to know is the name of the macro to run.
The below code will open the excel run the specified macro and save the excel.
xlApp = win32.
Application')wb = xlApp.
Open(file_location + filename)# Give the proper macro namexlApp.
Quit()Step 2— Create an emailWe will use the same pywin32 package as excel to connect to Outlook.
outlook = win32.
application')Here is another bonus.
There is often a need to send the mail from a specific outlook mail box when there are multiple email ids configured, we will deal with that next.
If you want to send your mail from the default mailbox you can skip this step.
The below code will loop through all your configured accounts and when it finds ‘abc@mail.
com’ it will send the mail from there.
However note that if it does not get the specified mail id it will send the mail from the default id only.
sendfromAC = Nonefor oacc in outlook.
Accounts:#give the mail id from which you want to send if oacc.
SmtpAddress == "abc@mail.
com": sendfromAC = oacc break##—————————————————————-mail = outlook.
CreateItem(0)if sendfromAC: mail.
Invoke(*(64209, 0, 8, 0, sendfromAC))##—————————————————————-Our outlook object is created now we will add the To/CC/Subject and the attachments.
For the attachment part I will show 2 things, attach the PDF in the mail as a file and embed the image in the mail body.
This should cover 2 use cases.
To = 'abc@mail.
Cc = 'abc@mail.
Subject = 'Demo Outlook Automation mail'Attach the PDF:mail.
Add(file_location + "Graph.
pdf")Embed in mail body:For embedding an image in the email body we first need to attach it as a normal attachment and then embed it as a part of the HTML mail body.
The below part deals with attaching the image and setting the properties so that outlook can handle the image properly.
We will come shortly to the embedding part.
attachment1 = mail.
Add(file_location + 'Graph.
HTMLBody = "<HTML lang='en' xmlns='http://www.
org/1999/xhtml' xmlns:o='urn:schemas-microsoft-com:office:office'> " + "<head>" + "<!–[if gte mso 9]><xml> <o:OfficeDocumentSettings> <o:Allowjpeg/> <o:PixelsPerInch>96</o:PixelsPerInch> </o:OfficeDocumentSettings> </xml> <![endif]–>" + "</head>" + "<BODY>"Next we have to create the email body.
You can set the email body as normal text format or as an HTML format.
I personally prefer the HTML body format as it gives me a lot of control as to how the email should be displayed instead of letting Outlook be the judge.
The below code is pretty obvious, it will set the mail body and the last ‘cid:Graph’ part will embed the attached image in the mail body.
HTMLBody = mail.
HTMLBody + "<BR>Hello,<b> </b>" + "<BR><BR> Refer to the image below: </b> .
" + "<html><body><img src='cid:Graph'></body></html>"Step 3 — Send the mailNow that we have the mail object ready all that remains is actually sending out the mail.
This will be done using the simple command:mail.
Send()And hurrah!!Sent MailThe full GitHub gist is added below:Known IssuesIssue 1Very rarely there will be a issue in which the the script will not be able to connect to excel.
The error goes something like:module 'win32com.
00020813-0000-0000-C000-000000000046x0x1x9' has no attribute 'CLSIDToClassMap'The solution is derived from this StackOverflow URLThe steps are:1.
Delete the entire gen_py folder, in my case it was present in this path: C:Users'user_name'AppDataLocalTempgen_py folder2.
Rerun the script, the above caches will be regeneratedResolving step 2 often results in Issue 2, which has to be resolved in turn.
Issue 2This issue is more common than Issue 1, after running quite some days, the code will throw an error:File "C:/Users/'user_name'/PycharmProjects/Mailer/mailDispatcher.
py", line 64, in MailDispatcher ws.
xlBitmap) File "C:Users'user_name'PycharmProjectsmailvenvlibsite-packageswin32comclient__init__.
py", line 178, in __getattr__ raise AttributeError(a) in the xlApp = win32.
The solution was arrived at a sort of hit and try way.
The steps are:1.
Replace xlApp = win32.
Application') with xlApp = win32.
Application') [This is already added in code.
Run the code for once, this will result in a mail with corrupted attachments.
Undo changes of Step 1.
Run the code again, now everything should work.
I don't really know what causes these issues, extensive Googling has yielded nothing of value.
It looks like some kind of cache corruption.
html· Too many Stack-overflow questions.