The Anchorage

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

• Quarterdeck
• What's New
• Microsoft Word Tips
• Consulting
• Chain Locker
• Lucky Bag
• Contact
• Search

Do you have ad-blocking software enabled? While I respect your right to do so, your donations and the minimal advertisements on this site help to defray internet and other costs of providing this content. Please consider excluding this website from blocking or turning off the blocker while browsing this site.

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:

• Data identifier (name) and variable data on a single line separated with a defined character. In the add-in, this is the "delimited" format.  The default delimiter is the colon, but you can define others.
• Data identifier (name) on one line followed by variable data on the next line. In the add-in, this is the "stepped" format.
• Data identifier (name) and variable data in tabular format.  In the add-in, tabular format is processed as stepped data.
• Data can be extracted from all of the above formats using the entire message body or a range defined by the add-in user. This enables unwanted chaff (e.g., heading text, signatures, etc.) to be omitted from the extraction process.

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:

• In tabular format in a Microsoft Word document.
• In an Excel spreadsheet.
• In an Access database.

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

• Version 1.0 - Initial release
• Version 1.1 - Corrected a minor bug in process log.
• Version 1.2 - Addressed an issue that could result in transposed days and months when reading European format dates into an Excel worksheet
• Version 1.4 - (Version 1.3 lost to history) Corrects a potential compile error with Office 64-bit installations, adds a feature to remove "Processed" category from previous processed messages so those messages can be reprocessed and resolves several issues with plain text vis-a-vis html message formats.

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:

User interface Word 2007/2010/2013/2016/2019 (Word 2019 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 removed the "Processed" category from previously processed messages.
4. 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

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


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


Simple Tabular Data


Range of Data

Note: 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.

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:

• Type and file path of the data store file.
• Outlook folder containing the message or messages to process.
• The extraction method.

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.

Note: 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

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:

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.

Note: 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.

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.

Note: 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.

Note: 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.

Notes:

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.

This page was last updated on March 09, 2021

Website Privacy Statement

Back to top     Back to Word Tips