Using VB/VBA to search Outlook messages and extract specific data into Excel worksheet

Question

So first things first, I'm a VB newbie working from scratch but have edited some code in the past. The closest question I could find to mine was this one but it wasn't quite as specific as I hoped.

So I'm using Outlook/Excel 2007 and I receive a daily email that contains some data in a fixed form. What I am hoping to do is set up a Macro/Script that will search my Outlook Inbox, and then based on the correct Message Subject, will look in the body of the message and extract certain portions into an Excel worksheet.

I think VB is probably the best way to do this based on my knowledge, but I'm not quite sure where to start. Any help on the general structure of the code or other similar examples would be much appreciated. Just looking to get started and hopefully figure it out on my own for future exercises. Thanks!

---

So thanks so much for the help! I've mostly got this working, I just haven't been able to get it to automatically update when I get a new message. I have a rule set up that moves the relevant emails into their own folder, and I was able to set up a public macro that I can run that pulls all the data out (for every email) and dumps them into a .csv file.

I tried to adapt that macro into the example you posted above that should automatically run when I receive a new message, but I haven't succeeded yet. The parse-ing of the emails shouldn't change (and definitely works in the manually run macro), so that is fine, it's just getting the auto-update macro to run on a new message. Am I missing something? Here is what I've got, which is basically the same as the example above aside from the new folder (and is a class module):

Public WithEvents myOlItems As Outlook.Items


Public Sub Application_Startup()

   ' Reference the items in the Inbox. Because myOlItems is declared
   ' "WithEvents" the ItemAdd event will fire below.
   Set myOlItems =  Outlook.Session.GetDefaultFolder(olFolderInbox).Folders("FolderX").Items


End Sub

Private Sub myOlItems_ItemAdd(ByVal Item As Object)

Dim objOutlook As New Outlook.Application
Dim objNameSpace As Outlook.NameSpace
Dim objFolder As Outlook.MAPIFolder
Dim objMail As MailItem
Dim count As Integer
Dim myTitlePos As Integer
Dim myTitleLen As Integer
Dim myVarPos As Integer
Dim myVarLen As Integer
Dim strPrice As String
Dim strYear As String
Dim myVarCRLF As Integer
Dim myDate As Date
Dim newLineTest As String


  ' Check to make sure it is an Outlook mail message, otherwise
  ' subsequent code will probably fail depending on what type
  ' of item it is.

  If TypeName(Item) = "MailItem" Then

  ' Data processing and parsing is done here

End Sub

Answer 1

VB is probably the easiest language to work with for your problem since you are new to all this and VBA (Visual Basic for Applications) is the simplest and most interoperable language for the particular problem.

You'll want to start by creating a new Outlook macro that fires whenever a new mail arrives in your inbox.

Start by creating a new class module in Outlook (ALT-F11) and copy in the following code:

Public WithEvents myOlItems As Outlook.Items


Public Sub Application_Startup()

   ' Reference the items in the Inbox. Because myOlItems is declared
   ' "WithEvents" the ItemAdd event will fire below.
   Set myOlItems = Outlook.Session.GetDefaultFolder(olFolderInbox).Items

End Sub


Private Sub myOlItems_ItemAdd(ByVal Item As Object)

      ' Check to make sure it is an Outlook mail message, otherwise
      ' subsequent code will probably fail depending on what type
      ' of item it is.
      If TypeName(Item) = "MailItem" Then

        If Item.Subject = "My Required Subject Line" Then

        ' Here's where you want to do some stuff.

        End If

      End If


End Sub

The next part is to open Excel and do whatever stuff it is you want to do. Be sure to establish the reference to the excel object library by using "Tools:References..." menu item and selecting Microsoft Excel xx.xx object library.

You'll probably want some code like the following:

Private Sub Do_Excel_Stuff(MyContent As Object)
Dim myXLApp As Excel.Application
Dim myXLWB As Excel.Workbook

    Set myXLApp = New Excel.Application
    Set myXLWB = New Excel.Workbook


    ' Do your data processing here


    Set myXLWB = Nothing
    Set myXLApp = Nothing


End Sub

This would likely be called from within your myOlItems_ItemAdd method.

Some looking around on Google or Stack Overflow should give you enough pointers about how you might want to handle the actual data processing part for your Excel method.

Hope this is enough to get you started.