Excel 2003: Display Live Data from Email Responses

Outlook 2007 has a wizard to collect data via email:

 

Access 2003 (for those of us that still have to use it at work !) does not have this feature.

Nevertheless, you can acheive much the same result with a bit more work.....

Create an email (as plainly as possible) asking the questions (e.g. a table with cells in which to type):

Place delimiters either side of the data that you want to extract.  Choose uncommon ascii characters like ÆØÝÞß挧€.

Please accept the following Application information for ........

Name	Office Use		Office Use
Given Name	Æ	Stephen	Æ
Family Name	Ø	Digby	Ø
Address Street	Ý	12 Digbys St	Ý
Address Suburb	Þ	Digby	Þ
Address Postcode	ß	1234	ß
Phone	æ	1234 5678	æ
Interests	Œ	Helping people	Œ
	§		§

Within an Access 2003 database: File - Get External Data - Link Tables - Files of Type - Outlook - Choose a mail folder that will contain the email responses.

This will create a table containing the email data that will include the responses.

 

Open Excel 2003 - Data - Import External Data - New Database Query - MS Access Database - Select Database:

 Choose the database

Select the fields that you want to use in the Excel 2003 spreadsheet (i.e. Email fields "From" and "Contents"  sometimes labelled as "Body".

The table data will then appear in the spreadsheet.

Use a formula to extract each delimited piece of data from within the "Body" field.

e.g. If the delimiter either side of the data in the email was Æ and the "Contents" field is in F5, then the following formula will search inside the "Contents" field and extract the data.

=TRIM(CLEAN(MID(F5,SEARCH("Æ",F5)+1,SEARCH("Æ",F5,SEARCH("Æ",F5)+1)-SEARCH("Æ",F5)-1)))

Note: If you wanted to do the same thing within Access 2003 by using a query, the formula would be:

TRIM(MID([Body],INSTR([Body],"#")+1,INSTR(INSTR([Body],"#")+1,[Body],"#")-INSTR([Body],"#")-1)).  As Excel 2003 cannot link to a query, I found it easier to link Excel 2003 to the table and then do the extraction within Excel 2003.

To refresh the sheet with the latest email data, just right click on any data field and select "Refresh Data".

You can help the user by coding this as a macor and assigning it to an excel button.

To remove old data, merely move emails in Oulookt to another folder or delete them.