get perticular data from outlook mail into excel using VBA

[kulkasac]

Everyday I am receiving hundreds of mail containing header data and data about active users and concurrent users. I need to extract header information,active user and concurrent user data into excel from all mails. The data should append. For this I want a macro which will do this task.
Note: I kept all these mail into one folder. Plese see example of data
********Header information*******
ABC
PQRSTUVDF
123456
********End of Header information*******
---------------
--------------
----------------

Active Users 300

-------------------------
------------------------

Concurrent Users 10

----------------------------

Please help me!!! Thanks in advance....

[JP2112]

The Instr Function returns a number indicating the position of found text in a string. If you assign the Body property of a MailItem to a string variable, you can parse it for the text you need.

For ex:


Dim Msg As Outlook.MailItem
Dim strMsgBody As String
Set Msg = ActiveExplorer.Selection.Item(i)
strMsgBody = Msg.Body

' do your parsing here:
Dim lActiveUsersStart As Long
Dim lActiveUsersEnd As Long
Dim lLastChar As Long
Dim lNumberOfActiveUsers As Long

lActiveUsersStart = Instr(strMsgBody,"Active Users")
lActiveUsersEnd = lActiveUsersStart + 13
lLastChar = Instr(lActiveUsersEnd,strMsgBody,vbCr)

lNumberOfActiveUsers = Instr(lActiveUsersEnd, strMsgBody, lLastChar - lActiveUsersEnd)


ps- if you receive hundreds of messages a day and need to append, you should consider using an Access database instead of Excel. Depending on your version of Excel, you will hit the row limit in a month or two.

HTH


Everyday I am receiving hundreds of mail containing header data and data about active users and concurrent users. I need to extract header information,active user and concurrent user data into excel from all mails. The data should append. For this I want a macro which will do this task.
Note: I kept all these mail into one folder. Plese see example of data
********Header information*******
ABC
PQRSTUVDF
123456
********End of Header information*******
---------------
--------------
----------------

Active Users 300

-------------------------
------------------------

Concurrent Users 10

----------------------------

Please help me!!! Thanks in advance....