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

Question

So, firstly, I am a newbie of VB working from scratch, but in the past I edited some code. The closest question I could find was this , but that was not quite the way I had hoped.

So, I use Outlook / Excel 2007, and I get a daily email containing some data in a fixed form. What I hope to do is set up a macro / Script that will search in my Outlook inbox, and then, based on the correct message object, it will look in the message body and extract certain parts into the 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 overall code structure or other similar examples would be greatly appreciated. I just want to start and, hopefully, figure out future exercises on my own. Thanks!

---

So thanks for the help! Basically, I got this work, I just couldn’t automatically update it when I receive a new message. I have a rule configured that moves the corresponding emails to their own folder, and I was able to configure an open macro that I can run, which pulls out all the data (for each letter) and uploads them to a CSV file.

I tried to adapt this macro in the example that you specified above, which should automatically start when I receive a new message, but have not succeeded so far. Password analysis should not change (and it definitely works in the start macro manually), so this is normal, it just runs the auto-update macro for a new message. Am I missing something? Here is what I have, which basically matches the example above, except for the new folder (and is a module of the class):

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 deal with your problem, since you are new to all of this, and VBA (Visual Basic for Applications) is the simplest and most compatible language for a specific problem.

You need to start by creating a new Outlook macro that runs whenever new mail arrives in your inbox.

Start by creating a new class module in Outlook (ALT-F11) and copy it 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 where you want to do some stuff. End If End If End Sub 

The next part is to open Excel and do whatever you want. Be sure to set the link to the excel object library using the "Tools: Links ..." menu item and select the Microsoft Excel xx.xx object library.

You will probably need the following code:

 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 will most likely be called from your myOlItems_ItemAdd method.

Some looking back to Google or stack overflows should give you enough guidance on how you can handle the actual data processing part for your Excel method.

Hope this is enough to get you started.