Exporting and Extracting Text from Emails

[nervous2]

I have a website which has an online LEADS form, when a customer fills out the form the information is automatically E-mailed to an address whereby I can send them information, the E-mail resembles this.

EXAMPLE

-----Original Message-----
From: prismpow@box91.bluehost.com [mailtorismpow@box91.bluehost.com]
Sent: March 17, 2008 2:05 PM
To: colo****d@pr((((der.com
Subject: Color card Request Form

Below is the result of your feedback form. It was submitted by
(a***@pe****x.com) on Monday, March 17, 2008 at 12:04:53
---------------------------------------------------------------------------

name: Ste*********ers, P***** X****ss

address: 186 J*******e

city: La V*****

state: T*

zip: 3***6

phone: 6*5-4**-***7

wphone: **5-**3-5***

Submit: Submit

*(I blanked this out to keep this information private)

I have about 1000 Emails like this, I would like to extract the contact information and have all this info entered into a spread sheet, I did manage to export to excel, but the data is hard to work with, each cell contains the whole E-mail message and I don't know how to then filter the info out into individual cells.

Would anyone have a practical way to achieve this?

Thanks You

 

[eyec]

lots of editing and replace has been my only recourse.

use the replace function to replace spaces with commas, then use the replace function to get rid of double commas...
and son on.

 

[JockMullin]

Hi, Nervous2

A VBA macro would likely be the most efficint way, and a post in the VBA for applications forum may very well elicit a useful response. However, here is what I do:

- Put all the emails in a folder
- Export that folder to a CSV file
- Open the CSV file in Excel. Column B contains the body of the emails as text. Delete all columns except B.
- Save that file as MS-DOS .txt file
- Open the .txt file in a text editor
- Remove the first and last lines which have only " in them
- Replace all occurrences of the four character string double quote/carriage return/line feed/double quote with a character that doesn't appear in the text (I use ^, the caret). This is now a separator between records.
- Replace all occurrences of carriage return/line feed with tab characters.
- Replace all carets with carriage return/line feed.
- Save the file.
- Import the file back into Excel as a tab-delimited text file. Each line in the original text body will now be in a separate cell.

Bit of a kludge, but it works.

Jock

 

[nervous2]

thanks for the solution, I'm working with your idea, but would you know of a way to setup the TXT file so that the instead of each field taking up the column, the name would be the column heading and the actual name would follow underneath and following the company the column heading and the actual company name follows underneath.

Thanks Again.

 

[JockMullin]

Hi, Nervous2

So, if I understand, what you want is:

name,address,city,state,zip,phone,mphone
Joe Smoo,123 Any St,Anytown,XY/12345-6789,555-1212,555-2323
AB Cyr,555 Main St.Mudvill,QA,21212,1234567,555-987-6543
...

That would most easily be done with a program, and in fact I have one handy very similar.

If you want I can send you a quick and dirty which will ask you for the input .txt file name and create a .csv file that you will be able to open in Excel.

I can send you an .exe file renamed to .xxx to the email address in your original post if you want.

Jock