Parse email body to excel

[phanikumar007]

Hi,

I regularly receive emails in the following format

------------------------------------------------------
Invoked at 12/29/2009 17:30:01

=========================================
Report from Engine: web:XXX
=========================================
Cumulative sessions so far = 0
Most sessions at a time = 0
Currently active sessions = 0

=========================================
Report from Engine: web:YYY
=========================================
Cumulative sessions so far = 300
Most sessions at a time = 40
Currently active sessions = 39

=========================================
Report from Engine: web:ZZZ
=========================================
Cumulative sessions so far = 711
Most sessions at a time = 40
Currently active sessions = 39

=========================================
Report from Engine: web:AAA =========================================
Cumulative sessions so far = 714
Most sessions at a time = 41
Currently active sessions = 39
------------------------------------------------------

I would like to know if we can parse this message to get the data in excel though VBA in the following format

Date XXX YYY ZZZ AAA
12/29/2009 17:30:01 0 39 39 39

Any help is much appreciated.

Thanks
Phani

 

[SkipVought]

Hi,

Try using Select Case...End Select

'assume that your data is in Column A
dim r as range, lRow as long, iCol as integer, a

lRow = 1
for each r in range([A1], [A65536].end(xlup))
  select case left(r.value, 6)
    case "Invoke"
      icol = 1
      lRow = lRow + 1
    case "Report"
      a = split(r.value, ":")
      icol = cells(1,Trim(a(1)))
    case "Cumula"

    case "Most s"

    case "Curren"
      a = split(r.value, "=")
      Cells(lRow, iCol).value = Trim(a(1))
  end select
next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[phanikumar007]

Hi Skip,

Thanks for the reply, I am a newbie here and would need some help in using this code.

I receive the above data every hour in an email and i am planning to use Outlook VBA for running through an outlook folder which holds these emails and extract only the text against 'Invoked at' and 'Currently active sessions =' lines in to a spreadsheet. Each email will have 4 lines of 'Currently active sessions = ' and the folder can have about 24 mails daily as they run each hour.

Thanks
Phani

 

[SkipVought]

Are you running the code from Outlook or from Excel?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[phanikumar007]

Running it from Outlook

 

[SkipVought]

You must set an application object for Excel. Check VB Help for CreateObject.

I would also use Tools > References... to select the reference for the most recent Excel Object Library.

Check VB Help for CreateObject.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[phanikumar007]

I have used Microsoft Excel Object Library but the code does not work. Any idea what needs to be changed in this code.

 

[SkipVought]

but the code does not work

What code? Please post the code!

Does not work in what way: Never executes? Produces unexpected results? Produces no results?

When you reply, please be CLEAR, CONCISE and COMPLETE. No one else can see what is in your mind. YOU must communicate everything relevant.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}