So one of my colleagues at work showed me this cool script he wrote in Visual Basic to pull all the data from Outlook for analysis.
Cool, I thought – I’d like to do that, but don’t want to muck about in VB.
Well, I was surprised to discover that Outlook has the ability to export email to CSV built in! Follow the simple steps below (here demonstrated in Outlook 2010) and you can analyze your emails yourself and do some cool quantified self type analysis
How to Export Outlook Email to CSV (from Outlook)
1. Open Outlook and click File then Options to bring up the options dialog:
2. Selected Advanced, then click the Export button:
3. Click Export to a file and then the next button:
4. Selected Comma Separated Values (Windows) and click next.
5. Unless you want to export a different folder, select Inbox and click next.
6. Browse to a folder and/or type a filename for your export.
7. Choose Map Custom Fields… if you want to customize which fields to export. Otherwise click the Finish button.
8. Sit tight while Outlook does its thing.
You should now have a CSV file of your inbox data!
How to Export Outlook Email to CSV (from Access)
This is all very well and good, but unfortunately exporting to CSV from Outlook does not provide the option for date and time as fields to be included, which makes it useless if you’d like to do time series (or other temporal) analysis.
To get the date and time data you can pull data from Outlook into Access and then export it as noted in this metafilter thread
Import from Outlook into Access
1. Fire up Access and create a new database. Select External Data, More.. and then Outlook Folder.
2. Select Import the source data into a new table in the current database and click OK
3. Select the email account and folder you’d like to import and click Next
4. Change the field settings if you’d like. Otherwise accept the defaults by clicking Next
5. Let Access add the primary key or not (you don’t need it). Click Next
6. Click Finish and wait. When the process is done you should have a new table called ‘Inbox’.
Export Data from Access to a CSV
1. Make sure the Inbox table is selected and click External Data then Text File.
2. Pick or type a filename and click OK
3. Selected Delimited and click Next
4. Select Comma as the delimiter and tick the box which says Include Field Names on First Row. Click next.
5. Pick or type a filename and click Finish
You should now have your Inbox data exported as CSV (including time / date data!) and ready for analysis. Of course you can repeat this process and append to the Access database folder by folder to analyze all the mail you have in Outlook.