find specific Subject line from outlook and copy the content of the mail body to excel

[narsing18]

I have group mail box in we receive mails frequently with Subject line
"Request ID 691941: Call Lodged", here 691941 keep changing with request coming in mail box and remaining will be same.

what I would like to do is;

1. My Macro should keep reading the group mail box when ever it sees a new mail with only subject line contains "Request ID xxxxxx: Call Lodged " remaining mails can be ignored
2. from mail body it should copy only these fields to excel.
   
   i) Request ID xxxxxx (in this only xxxxxx ( Numeric number) should be copied to Excel)
   
   ii) Severity Level: Sev2 (in this only Sev2( this fiels keep changing like Sev1 or Sev3) should be copied to Excel)
   
   iii) Product: FINCORE (in this only FINCORE(This filed keep changing) should be copied to Excel)
   
   iv) Customer:FINATS (in this only FINATS(This field keep changing) should be copied to Excel)
   
   v) Date & Time : when this mail was received date and time

this should be copied in Excel in specified columns.

 

[jigar1276]

1. Are you using Outlook or any other web email client?
2. Sample email will be helpful to determine the subject line and contents in body.
3. Which are the columns in excel where you want the info to be posted?

 

[narsing18]

1. Are you using Outlook or any other web email client?
2. Sample email will be helpful to determine the subject line and contents in body.
3. Which are the columns in excel where you want the info to be posted?

Hi,

1. I am using Outlook 2016
2 . Sample email body (find below)
=================================================================
Request ID 692248: Call Lodged


To:xyzlksdksdk@skdmsd.com


cc:xyzlksdksdk@skdmsd.com




Dear Finacle Service Team,


Request ID 692248 is Lodged.
Requester: sjdhjksdj
Severity Level: Sev3-Some Impact
Request Status: With Assignee
Problem Description : Dear xyz, sdlksdjksdlksjdlksd lkjdfklsdjfksdjf klkldsfksdfklsdfkldfkl
Product: FINCORE
Customer: sjdskdjaskldasd


View: Intranet | Internet
=================================================================
Here first line is the subject line , 2nd & 3rd lines are the To and CC, Remaining is the mail body
in mail body 692248 number keeps changing and all values after : will keep changing so what ever is there after : should be captured

3.

S.no

Request ID

Severity Level

Product

Customer

Lodged Date &Time

<tbody>
</tbody>

A4 B4 C4 D4 E4 F4


the column should start at B4 with s.no starting at A4( S.no is just to count the records which is not there in mail body)

Hope this clarifies.