Skip to content
Search
Generic filters
Exact matches only

A Sweet hack to Cut Your Work in Half: Automatic Python Execution | by Sruthi Korlakunta | Aug, 2020

Automate your Python execution and email the results with VBA, Powershell, and Task scheduler

Sruthi Korlakunta

Abstract: This article covers how to schedule your regular python scripts. Assuming that you

  1. Get updated excel reports every day/week/month into a shared Network or email
  2. Have a python script that is up and running to processes your reports and deliver insights in the form of images, worksheets, or other forms of data.
  3. Want to run the process over an extended period at regular intervals of time but heavily dread having to run it manually.

There are fun parts of Data Engineering — like programming and analyzing data. Then there are boring parts, like using the already written programs on a regular basis to get the most recent results. Clearly, nobody wants to do the latter. Here is a quick fix I use to avoid running those scripts which need to be executed daily/weekly/monthly so that this time can be put to use better.

Three words — Macro, Batch, and schedule.

  1. I use a macro to save my incoming excel reports to a location as and when they come. (I get it from a place which performs the analysis and sends a report to me manually. No, there is no way around this).
  2. I set up an outlook rule to run the macro only on desired folders along with few other conditions (sender, subject etc).
  3. I write a batch file (.bat), which on clicking executes the script on command prompt.
  4. I schedule the bat file using the Windows Task scheduler to execute the script with the same frequency as the arrival of new excel reports
  5. I write a PowerShell script to email the newest results to all the necessary recipients and then I schedule it to run soon after the bat file executes.

Step 1: Outlook Macros

  1. You can activate VBA scripting in Outlook by activating it in the options pane of outlook.
  2. You will then see that a button for developer tools appears at the top of the window in your outlook homepage.

3. Go to the Developer Tools tab and start-up Visual Basic scripting.

4. Here create a new module and script your code to save incoming attachments.

Easy Peasy.

In the example below, the report name is always the subject of the automated email I get. So I used this criterion to decide the save name of my file and save it to a folder.

(I also make sure to save it with the date so that I know that the destination folder always has the most recent file before executing my script.)

VBA script to save attachments on incoming email:

Public Sub SaveRegularReports(MItem As Outlook.MailItem)
Dim oAttachment As Outlook.Attachment
Dim sSaveFolder As String
sSaveFolder = "/You/want/to/save/here/"
If MItem.Subject = "Quarterly_rep" Then
savename = "QRep"
ElseIf MItem.Subject = "JustSomeRegRep" Then
savename = "RegRep"
ElseIf MItem.Subject = "WeeklyReport" Then
savename = "WeeklyUpdate"
End If
For Each oAttachment In MItem.Attachments
oAttachment.SaveAsFile sSaveFolder & savename & Format(MItem.ReceivedTime, "mmddyyyy") & ".xlsx"
Next
End Sub

Now, all I have to do is decide whom to apply these rules to. So I go to rules and set up the conditions I want to apply the script to.

In my example, I have the conditions that the email always comes from one specific address with a subject containing the name of the report. (I moved all such regular reports to a separate folder in the inbox for safety)

In the next step, As an action, I execute the above script SaveRegularReports whenever this folder gets a newbie.

Voila! Autosaved everything I need without lifting a finger!!

Step 2. Write a bat file to execute your python script on saved excel sheets

This is also a piece of cake. A bat (or batch) file is a set of commands that get executed by the command prompt when you click it. If you ever wrote bash commands or used the windows command prompt shell scripts to “pip install” and the like, you know what I’m talking about.

  1. Open up a text file and write commands similar to those below to execute your python script, as you would on the command prompt. (I am using Anaconda, So I first activate the anaconda Prompt before executing my python script).
  2. Save this file as filename.bat
@echo offcall C:UsersmyUSerAppDataLocalanacondaScriptsactivate.batC:ThisisWhereYourScriptLivesScriptchen.pypause

When I click on the above bat file (scriptchens_bat.bat), my python script gets executed. It uses the saved excel reports from step 1.

Step 3. Task-Schedule your script execution

Note: I know I receive my reports every week, say on Friday at 10 AM. I set this script to run every Friday at 10:30.

  1. Open up the Task scheduler from the search bar in Windows. Go to Create Basic Task.
  2. After naming and describing it in the appearing window, go to trigger pane and set up your schedule. In this example to 10:30, Fridays.

Now comes the meaty part. Go to the Actions pane. Here is where you set your bat file up for execution. Select “Start Program” from actions, and in the program pane, give the path to your .bat file you wrote in step 2.

click ok and confirm.

Voila! you will now have output of your Python script every week without lifting a finger!

Step 4. Emailing the Results

If you are happy with just having the most updated results, you can stop here. But if you want to go the extra mile and also automate the distribution of your processing results, grab a coffee and read through to the last step.

I decided to use powershell to email my results with outlook to other coworkers. Here is how:

  1. Open up PowerShell ISE from windows search.
  2. The following script is an example for my case, which is to mail the results separately to all recipients with a body and a subject line. You can modify your script to suit your needs.
$OL = New-Object -ComObject outlook.applicationStart-Sleep 5$recipients = @(“[email protected]”,”[email protected]”,”[email protected]”)foreach ($recipient in $recipients){$mItem = $OL.CreateItem(“olMailItem”)$mItem.To = $recipient$mItem.Subject = “Automatic Email: Weekly Report”$mItem.Body = “This is an automated email sent using powershell. In case of any questions please contact Me. Dieses Dokument enthält… “$mItem.Attachments.Add(“Thisiswheremypythonoutputlivesfile_to_email.xlsx”)$mItem.Send()}

Step 5. Set up Task Scheduler to email your results to the recipients

Similar to step 3, you will now schedule your powershell script to execute every week soon after your python script is executed.

  1. Go to New Task in your task scheduler
  2. Name and describe your task
  3. Go To trigger and set up your schedule
  4. Go to action. In the program/script bar, enter the address to your PowerShell application. It is usually here:
C:WindowsSystem32WindowsPowerShellv1.0powershell.exe

In the argument bar, enter the path to your .ps1 script written earlier.

Voila! You sent an email without lifting a finger!

So to summarize, you automated an entire chain of events starting from receiving an excel file, saving it to a location, processing it with python and sending the results in an email. This is a pretty neat hack that saves me a bunch of time at work. Hopefully, it helps you all too!