Extracting data from an email message (or several thousand emails) [Exchange based]

Question

My marketing department, bless them, has decided to make a sweepstakes where people enter over a webpage. That is great but the information isn't stored to a DB of any sort but is sent to an exchange mail box as an email. Great.

My challenge is to extract the entry (and marketing info) from these emails and store them someplace more useful, say a flat file or CSV. The only saving grace is that the emails have a highly consistant format.

I am sure I could spend the time saving all the emails to files and then write an app to munge through them all but was hoping for a much more elegant solution. Can I programmatically access an exchange mailbox, read all the emails and then save that data?

Monteith

Answer 1

There's lots of different ways to get at the messages in an exchange mailbox, but since it seems this is something you're only going to want to run once to extract the data I'd suggest writing a VBA macro to run inside Outlook itself (having opened the exchange mailbox in question within Outlook). It's pretty easy to iterate through the mail items in a specific mailbox and read the body text from them. You can then write a text file with just the stuff you want.

Bewail

I will definately give it a shot. I'll report back. :) – Monteith

Answer 2

Here is the code I used....

Private Sub btnGo_Click()
  If ComboBox1.SelText <> "" Then
    Dim objOutlook As New Outlook.Application
    Dim objNameSpace As Outlook.NameSpace
    Dim objInbox As MAPIFolder
    Dim objMail As mailItem

    //Get the MAPI reference
    Set objNameSpace = objOutlook.GetNamespace("MAPI")

    //Pick up the Inbox
    Set objInbox = objNameSpace.GetDefaultFolder(olFolderInbox)
    For Each objFolder In objInbox.Folders
       If (objFolder.Name = ComboBox1.SelText) Then
          Set objInbox = objFolder
       End If
    Next objFolder

    //Loop through the items in the Inbox
    Dim count As Integer
    count = 1

    For Each objMail In objInbox.Items
       lblStatus.Caption = "Count: " + CStr(count)
       If (CheckBox1.Value = False Or objMail.UnRead = True) Then
          ProcessMailItem (objMail.Body)
          count = count + 1
          objMail.UnRead = False
       End If
    Next objMail
  End If
End Sub

Private Sub ProcessMailItem(strBody As String)
   Open "C:\file.txt" For Append As 1

   Dim strTmp As String
   strTmp = Replace(strBody, vbNewLine, " ")
   strTmp = Replace(strTmp, vbCrLf, " ")
   strTmp = Replace(strTmp, Chr(13) & Chr(10), " ")
   strTmp = Replace(strTmp, ",", "_")

   //Extra Processing went here (Deleted for brevity)
   Print #1, strTmp
   Close #1

End Sub

Private Function Strip(strStart As String, strEnd As String, strBody As String) As String
   Dim iStart As Integer
   Dim iEnd As Integer

   iStart = InStr(strBody, strStart) + Len(strStart)
   If (strEnd = "xxx") Then
      iEnd = Len(strBody)
   Else
      iEnd = InStr(strBody, strEnd) - 1
   End If

   Strip = LTrim(RTrim(Mid(strBody, iStart, iEnd - iStart)))
End Function


Private Sub UserForm_Initialize()
  Dim objOutlook As New Outlook.Application
  Dim objNameSpace As Outlook.NameSpace
  Dim objInbox As MAPIFolder
  Dim objFolder As MAPIFolder

  //Get the MAPI reference
  Set objNameSpace = objOutlook.GetNamespace("MAPI")

  //Pick up the Inbox
  Set objInbox = objNameSpace.GetDefaultFolder(olFolderInbox)

  //Loop through the folders under the Inbox
  For Each objFolder In objInbox.Folders
    ComboBox1.AddItem objFolder.Name
  Next objFolder
End Sub

Monteith

Cool. Glad you got it working and well done for posting the code so others can benefit later when this crops up in google years from now. – Bewail

In 2012, it popped up as the first result of a google search and was immediately useful - It made a tired guy looking to statistically analyse his mail flow very happy! :) – Sambar