Photo by Finding Dan | Dan Grinwis on Unsplash

Automating Outlook Calendar Downloads with Python

(because life’s too short to waste it on manual tasks)

This post will guide you to extract specific attributes of calendar entries stored in Outlook, group them by date and download the result to a .csv file.

My current project requires us to account for what meetings we attended each day, and I use this code to help me prepare my project timesheets. Maybe it helps you too.

I’ll show you how to do the following:

  1. Use win32com.client to access your local outlook calendar
  2. Filter the appointments collection to a specified date range
  3. Extract specific attributes
  4. Group the results by date
  5. Save to .csv file

(TLDR: see the end for the full code list)

I won’t explain how to do the following (there are better sites for that):

  1. Setup Python
  2. Install any of the software

Step 1— The win32com.client and python

First: Fire up a new Jupyter Notebook/Lab session. I prefer Jupyter Lab as you’ll see from my screenshots. Set up the library calls:

Libraries needed in this Notebook

Second: Add the following code to a new cell in your Jupyter Notebook:

# Step 1, block 1 : access Outlook and get events from the calendar
Outlook = win32com.client.Dispatch(“Outlook.Application”)
ns = Outlook.GetNamespace(“MAPI”)
appts = ns.GetDefaultFolder(9).Items
# Step 1, block 2 : sort events by occurrence and include recurring events
appts.Sort(“[Start]”)
appts.IncludeRecurrences = “True”

In the above code:

  1. I set a variable called Outlook to the windows Outlook application, via the win32com.client module (huge shoutout to Mark Hammond). Then we set variable ns to the MAPI namespace. Finally we fetch the calendar Outlook folder of the user who is logged on and assign a reference to the variable appts.
  2. I then sort the appointments folder by Start date, and set the flag to include recurring events.

Step 2— Filter appointments to a date range

Add the following code to a new cell in your Jupyter Notebook:

# Step 2, block 1 : filter to the range: from = (today — 10), to = (today)
end = date.today().strftime(“%m/%d/%Y”)
begin = date.today() — datetime.timedelta(days=10)
begin = begin.strftime(“%m/%d/%Y”)
appts = appts.Restrict(“[Start] >= ‘“ +begin+ “‘ AND [END] <= ‘“ +end+ “‘“)

In the above code:

  1. I set the end variable to today’s date in the format dd/mm/YYYY. Then assign the begin variable to a date offset by 10 days prior from end, and format that in the same way.

Step 3 — Extract specific attributes

Add the following code to a new cell in your Jupyter Notebook:

# Step 3, block 1 create list of excluded meeting subjects
excluded_subjects=(‘<first excluded subject>’, ‘<second excluded subject>’,
‘<third excluded subject>’, ‘<etc … >’)
# Step 3, block 2 : populate dictionary of meetings
apptDict = {}
item = 0
for indx, a in enumerate(appts):
subject = str(a.Subject)
if subject in (excluded_subjects):
continue
else:
organizer = str(a.Organizer)
meetingDate = str(a.Start)
date = parse(meetingDate).date()
subject = str(a.Subject)
duration = str(a.duration)
apptDict[item] = {“Duration”: duration, “Organizer”: organizer, “Subject”: subject, “Date”: date.strftime(“%m/%d/%Y”)}
item = item + 1

In the above code:

  1. I set the excluded_subjects variable to the list of subjects that I want to exclude (not all meetings are billable to this project).
  2. Then I iterate over the collection of appointments and build a dictionary of appointments with just the attributes I need. These are the ‘Duration’ of the meeting (to bill my time), the ‘Organizer’ of the meeting, the ‘Subject’, and the ‘Date’. I arrange those fields in that order and add them to the dictionary using an index ‘key’

Step 4 — Group the results by date

Add the following code to a new cell in your Jupyter Notebook:

# Step 4, block 1 : convert discretionary to dataframe and group_by Date
apt_df = pd.DataFrame.from_dict(apptDict, orient=’index’, columns = [‘Duration’, ‘Organizer’, ‘Subject’, ‘Date’])
apt_df = apt_df.set_index(‘Date’)
apt_df[‘Meetings’] = apt_df[[‘Duration’, ‘Organizer’, ‘Subject’]].agg(‘ | ‘.join, axis=1)
grouped_apt_df = apt_df.groupby(‘Date’).agg({‘Meetings’:’, ‘.join})
grouped_apt_df.index = pd.to_datetime(grouped_apt_df.index)
grouped_apt_df.sort_index()

In the above code:

  1. I convert the dictionary to a pandas DataFrame. Set the ‘Date’ as the index. I then concatenate the ‘Duration’, ‘Organizer’ and ‘Subject’ fields into a new field: ‘Meeting’. Then I use ‘.group_by’ on ‘Date’ to create a new DataFrame and join all the meetings for each date together in the ‘Meetings’ field. Finally I convert the index to a datetime format to ensure good sorting and then I sort.

Step 5 — Save to .csv file

Add the following code to a new cell in your Jupyter Notebook:

# Step 5, block 1: add timestamp to filename and save
filename = date.today().strftime(“%Y%m%d”) + ‘_10day_meeting_list.csv’
grouped_apt_df.to_csv (filename, index = True, header=True)

In the above code:

  1. I build a filename variable using the current date and some text. I then save the DataFrame to .csv.

That’s it. In this post you saw how to use the win32com.client to access the Outlook application, extract elements of the appointments in the calendar collection and save those to a DataFrame. Finally we saved our output to a .csv file.

I hope this was useful to you.

BR, Andrew.

PS I also automate the collection and categorisation of Jira Activity Stream entries with Python … but that’s another story.

Here is the full codeset of my Jupyter Lab Notebook:

--

--

--

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Become a PRO on How to Use SAGE 50 With This Comprehensive Video Training.

Everything you should know about Python String

READ/DOWNLOAD%$ Engineering Fundamentals: An Intro

Let’s Get Technical: User Stories

Learn Kubernetes step by step using LKE

Asynchronous Programming in Dart.

Basic Math Operators in Python

Bitwise Operators C++

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Andrew Jabbitt

Andrew Jabbitt

More from Medium

“ python Operators”

COLLECTION IN PYTHON

Introduction to Matplotlib: A library for Visualisation in Python

PYTHON SQLITE3