Extracting Data from MS Outlook Mail Messages

[Steve Wright]

Hi all,
I have a problem.

I am recieving a number of email messages from which I need to extract the
contents of the mail message body and import it into Excel. The data comes
from HTML forms that are Emailed to me.

The body of the Mail Message contains data in the following format:

jobno=100325
contract=RS-330
jobtype=Asphalt
completedate=20/03/2003

What I want to do is extract the data from the mail message and insert it in
to Excel.
The bit before the = should be the column name
Anyone know where I should start.

TIA
Steve

 

[zantor]

Hi Steve,

Do the emails all have the same subject lines / come from the same
sender?

Does the body contain only the format you specified and nothing more?

 

[keepitcool]

Following would work for PLAINTEXT messages. For HTML you'd need to
strip any tags found..

note the routine to loop/select messages from the inbox is rudimentary
and for illustration only.

You MUST set a reference to the Outlook Object Library.
Hope it gets you on your way



Option Explicit
Option Compare Text

Sub ReadInbox()
Dim appOL As Outlook.Application
Dim oSpace As Outlook.NameSpace
Dim oFolder As Outlook.MAPIFolder
Dim oItems As Outlook.Items
Dim oMail As Outlook.MailItem

Set appOL = CreateObject("Outlook.Application")
Set oSpace = appOL.GetNamespace("MAPI")
Set oFolder = oSpace.GetDefaultFolder(olFolderInbox)
Set oItems = oFolder.Items
oItems.Sort "Received", True
For Each oMail In oItems
If oMail.Subject Like "*BodyTest*" Then
Call bodyStrip(oMail)
End If
Next
End Sub

Sub bodyStrip(msg As Outlook.MailItem)
Dim sBody As String
Dim aFields As Variant
Dim r As Range
Dim n&, iPos1&, ipos2&

aFields = Array("jobno=", "contract=", "jobtype=", "completedate=")

Set r = [a65536].End(xlUp).Offset(1).Resize(, 4)
sBody = msg.Body

For n = 1 To 4
iPos1 = InStr(ipos2 + 1, sBody, aFields(n - 1))
If iPos1 > 0 Then
iPos1 = iPos1 + Len(aFields(n - 1))
ipos2 = InStr(iPos1 + 1, sBody, vbCrLf)
r(n) = Mid(sBody, iPos1, ipos2 - iPos1)
Else
Exit For
End If
Next
End Sub




keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Steve Wright]

Yes the subject lines are all the same but the message may come from four or
five different people.

The body contains only the specified format


Steve

 

[Steve Wright]

Works great thanks!!

Next question!

This works with the Inbox. I would like to make it work with a folder
called "RAMM" as I have a rule in outlook that transfers those messages
there.

Also I would like to "move" the message to another mailbox that I have
access to.

Any help appreciated

Steve

Following would work for PLAINTEXT messages. For HTML you'd need to
strip any tags found..

note the routine to loop/select messages from the inbox is rudimentary
and for illustration only.

You MUST set a reference to the Outlook Object Library.
Hope it gets you on your way



Option Explicit
Option Compare Text

Sub ReadInbox()
Dim appOL As Outlook.Application
Dim oSpace As Outlook.NameSpace
Dim oFolder As Outlook.MAPIFolder
Dim oItems As Outlook.Items
Dim oMail As Outlook.MailItem

Set appOL = CreateObject("Outlook.Application")
Set oSpace = appOL.GetNamespace("MAPI")
Set oFolder = oSpace.GetDefaultFolder(olFolderInbox)
Set oItems = oFolder.Items
oItems.Sort "Received", True
For Each oMail In oItems
If oMail.Subject Like "*BodyTest*" Then
Call bodyStrip(oMail)
End If
Next
End Sub

Sub bodyStrip(msg As Outlook.MailItem)
Dim sBody As String
Dim aFields As Variant
Dim r As Range
Dim n&, iPos1&, ipos2&

aFields = Array("jobno=", "contract=", "jobtype=", "completedate=")

Set r = [a65536].End(xlUp).Offset(1).Resize(, 4)
sBody = msg.Body

For n = 1 To 4
iPos1 = InStr(ipos2 + 1, sBody, aFields(n - 1))
If iPos1 > 0 Then
iPos1 = iPos1 + Len(aFields(n - 1))
ipos2 = InStr(iPos1 + 1, sBody, vbCrLf)
r(n) = Mid(sBody, iPos1, ipos2 - iPos1)
Else
Exit For
End If
Next
End Sub




keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

Hi all,
I have a problem.

I am recieving a number of email messages from which I need to extract
the contents of the mail message body and import it into Excel. The
data comes from HTML forms that are Emailed to me.

The body of the Mail Message contains data in the following format:

jobno=100325
contract=RS-330
jobtype=Asphalt
completedate=20/03/2003

What I want to do is extract the data from the mail message and insert
it in to Excel.
The bit before the = should be the column name
Anyone know where I should start.

TIA
Steve

 

[keepitcool]

sorry for slow response


read for plenty of info on working with outlook.



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >