We help IT Professionals succeed at work.

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

GCortesJr
GCortesJr asked
on
355 Views
Last Modified: 2013-12-18
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?
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2007

Commented:
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 !

Author

Commented:
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.
CERTIFIED EXPERT
Top Expert 2007

Commented:
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 !
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.
CERTIFIED EXPERT
Top Expert 2007

Commented:
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 !

Commented:
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.

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

Author

Commented:
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

Author

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

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

Commented:
PAQd, 500 points refunded.

DarthMod
CS Moderator
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.