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.
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.
The types of message format that may be processed include messages with lists as follows:
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
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.
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:
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.:
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.
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.