Resting Anchor

The Anchorage

Personal website of Gregory K. Maxey, Commander USN (Retired)

Extract Data from Outlook E-Mail Messages
(A Microsoft Word Help & Tip page by Gregory K. Maxey)

DISCLAIMER/TERMS OF USE

The information, illustrations and code contained in my "Microsoft Word Tips" are provided free and without risk or obligation.

Click to acces PayPal Verification Service Click to acces PayPal Verification Service

However, the work is mine. If you use it for commercial purposes or benefit from my efforts through income earned or time saved then a donation, however small, will help to ensure the continued availability of this resource.

If you would like to donate, please use the appropriate donate button to access PayPal. Thank you!


This Microsoft Word Tips & Microsoft Word Help page provides my "Extract Data from Outlook E-mail Messages" Word template add-in.

Background\Summary\Applicability

The development of this add-in resulted from repeated questions in Outlook user support forums seeking a method to extract parts a message body as named data fields and variable data elements to a structured data file.

It is a collaborative effort with Word MVP and my long time Englishman friend Graham Mayor.

In each case the answer to the question asked required a custom VBA solution.  Graham and I have endeavored to create a single solution that will work with several message formats:

A best practice is to design your message format to work with the add-in rather that rely on the add-in to work with your format.

The extracted data can be stored and viewed:

You might think that Word is a curious vehicle to use for extracting e-mail data to Excel, Access or a Word table from Outlook.  Curious maybe, but it has a few advantages for a Word programmer like myself.  Foremost of which is the ease of distribution, given that all versions of Office that include Outlook and Excel will also include Word.

So I present an add-in applicable to Word versions 2007/2010/2013 which is interoperable with Access, Excel and Outlook.

Version History

Getting Started

As with the other add-ins available from this site, it is presented as a Word template and is supplied in zipped format.  You can download the template using the link at the end of this tips page.

When installed, the add-in displays a control group and controls on the Word Ribbon Add-Ins tab as shown below:

extra outlook data 1

User interface Word 2007/2010/2013 (Word 2010 shown)
  1. Group control.  Defines all other controls used by the add-in.
  2. Main interface control. Launches the add-in primary user interface dialog.
  3. Auxiliary control. Use to reset the add-in default settings.

Outlook Message Format

As mentioned in the summary, the data in the Outlook message body can be presented in several different formats.  The following illustrations depict the formats supported by the add-in

extract outlook data 2
Simple Delimited Data
(Data identifier and variable data delimited using a colon)

extract outlook data 3
Simple Stepped Data
(Data identifier and variable data appear on separate lines)

extract outlook data 4
Simple Tabular Data

extract outlook data 5
Range of Data

Site Note IconNote: The highlighted area in the illustration above depicts data the user will define for extraction.  The remaining data is ignored by the add-in as superfluous chaff.  Range extraction can be used with all three simple data formats.

When using the add-in to extract data, "ALL" messages in the Outlook Message Source folder must be in the same format and contain the same data identifiers for a given extraction session.

First Use/Main Dialog

Disclaimer

The first time you click the add-in "Extract Message Data" control, the main userform interface dialog displays a disclaimer notice.  Before you can use the add-in to extract data you must accept the terms of the disclaimer.

extract outlook data 6

The add-in main dialog contains four common controls illustrated above.

  1. Cancel control.  Cancels setup for data extraction and closes the dialog.
  2. Donate control.  Displays links and controls that enable you to contribute to the support of this website and my work with donations processed with PayPal.
  3. The "Resting Anchor" logo control.  Opens this tips page.
  4. Execute control.  A multi-purpose command execute control.

Extract Data

After accepting the disclaimer, the "Extract Data from Outlook" page is displayed.  Using the controls on this page you set:

extract outlook data 7

Data Store

Use the controls in this section to define the data store for the extracted data.

The add-in will extract data from OUTLOOK mail message items to an Access table, Excel spreadsheet or Word table.

Prior to extracting data, you must create and save a properly formatted Access, Excel, or Word file to use as the output target file.

Excel is the simplest file type to employ. To use Excel, you simply create and save a new Excel file. Select the Excel data store, browse to and select the Excel file, then select the output worksheet.

To use Word, you must create and save a Word file containing a single table with one (1) row and "X" columns, where "X" represents the number of variable data fields you are extracting. Select the Word data store, browse to and select the Word file.

To use Access, you must create and save a Access file and table with "X" columns, where "X" represents the number of data fields you are extracting. Select the Access data store, browse to and select the Access file, then select the output database table.

You may add column names to your output files at your discretion.

Outlook Message Source

Use the controls in this section to define the Outlook mail item folder containing the message or messages you want to process.

For best results, you should create and define a dedicated Outlook mail item folder to hold the messages you want to process.  When messages are received in your inbox you can manually move them to the defined folder or create an Outlook rule to move them automatically as they are received.  See:  Managing E-mail Messages Using Rules.

The following illustrates a dedicated mail item folder I've created for demonstration purposes.  It consists of a sub-folder of my inbox folder and contains a single message to process.

extract outlook data 8

Site Note IconNote: I've used symbols (e.g., ####) to mask other personal sub-folders contained in my inbox.

After processing, the add-in automatically tags each message in the source folder as Processed which prevents messages left in the source folder from being processed repeatedly each time the process is run.  A best practice is to move the processed messages to a different folder after processing. Here I have created a new folder "Processed Forms" and move processed messages into it.

extract outlook data 9

Extract

When the data store and Outlook Source Folder are defined, you are ready to being data extraction.  The following depicts the selections I've made for this demonstration:

extract_outlook_data_10
  1. That data store is an Excel file named Extracted Outlook Data.xlsx located in the D:\Data Stores folder.
  2. The worksheet is Sheet 1.
  3. The Outlook Message Source is the Forms Received folder that I created for storing messages to process.
  4. When the Outlook Message Source is defined, the dialog listbox displays a representative example of the data contained in the folder messages.  This data is determined by the content of the first message in the folder.

Site Note IconNote: The "Extract Data" command button on the dialog is disabled.  At this point it is up to the add-in user to determine the type of extraction to perform.

On reviewing the representative data, I can readily determine that the message format contains superfluous data (chaff) e.g., "John, In my weekly ..." that I do not want to extract and the data identifiers and variable data are stepped i.e., data variable follows data identifier on a new line.

At this point, as the add-in user, I must select the extraction method Range Stepped and identify the first and last data variable in the extraction range.

I also note that the representative data contains hyperlinks.  I want to retrain extraneous hyperlink data so I've check the option to to so.

extract outlook data 11
  1. For this extraction session the extraction method is Range Stepped.  Remember: All messages in the Outlook Message Source folder must be in the same format.
  2. I want to retain extraneous data in hyperlinks.
  3. When using a range method, you must select and identify the first and last data element to extract.  When the range method is stepped, you select and identify the first and last variable data element.
  4. Here I have selected the last variable data element in the range.  While not shown, I have also selected the first variable data element in the range.
  5. The add-in is configured and ready for data extraction.

Site Note IconNote: Both tabular and stepped message formats will appear as stepped data in the dialog listbox.

The help "?" icons on the dialog can be used to display amplifying information on setup.

Processing and Output

The add-in displays a progress bar during processing and when processing is complete.

extract outlook data 12

Site Note IconNote: Processing occurs rapidly and the progress bar may simply appear as Processing Complete as shown above.

When processing is complete, you can open and few the extracted data in the data store you defined.  The following illustrates data extracted to an Excel file.

extract outlook data 13

Site Note IconNotes:

1. You can customize your data file by defining column headings and adjusting column widths to suit your data requirements.

2. Due to page size attributes in Word, Word may not be a suitable format for extracting a large number of variable data elements.

Conclusion

You can download the template add-in here:  Extract Outlook Data.  For installation instructions, see: Organizing your Macros\Template Add-Ins.

Graham and I can never agree 100% on style and format.  To review Graham's functionally similar add-in and tips page see:  Graham's version.

That's it! I hope you have found this tips page useful and informative.

Share

PAYMENTS/DONATIONS

Click to acces PayPal Verification Service Click to acces PayPal Verification Service

Do you want to make a payment for consulting work or donate to help support this site?

PayPal is a safe, easy way to pay online.

Use the appropriate currency "Donate" button to make a payment or donation.


Search my site or the web using Google Search Engine

Google Search Logo