Main

You do not really understand something unless you can explain it to your grandmother.

Albert Einstein

NOTE: Outlook 2010 is the same format

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)
NameKeep?Typical Value
Subjectxtext
BodyxFULL text of the email
FromNamexname
FromAddressxemail address
FromType SMTP for example
ToNamexname
ToAddressxemail address
ToType SMTP for example
CCName  
CCAddress  
CCType  
BCCName  
BCCAddress  
BCCType  
BillingInformation  
Categoriesxcategories (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
NameTypeSizeKeep?Typical Value
IDLong Integer4xUsed autonumber for PK
ImportanceLong Integer4  
IconText255  
PriorityLong Integer4  
SubjectText255x 
FromText255xOutlook name (not address)
Message To MeYes/No1  
Message CC to MeYes/No1  
Sender NameText255xOutlook name (not address)
CCText255x 
ToText255xOutlook name (not address)
ReceivedDate/Time8xwhen received in Outlook
Message SizeLong Integer4xbytes
ContentsMemo-xFULL text of the email
CreatedDate/Time8xTypically when Written
ModifiedDate/Time8xTypically when Sent
Subject PrefixText255xfor example, "RE:" or "FWD:"
Has AttachmentsYes/No1x 
Normalized SubjectText255xwith the prefix stripped out
Object TypeLong Integer4 5??
Content UnreadLong Integer4  

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:

References:

2009-03-11

Tags: Outlook

Back to Outlook


Backlinks

Courage is contagious.
When a brave man takes a stand, the spines of others are often stiffened.

Billy Graham

Page Views: 3274 | << | Trail Index | >> | HomeLinks