Beginning Outlook Email Analysis

June 12, 2020

[2020-06-24 Wed 22:36] This was originally titled “Outlook email analysis” but on reflection it sounded to presumptious for what is actually shown hence the new “beginning” addition/

Its been a productive week and this morning i finally got around to trying my hand at using python to conduct some simple Outlook inbox analysis. I’ve wanted for a long time to have more data visualisation and so another journey begins. The following piece of code is simple and needs improvement but what it does is to connect to the outlook mailbox, and loop through all emails to provide a count of emails received against a persons name for today and it creates an org table output

2020-06-12
man, Bat	2
Darwin, Charles	7
Dent, Arthur	3
Morgan, Arthur	1
Pratchett, Terry	1

The code is

import win32com.client
from collections import defaultdict
from datetime import datetime, date
import datetime

f = open("test.org", 'a')

# setup connection to outlook
outlook = win32com.client.Dispatch("Outlook.Application").GetNamespace("MAPI")
inbox = outlook.GetDefaultFolder(6)

# get inbox messages
messages = inbox.Items
message = inbox.Items.GetFirst()

# define yesterday's date & create dictionary
d = (date.today() - datetime.timedelta (days=1)).strftime('%Y-%m-%d')
counts = defaultdict(int)

# loop through all messages and check if yesterday < received time then update dictionary
for message in inbox.Items:
    received_time = message.ReceivedTime.strftime('%Y-%m-%d')
    if d < received_time:
        counts[message.SenderName] +=1

f.write('%s%s%s%s' % ('| ', date.today().strftime('%Y-%m-%d') , ' |', '\n'))
for key in (counts):
    print('|', key, '|', counts[key], '|')
    f.write('%s%s%s%s%s%s' % ('|', key, '|', counts[key], '|', '\n'))


f.close()

Next tasks are to have it count all yesterday’s msgs, set the script to run first thing every morning or perhaps have it run once a week and compile stats for the week so that there is not too much data.

[2020-06-13 Sat 10:33] now updated for weekly analysis and corrected the misuse of importing datetime as shown in the above example, but also started to play with the xlsxwriter module

# -*- coding: utf-8 -*-
# this collects the names and how many emails i received from them for the week

import win32com.client
from collections import defaultdict
import datetime as dt
from datetime import datetime, date
import xlsxwriter

f = open("test.org", 'w')

# setup connection to outlook
outlook = win32com.client.Dispatch("Outlook.Application").GetNamespace("MAPI")
inbox = outlook.GetDefaultFolder(6)

# setup xlsx file
workbook = xlsxwriter.Workbook('outlook_data.xlsx')
worksheet = workbook.add_worksheet('WK'+date.today().strftime('%V'))

# get inbox messages
messages = inbox.Items
message = inbox.Items.GetFirst()

# get week number
counts = defaultdict(int)
rw = 0

# create header for file
f.write('%s%s%s%s%s%s' % ('| ', 'Mails received in week', '| ' , date.today().strftime('%V'), ' |', '\n'))

# loop through all messages and check if received this week
for message in inbox.Items:
    r = message.ReceivedTime.strftime('%V')
    if r == date.today().strftime('%V'):
        counts[message.SenderName] +=1

for key in (counts):
    print('|', key, '|', counts[key], '|')
    worksheet.write(rw, 0, key)
    worksheet.write(rw, 1, counts[key])
    f.write('%s%s%s%s%s%s' % ('|', key, '|', counts[key], '|', '\n'))
    rw +=1


f.close()
workbook.close()