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

Sub PopulateDirectoryList()
 '''''MUST SET REFERENCE to WINDOWS SCRIPT HOST OBJECT MODEL''''''''''''
     
    Dim objFSO As FileSystemObject, objFolder As Folder
    Dim objFile As File, strSourceFolder As String, x As Long, i As Long
    Dim wbNew As Workbook, wsNew As Worksheet
     
    Application.ScreenUpdating = False
     
    Set objFSO = New FileSystemObject
    strSourceFolder = BrowseForFolder
    If strSourceFolder = "" Then Exit Sub
     
    Sheets("FileList").Range("A2:F10000").Value = vbNullString
     
    Set wbNew = ThisWorkbook
    Set wsNew = wbNew.Sheets("FileList") 'set the worksheet
    wsNew.Activate
     
    With Application.FileSearch
        .LookIn = strSourceFolder 'look in the folder browsed to
        .FileType = msoFileTypeAllFiles
        .SearchSubFolders = True
        .Execute
         
        For x = 1 To .FoundFiles.Count
            i = x
            If x > 60000 Then
                MsgBox "There are too many files in the folder (> 60000!).", vbCritical
                Exit Sub
            End If
            On Error GoTo Skip
             
            Set objFile = objFSO.GetFile(.FoundFiles(x))
            With wsNew.Cells(1, 1)
                .Offset(i, 0) = objFile.Name
                .Offset(i, 1) = Format(objFile.Size, "0,000") & " KB"
                .Offset(i, 2) = objFile.DateLastModified
                .Offset(i, 3) = objFile.DateLastAccessed
                .Offset(i, 4) = objFile.DateCreated
                .Offset(i, 5) = objFile.Path
                 
            End With
             
Skip:
             
             'Do nothing, just go to next file
        Next x
        wsNew.Columns("A:F").AutoFit
         
    End With
     
     
    Set objFolder = Nothing
    Set objFile = Nothing
    Set objFSO = Nothing
    Set wsNew = Nothing
    Set wbNew = Nothing
     
    Application.ScreenUpdating = True
End Sub
Function BrowseForFolder(Optional OpenAt As Variant) As Variant
     
    Dim ShellApp As Object
    Set ShellApp = CreateObject("Shell.Application"). _
    BrowseForFolder(0, "Select the Unprocessed Invoices folder", 0, OpenAt)
     
    On Error Resume Next
    BrowseForFolder = ShellApp.self.Path
    On Error GoTo 0
     
    Set ShellApp = Nothing
     
    Select Case Mid(BrowseForFolder, 2, 1)
    Case Is = ":"
        If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
    Case Is = "\"
        If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
    Case Else
        GoTo Invalid
    End Select
    Exit Function
     
Invalid:
     
End Function

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