Main »

Extracting Data From Outlook 2007

If you eat a frog first thing in the morning, the rest of your day will be wonderful.
...
If you have to eat a frog, don’t look at it for too long.

Mark Twain

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

Do not go where the path may lead, go instead where there is no path and leave a trail.

Ralph Waldo Emerson

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

Index | Home

  Notes
  Investments
  InvestmentLog
  Research
  Teaching 
  ReadingList
  ClassicQuotes
  Computer Applications

Recently Changed


About | Stats | All Pages by Date Changed

http://devriesonline.net

PmWiki

 PmWikiUserInfo
 Skin Admin  
 Basic Editing
 Documentation Index
 pmwiki.org

Lego

When you don't know what to do, do something.

James Fisk, Bell Labs

edit SideBar

Page last modified on October 10, 2012, at 05:32 PM - Powered by PmWiki

^