asked on

How do I grab information from e-mails and pt it into a spreadsheet?

I sent out an email to 500+ contacts. the email launch a survey that the user responded to an when the survey finish it automatically emailed all of the answers back to me. All of the emails are in the same format. How do I get the information from all of the emails, that are stored in the same folder, into a spreadsheet. I need to grab the "from" field too.

The responses are in the follwing format:

Preventative Maintenance performed in 2006:
No [I want to grab this line]

Reason Preventative Maintenance not performed in 2006:
preventative maintenace on what? [I want to grab this line]

Has a Preventative Maintenance Service Provider been selected for 2007:
No [I want to grab this line]

Would you like help selecting a Service Provider:
Yes [I want to grab this line]

Service Provider Selected for 2007:
N/A [I want to grab this line]

Name of 'OTHER' Service Provider:
N/A [I want to grab this line]

Helpdesk Technician:
John Doe [I want to grab this line]

I would like to get this into a spreadsheet in the following format:

[From] [line 1 answer] [line 2 answer] [line 3 answer] [line 4 answer] [line 5 answer] [line 6 answer] [line 7 answer]

Can anyone help me with this?

SysExpert

Well , if you did not create fields for each answer then you are going to have to parse each email , pull the answer out and put it into a form that does have Fields for each answer.

Do you have fields in the original Email form sent ?

I hope this helps !

GCortesJr

ASKER

No I don't have fields. However, I was thinking that it would be possible to use the TextParagraph Property. I am looking into it right now.

If anyone else has any ideas, I am listening.

SysExpert

Is the mail using Richtext ?

First you need to find a way to read it.
See the Documnet properties to determine where your text is stored ( Text, RTF ) and then use those specific Doc methods to extract the info.

I hope this helps !

madheeswar

I think you should take path of Mail-in-database.

In using mail-in-db, all the mails coming into the survey db, can be handled and can process according to your requirements.

Hope it helps.

SysExpert

GCortesJr.

1) Is this a completed Survey already or are you still planning it ?

2) If not completed, I would definitely look at the options and good samples in the Sandbx

http://www-10.lotus.com/ldd/sandbox.nsf

I liked this one in particular.

http://www-10.lotus.com/ldd/sandbox.nsf/ecc552f1ab6e46e4852568a90055c4cd/4c3d4a272f9f33f685256b6e004fddaf?OpenDocument&Highlight=0,survey

Easy customizable and has validation and auto export to Excel.

I hope this helps !

LukeOliv

I'm currently working with a similar project and my best idea so far is to export all the emails (in my case about 3000) to text format and use the free and easy to use AutoIt scripting program to collect all the info to another format.

When you export an email to text format you can also easily get the "from" -field.

I'm sure that there are faster scripting utilities you can use to do the task but I've found AutoIt the simplest to use.

LukeOliv

Btw. the link to AutoIt is http://www.hiddensoft.com/autoit3/

GCortesJr

ASKER

I solved my own problem, but thanks for your contributions. I exported all docs to one text file and because all of them were in the same format I was able to put this together ( a little sloppy but I wrote very late in the night) and it worked out great.

Sub ImportTXTFile()

' Macro written on 2/6/2007 by CORTEG01
Worksheets("Imported TXT File").Activate
fileToOpen = Application _
.GetOpenFilename("Text Files (*.txt), *.txt")
fileToOpen2 = "TEXT;" & fileToOpen
If fileToOpen <> False Then
With Worksheets("Imported TXT File").QueryTables.Add(Connection:= _
fileToOpen2, Destination:= _
Range("A1"))
.Name = "test"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End If

Worksheets("Processed TXT Records").Activate
Dim myFile, myStr
myFile = FreeFile
Open fileToOpen For Input As #myFile
Do While Not EOF(myFile)
Line Input #myFile, myStr
If InStr(1, myStr, "Subject") Then
Cells(i + 1, 1) = myStr: i = i + 1
End If
Loop
Close #myFile

End Sub

Sub processImport()
numRecords = Application.WorksheetFunction.CountA(Worksheets("Processed TXT Records").Range("A:A"))
range1 = "B1:J" & numRecords
Worksheets("Imported TXT File").Activate
Range("A1").Activate
myLastRow = "R" & ActiveSheet.Cells(65536, ActiveCell.Column).End(xlUp).Row & "C1"
Worksheets("Processed TXT Records").Activate
' Macro written on 2/6/2007 by CORTEG01
Range("B1").Activate
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],4)"
Range("C1").Activate
ActiveCell.FormulaR1C1 = "=RIGHT(OFFSET('Imported TXT File'!R1C1,MATCH(RC[-2],'Imported TXT File'!R1C1:" & myLastRow & ",0)+5,0),22)"
Range("D1").Activate
ActiveCell.FormulaR1C1 = "=OFFSET('Imported TXT File'!R1C1,MATCH(RC[-3],'Imported TXT File'!R1C1:" & myLastRow & ",0)+17,0)"
Range("E1").Activate
ActiveCell.FormulaR1C1 = "=OFFSET('Imported TXT File'!R1C1,MATCH(RC[-4],'Imported TXT File'!R1C1:" & myLastRow & ",0)+20,0)"
Range("F1").Activate
ActiveCell.FormulaR1C1 = "=OFFSET('Imported TXT File'!R1C1,MATCH(RC[-5],'Imported TXT File'!R1C1:" & myLastRow & ",0)+23,0)"
Range("G1").Activate
ActiveCell.FormulaR1C1 = "=OFFSET('Imported TXT File'!R1C1,MATCH(RC[-6],'Imported TXT File'!R1C1:" & myLastRow & ",0)+26,0)"
Range("H1").Activate
ActiveCell.FormulaR1C1 = "=OFFSET('Imported TXT File'!R1C1,MATCH(RC[-7],'Imported TXT File'!R1C1:" & myLastRow & ",0)+29,0)"
Range("I1").Activate
ActiveCell.FormulaR1C1 = "=OFFSET('Imported TXT File'!R1C1,MATCH(RC[-8],'Imported TXT File'!R1C1:" & myLastRow & ",0)+32,0)"
Range("J1").Activate
ActiveCell.FormulaR1C1 = "=OFFSET('Imported TXT File'!R1C1,MATCH(RC[-9],'Imported TXT File'!R1C1:" & myLastRow & ",0)+35,0)"
Range("B1:J1").Select
Application.CutCopyMode = False
Selection.Copy
Range(range1).Select
ActiveSheet.Paste
Range(range1).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
End Sub

Sub processFlatFile()
ImportTXTFile
processImport
ActiveWorkbook.Save
End Sub

GCortesJr

ASKER

Can I have a refund on my points and the question closed?

LukeOliv

Thanks for sharing your macro! Glad you worked it out ok.

DarthMod

PAQd, 500 points refunded.

DarthMod
CS Moderator

ASKER CERTIFIED SOLUTION

Computer101

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial