Extract specific information from the body of outlook mail to an Excel File using VBA

I have to extract specific information from the body of outlook mail to an Excel File in respective cells using VBA(macros).

The details are as under :



-----Original Message-----
From: [email protected] [mailto:[email protected]]
Sent: Tuesday, March 01, 2016 1:15 PM
To: [email protected]
Subject: Regualr Info of Product Release R225F - V12.0




do -while ofPath \""SN5-HL5\"" E:/Docs/Expro.ebook
do -while member \""Michael Blaha\"" E:/Docs/Expro.ebook
do -while nEXTgEN \""\"" E:/Docs/Expro.ebook
do -while Mandate \""\"" E:/Docs/Expro.ebook
do -while Tp \""AX\"" E:/Docs/Expro.ebook
do -while Attr \""\"" E:/Docs/Expro.ebook
do -while main \""beta\"" E:/Docs/Expro.ebook
do -while ver \""R225F\"" E:/Docs/Expro.ebook
do -while comp \""Cloude Based\"" E:/Docs/Expro.ebook
do -while date \""2016-02-26\"" E:/Docs/Expro.ebook
do -while onPreg \""CONVERT\"" E:/Docs/Expro.ebook
do -while onProg \""Complete\"" E:/Docs/Expro.ebook


The above texts are the part of outlook mail.



The requirements are as under:

The header information should be like this:

A -> If it encounters "main" in the body of body of mail, it should be replaced by "Obj Name" in new Excel file
B -> If it encounters "ver" in the body of mail, it should be replaced by "Obj Ver" in new Excel file
C -> If it encounters "Tp" in the body of mail, it should be replaced by "Specs" in new Excel file
D -> If it encounters "comp" in the body of mail, it should be replaced by "Comp Name" in new Excel file
E -> The header name for this cell should be "Release No." in new Excel file
F -> If it encounters "member" in the body of mail, it should be replaced by "Owner" in new Excel file
G -> If it encounters "date" in the body of mail, it should be replaced by "Obj Last Date" in new Excel file
H -> If it encounters "ofPath" in the body of mail, it should be replaced by "Location" in new Excel file
I -> If it encounters "onProg" in the body of mail, it should be replaced by "Progress" in new Excel file


After the headers are named in new excel file, we need to extract the data from body of the mail and put in respective cells.
A -> If it encounters "main" in the body of body of mail, then extract the text between first \""\"" (i.e., in our case it should be "beta" (\""beta\"")) and put "beta" in Cell A, i.e., under header "Obj Name"
B -> If it encounters "ver" in the body of body of mail, then extract the text between first \""\"" (i.e., in our case it should be "R225F" (\""R225F\"")) and put "R225F" in Cell B, i.e., under header "Obj Ver"
C -> If it encounters "Tp" in the body of body of mail, then extract the text between first \""\"" (i.e., in our case it should be "AX" (\""AX\"")) and put "AX" in Cell C, i.e., under header "Specs"
D -> If it encounters "comp" in the body of body of mail, then extract the text between first \""\"" (i.e., in our case it should be "Cloude Based" (\""Cloude Based\"")) and put "Cloude based" in Cell D, i.e., under header "Comp Name"
E -> Extract the last part of text from the Subject of mail (i.e., in our case it should be "V12.0" (Subject: Regualr Info of Product Release R225F - V12.0)) and put "V12.0" in Cell E, i.e., under header "Release Number"
F -> If it encounters "member" in the body of body of mail, then extract the text between first \""\"" (i.e., in our case it should be "Michael Blaha" (\""Michael Blaha\"")) and put "Michael Blaha" in Cell F, i.e., under header "Owner"
G -> If it encounters "date" in the body of body of mail, then extract the text between first \""\"" (i.e., in our case it should be "2016-02-26" (\""2016-02-26\"")) and put "2016-02-26" in Cell G, i.e., under header "Last Date"
H -> If it encounters "ofPath" in the body of body of mail, then extract the text **after** \""\"" (i.e., in our case it should be "E:/Docs/Expro.ebook" (\""SN5-HL5\"" E:/Docs/Expro.ebook)) and put "E:/Docs/Expro.ebook" in Cell H, i.e., under header "Location"
I -> If it encounters "onProg" in the body of body of mail, then extract the text between first \""\"" (i.e., in our case it should be "Complete" (\""Complete\"")) and put "Complete" in Cell I, i.e., under header "Progress"






Please help me with this!

Right - so that's your list of 'requirements'. What, exactly, do you require 'help' with?

I have to extract the details from the body of the mail and put in respective cells of excel sheet.

A -> If it encounters "main" in the body of body of mail, then extract the text between first \""\"" (i.e., in our case it should be "beta" (\""beta\"")) and put "beta" in Cell A, i.e., under header "Obj Name"
B -> If it encounters "ver" in the body of body of mail, then extract the text between first \""\"" (i.e., in our case it should be "R225F" (\""R225F\"")) and put "R225F" in Cell B, i.e., under header "Obj Ver"
C -> If it encounters "Tp" in the body of body of mail, then extract the text between first \""\"" (i.e., in our case it should be "AX" (\""AX\"")) and put "AX" in Cell C, i.e., under header "Specs"
D -> If it encounters "comp" in the body of body of mail, then extract the text between first \""\"" (i.e., in our case it should be "Cloude Based" (\""Cloude Based\"")) and put "Cloude based" in Cell D, i.e., under header "Comp Name"
E -> Extract the last part of text from the Subject of mail (i.e., in our case it should be "V12.0" (Subject: Regualr Info of Product Release R225F - V12.0)) and put "V12.0" in Cell E, i.e., under header "Release Number"
F -> If it encounters "member" in the body of body of mail, then extract the text between first \""\"" (i.e., in our case it should be "Michael Blaha" (\""Michael Blaha\"")) and put "Michael Blaha" in Cell F, i.e., under header "Owner"
G -> If it encounters "date" in the body of body of mail, then extract the text between first \""\"" (i.e., in our case it should be "2016-02-26" (\""2016-02-26\"")) and put "2016-02-26" in Cell G, i.e., under header "Last Date"
H -> If it encounters "ofPath" in the body of body of mail, then extract the text **after** \""\"" (i.e., in our case it should be "E:/Docs/Expro.ebook" (\""SN5-HL5\"" E:/Docs/Expro.ebook)) and put "E:/Docs/Expro.ebook" in Cell H, i.e., under header "Location"
I -> If it encounters "onProg" in the body of body of mail, then extract the text between first \""\"" (i.e., in our case it should be "Complete" (\""Complete\"")) and put "Complete" in Cell I, i.e., under header "Progress"