Graham Mayor
... helping to ease the lives of Microsoft Word users.
 |
 |
 |
Many people access the material from this web site daily. Most just take what they want and run. That's OK, provided they are not selling on the material as their own; however if your productivity gains from the material you have used, a donation from the money you have saved would help to ensure the continued availability of this resource. Click the appropriate button above to access PayPal.
Extract Data from Outlook E-Mail Messages Add-in
The add-in described below is for Word versions 2007 and later. Word, you might think, is a curious vehicle to use for extracting e-mail data to Excel, Access or a Word table from Outlook, but it has a few advantages over doing the same thing 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 there you have it - it's a Word add-in that automates Outlook, Excel and Access.
Overview
The add-in can batch process messages in a variety of formats, but only one type of message at a time and that type of message should have been moved to an Outlook folder dedicated to the task.
Moving the messages to a dedicated Outlook folder is best done as they arrive, using an Outlook Rule to identify the messages and move them to the folder. If you want to process the messages individually as they arrive then I would refer you to the link in the second paragraph of the column on the right of this page.
The types of message format that may be processed include messages with lists as follows:
- Data comprising identifiers and variable data on the same line separated with a defined character. This is referred to in the add-in as 'delimited'. The default delimiter is the colon, but you can select others.
- Data comprising identifiers on one line with the variable data on the following line. This is referred to in the add-in as 'stepped'. (If your identifiers and data are separated by more than one line, you can select the individual lines to extract).
- Data with the identifiers and variable data in tabular format.
- Data can be extracted from the above formats using the entire message body or a range defined by the add-in user. This enables unwanted heading text and signatures etc. to be omitted from the process.
If you plan to use this add-in to process your returned forms, then it is not a bad plan to setup the forms to work with the add-in rather than rely on the fact that it might work with your format.
As with the other add-ins available from this site, it is presented as a Word template and is supplied in zipped format. The zip container also contains a self extracting zip file which will attempt to install the add-in the default Word startup folder. If you have not changed the preferred startup folder it can be located (in English language versions of Windows) by typing
%appdata%\Microsoft\Word\Startup
in the Windows Explorer Address bar and pressing Enter.
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:
The functions of the two buttons are self evident. The main button runs the process, the 'Reset' button removes the stored data from the registry to install default status. The 'Remove Category' button is a later addition.
When the process is run, the messages processed are categorized with a 'Processed' category. This ensures that the messages are not re-processed next time the main function is used. The 'Remove Category' button removes this category from all messages in a selected folder.
Message format examples
Identifier and data on the same line - separated by a colon separator character:
Identifier and data on the same line - separated by a colon separator character with unwanted 'chaff' at the start and end of the message:
Stepped message data - identifiers with data on subsequent lines
Identifiers and data in tabular format
Note: The process is intended to work with HTML format messages. Where the messages are in Plain Text format, the process can still be used, as it changes the format of the messages, temporarily to HTML, then changes them back again. This can result in some permanent changes to the messages and therefore the process warns of the danger when the Outlook folder is selected. If this is likely to be a problem, create a new Outlook message folder and copy the messages to that new folder for processing.
Getting Started
The first time you click the add-in's 'Extract Message Data' control button, the main userform interface dialog displays a standard disclaimer notice. Before you can use the add-in to extract data you must accept the terms of the disclaimer.
With the disclaimer text accepted, the main dialog is displayed.
From this dialog you must select the place to store the data in the top section, and select the Outlook folder containing the messages in the following section, from the dialog:
All the messages in the selected folder must be formatted similarly!
With the data target location selected and the Outlook folder selected the main dialog displays a warning which when closed, the settings options and the content of the first message in the folder.
Complete the 'Extract' options to reflect the message type.
In the illustrated example, that is 'selected lines only.
If 'extract
text to the right of the first delimiter only' an additional
combo box is displayed which allows the selection of the
delimiter, which would, for the example below, be a colon.
Select the lines to be processed in the list box to activate the 'Extract Data' button.
Help screens are provided when you click the context sensitive help buttons e.g.:
Click Extract Data to complete the process.
It is a good plan to start with a blank worksheet. Then add the header row on completion of the data transfer. Subsequent data transfers will append the data to the end of the worksheet.
Transfer of data is very fast, however there is a progress indicator, which is really of benefit only when processing a large number of messages. When only a few files are processed the process is so fast that (with Excel and Access data files) you may only see the following completion dialog.
A lot of work that went into developing this add-in, and it was Greg's and my original intention to make a charge for it. We have, however not done that. It will be donation ware like the other add-ins on our respective sites. Please consider making a donation.
-
Click here to download the add-in
The Background
Some time ago I became embroiled in a long running thread in the MSDN programmers' forum where (and from the private feedback) it became clear that this was a requirement desired by a large number of business users, and there was no tool available that I could find to make this job easy.
I began by creating the page elsewhere on this site that brought together the techniques described in the forum thread, but it soon became clear that this was beyond the skills of many ordinary users to implement - and not helped by the fact that Outlook is not the most comfortable programming environment for beginners.
My long time American friend and frequent collaborator Greg Maxey picked up on the discussions and suggested we develop an add-in that users could simply plug into Office and take away the backroom tasks from the user.
The result is the add-in featured on this page. We both hope you find it useful.
You will find a functionally similar add-in on Greg's site. The only differences between them are those of presentation and style to reflect our personal preferences.