Del DeVries : Extracting Data From Outlook 2007

Outlook fields exported to an Excel file

I started this process by trying to extract email from Outlook to an Excel file for summarizing information. Although the export works well for obtaining some of the email information, including Categories, subject, to/from, and the Body - it is missing a critical piece: the date!

Outlook fields (export)

Name	Keep?	Typical Value
Subject	x	text
Body	x	FULL text of the email
FromName	x	name
FromAddress	x	email address
FromType		SMTP for example
ToName	x	name
ToAddress	x	email address
ToType		SMTP for example
CCName
CCAddress
CCType
BCCName
BCCAddress
BCCType
BillingInformation
Categories	x	categories (separated by commas)
Importance		Normal for example
Mileage
Sensitivity

Outlook fields pulled into an Access database file

Starting from the other side of this problem - using Access 2007 to get External Data from an Outlook file, works well , retrieving the 3 different date/time stamps for each message (Received, Created, Modified) ... but no Categories! Starting from Access is better as there is more extensive info, including email size, attachment flag, and some other details.

The resulting table in Access (pulled in from Outlook using External Data menu) has these fields (annotated with my keep criteria and notes):

Outlook fields

Name	Type	Size	Keep?	Typical Value
ID	Long Integer	4	x	Used autonumber for PK
Importance	Long Integer	4
Icon	Text	255
Priority	Long Integer	4
Subject	Text	255	x
From	Text	255	x	Outlook name (not address)
Message To Me	Yes/No	1
Message CC to Me	Yes/No	1
Sender Name	Text	255	x	Outlook name (not address)
CC	Text	255	x
To	Text	255	x	Outlook name (not address)
Received	Date/Time	8	x	when received in Outlook
Message Size	Long Integer	4	x	bytes
Contents	Memo	-	x	FULL text of the email
Created	Date/Time	8	x	Typically when Written
Modified	Date/Time	8	x	Typically when Sent
Subject Prefix	Text	255	x	for example, "RE:" or "FWD:"
Has Attachments	Yes/No	1	x
Normalized Subject	Text	255	x	with the prefix stripped out
Object Type	Long Integer	4		5??
Content Unread	Long Integer	4

Outlook

Outlook Keyboard Shortcuts

Linking the 2 files in Access

Using both files, the Export from Outlook and the Access file pulled from Outlook, is a decent hack for adding the Categories field to Access. But it requires a little faith that the export/import order is exactly the same in the 2 methods (it appears to be!). Before establishing a relationship in Access between the two tables, the export from Outlook requires an autonumber primary key field. Of course check that the resulting relationships make sense. My simple file testing showed it was ok. Export / import order was oldest to newest.

Using VBA for a customized extract

If more than a hack is required! The alternative is to write VBA to create the exact set of data that you need. Some info to get started, with sample code:

How do I... Export e-mail messages to Access using VBA?
Quickly export Outlook e-mail items to Excel
Other code snippets specific to the date and time objects at OutlookCode
MSDN reference to Outlook 2007 MailItem objects and properties.
MSDN reference to MailItem object (Outlook 2003). Applicable Properties are referenced, including: Attachments, Body, BodyFormat, Categories, CreationTime, FlagDueBy, FlagStatus, HTMLBody. LastModificationTime, ReceivedTime, Size, Subject, To, and others

References:

Importing Data from an Outlook Folder in Access 2007 step by step process in Access to bring in External data.

_2009-03-11

Tags: Outlook

Back to Outlook

Please, God, send me ideas.

Julia Cameron

<< | Trail Index | >> HomeLinks