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

so first things first, i'm vb newbie working scratch have edited code in past. closest question find mine this one wasn't quite specific hoped.

so i'm using outlook/excel 2007 , receive daily email contains data in fixed form. hoping set macro/script search outlook inbox, , based on correct message subject, in body of message , extract portions excel worksheet.

i think vb best way based on knowledge, i'm not quite sure start. on general structure of code or other similar examples appreciated. looking started , figure out on own future exercises. thanks!

---

so help! i've got working, haven't been able automatically update when new message. have rule set moves relevant emails own folder, , able set public macro can run pulls data out (for every email) , dumps them .csv file.

i tried adapt macro example posted above should automatically run when receive new message, haven't succeeded yet. parse-ing of emails shouldn't change (and works in manually run macro), fine, it's getting auto-update macro run on new message. missing something? here i've got, same example above aside new folder (and class module):

public withevents myolitems outlook.items   public sub application_startup()     ' reference items in inbox. because myolitems declared    ' "withevents" itemadd event fire below.    set myolitems =  outlook.session.getdefaultfolder(olfolderinbox).folders("folderx").items   end sub  private sub myolitems_itemadd(byval item object)  dim objoutlook new outlook.application dim objnamespace outlook.namespace dim objfolder outlook.mapifolder dim objmail mailitem dim count integer dim mytitlepos integer dim mytitlelen integer dim myvarpos integer dim myvarlen integer dim strprice string dim stryear string dim myvarcrlf integer dim mydate date dim newlinetest string     ' check make sure outlook mail message, otherwise   ' subsequent code fail depending on type   ' of item is.    if typename(item) = "mailitem"    ' data processing , parsing done here  end sub 

vb easiest language work problem since new , vba (visual basic applications) simplest , interoperable language particular problem.

you'll want start creating new outlook macro fires whenever new mail arrives in inbox.

start creating new class module in outlook (alt-f11) , copy in following code:

public withevents myolitems outlook.items   public sub application_startup()     ' reference items in inbox. because myolitems declared    ' "withevents" itemadd event fire below.    set myolitems = outlook.session.getdefaultfolder(olfolderinbox).items  end sub   private sub myolitems_itemadd(byval item object)        ' check make sure outlook mail message, otherwise       ' subsequent code fail depending on type       ' of item is.       if typename(item) = "mailitem"          if item.subject = "my required subject line"          ' here's want stuff.          end if        end if   end sub 

the next part open excel , whatever stuff want do. sure establish reference excel object library using "tools:references..." menu item , selecting microsoft excel xx.xx object library.

you'll want code following:

private sub do_excel_stuff(mycontent object) dim myxlapp excel.application dim myxlwb excel.workbook      set myxlapp = new excel.application     set myxlwb = new excel.workbook       ' data processing here       set myxlwb = nothing     set myxlapp = nothing   end sub 

this called within myolitems_itemadd method.

some looking around on google or stack overflow should give enough pointers how might want handle actual data processing part excel method.

hope enough started.