Importing and Parsing Email Text in Excel: interesting problem

[jscranton]

Thinking through a problem here and thought I would elicit thoughts from the board.

I have a few thousand emails saved as text files. [For the sake of the problem, they are all saved in .txt format]. The issue is that the emails are often chains where the recepient/sender/dates/subject can change in the course of the chain. So one Thread might contain two separate emails:

From: Sender1
To: Recepient1; Recepient2
CC: Recepient3; Recepient4
Subject: Email1
Date: Date1

Text of Email1 here

From: Recepient1
To: Sender1
CC: Recipient3
Subject: Email2
Date: Date2

Text of Email2 here

I have a macro to import all the texts into a spreadsheet. What I am thinking through is how to parse the email into the various pieces of the email chain.

Since the format for the sender/recepient/subject/date is always the same, it seems that some sort of loop would work such as:

For each text:
Find "From:" and return value then
Find "To:" and return value then . . .

Find Next From and if none go to next document.

I am happy to paste the macro for importing text if it will help anyone else think through this with me.

As always, appreciate the help.

 

[hiker95]

jscranton,


If your raw data was in Sheet1, column A, we could do something like this:

Excel Workbook
	A	B	C	D	E	F
1	From: Sender1		Sender	Receipient	Subject	Date
2	To: Recepient1; Recepient2		Sender1	Recepient1, Recepient2, Recepient3, Recepient4	Email1	Date1
3	CC: Recepient3; Recepient4		Sender1	Sender1, Recipient3	Email2	Date2
4	Subject: Email1
5	Date: Date1
6
7	Text of Email1 here
8
9	From: Recepient1
10	To: Sender1
11	CC: Recipient3
12	Subject: Email2
13	Date: Date2
14
15	Text of Email2 here
16
Sheet1

We would like to see more raw data, and also the actual results area.


What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (sensitive data scrubbed/removed/changed) (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net, mark the workbook for sharing, and provide us with a link to your workbook.

 

[hiker95]

jscranton,


Can we also see your macro code?


If posting VBA code, please use Code Tags, see below in my Signature block: If posting VBA code, please use Code Tags - like this

 

[jscranton]

THanks for the help and forum suggestions. Using Excel 2007.

Let me take a step back as I realize the code to import texts isn't working.

So, I will start from the beginning. I have a few thousand emails that I have extracted the text from so that--for the most part--they look like this:

_________________________________________________
Sample Email

From: John Doe
Sent: Tuesday, November 05, 2011 8:46 AM
To: Jane Deer
CC: Steve Boss
Subject: RE:
Email Text.

From: Jane Deer
Sent: Tuesday, November 04, 2011 8:45 AM
To: John Doe
Subject: RE: Sensitive Data With Comments
Email Text

From: John Doe
Sent: Tuesday, November 03, 2011 8:44 AM
To: Jane Deer
Subject: RE: Sensitive Data
Email text.

From: Jane Deer
Sent: Tuesday, November 01, 2011 8:28 AM
To: John Doe
CC: Mark Coworker
Subject: FW: Sensitive Data
_______________________________________________


I would like to create code that would do import the text into excel so that it would appear like this:

Sheet1

Excel Workbook
	A	B	C	D	E	F
1	Sample Email	From	To	CC	Sent	Subject
2	1	John Doe	Jane Deer	Steve Boss	Tuesday, November 05, 2011 8:46 AM	RE:
3	2	Jane Deer	John Doe		Tuesday, November 04, 2011 8:45 AM	RE: Sensitive Data With Comments
4	3	John Doe	Jane Deer		Tuesday, November 03, 2011 8:45 AM	RE: Sensitive Data
5	4	Jane Deer	John Doe	Mark Coworker	Tuesday, November 02, 2011 8:45 AM	FW: Sensitive Data
6	Next Sample
...

Excel 2007

So, there would be two loops (one nested). The first would cycle through all the saved emails in a given file and import the text. The second would cycle through the extracted text in a given sample email and parse it as I have shown above.

Does that help?

 

[hiker95]

jscranton,

I would like to create code that would do import the text into excel so that it would appear like this:

Sorry, I have not tried to read all the text files in a folder, and manipulate the text file data directly into the six fields per your screenshot.

Click on the Post Reply button, and just enter the word BUMP, and then click on the Post Reply button, and someone else will assist you.

 

[jscranton]

Thanks for your help. Here is where I stand at the momen. I haven't checked this code for errors but logically this should work:

Sub txtSearch()

Dim fs As FileSearch
Dim i As Integer
Dim wbk As Workbook

Set fs = Application.FileSearch

With fs
.LookIn = ThisWorkbook.Path
.Filename = "*.txt"

For i = 1 To .Execute()

Set wbk = Workbooks.Open(.FoundFiles(i))
''//RUN Other loop HERE

Set wbk = wbk.Close(SaveChanges:=True)

Next i

End With

End Sub

Sorry if this isn't the proper format to post code. Appreciate any help.

 

[jscranton]

Bump if anyone has ideas.

Appreciate the help.