Recent Changes
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 |
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
|
<< | Trail Index | >> HomeLinks |
