Importing Email Form Data into a Single Worksheet

PROBLEM:
I have a form on a webpage that submits the form data results to me via email. I now have hundreds of emails (.eml files from Windows Live Mail) in a folder on my desktop that contain newsletter signup data for people wanting to join my mailing list. The data is in the body of the messages in the following format:

Field 1: User Entry 1
Field 2: User Entry 2


SOLUTION NEEDED:
I need a way to automate importation of the data from the text inside these emails into an excel (2007) spreadsheet.

DETAILS:
I can import them one by one using the data > import from text command. But this only works one at a time. Excel does not allow importing more than one text file at a time. I have hundreds. I tired recording a macro to automate this process, but the resulting macro simply imports the same email files over and over rather than moving through them. I can't get it to import all the email files in a folder or to import them in bulk. I need a "one click" solution. I set it up, run it, and the data gets stripped out, imported to the appropriate cells, and I'm done.

Also, this method imports them vertically, which each entry for a person as a COLUMN and the data header fields as rows, when I really would like it to be the opposite (which each entry as a row and the headers for each field as a column). This is a smaller point, since just getting the data into Excel is my main problem right now, but it's worth mentioning.

I've tried looking for software to do this, but have not yet had any luck. I am not in any way a script writer, by the way, so scripting something is not really an option for me. I am hoping there is a simple and quick way to make short work of this task.



I would appreciate some help from an Excel guru more learned than I! Thanks guys!

Hi

You can create a list of all files in a folder using the following code (the files will be listed on a sheet called 'FileList' so make sure it exists.

Please Login or Register  to view this content.

You could then loop through each entry on the list and import it. Without seeing the format of the files I can't write the code.

Dion