extracting mailitem.body text to Excel columns

  • Thread starter Thread starter Junoon
  • Start date Start date
J

Junoon

Hi,

I am trying to Compare names in first column of excel sheet with
objitem.SenderName,. IF Found, then copy the mailitem.body text into 3
columns viz, Location, EmpID & Shift-Time.

The mailitem.body has 3 lines of data viz.,

Location: value
EmpID: value
Shift-Time: value

For Each Mailitem, i want to COMPARE the senderName, if found, then
SPLIT the mailitem.body text at every '/n' (enter), then compare the
Labels (e.g. Location) with the Column headers (e.g. Location) & then
dump the corresponding value of Labels under the respective columns.

Can anyone help ASAP!
 
Am 16 Apr 2006 04:41:44 -0700 schrieb Junoon:

You can loop through the items with a For Each... and read their SenderName
property.

The easiest way to split the Body is to use the Split function. Split by
vbCRLF and it returns an array with one item for each row. You could then
look for the ":" in the array element with the Instr function and extract
the parts left and right from that with the Left and Mid functions.
 
hi Michael,

1] Yes i have looped thru the items using For Each...& able get the
mailitem.sendername (but with a Security dialog 1st time)....
but my main area of concern is to use the Split function & instr
functions.....
i am not able to split the lines & then compare....i am NEW to this....

Also, its possible that the persons mailing me would create empty lines
below, not necessary that they would write the above details on each
single line one below the other......for e.g. the 1st label: Location,
is an address label......

So a person could write his complete address in more than one lines &
also create a empty line space below the 1st label & start of with next
label....

like this.....

Location: 25, Evershine Apts,
Shining Lane,
Brightness City,
Malaysia- 400000

EmpID: 344654

Shift Time: 18:30-6:30

also, as per what you say, to search for ":" in the body, then Shift
Time would be again broken into Shift Time, 18,30,6,30..........i dont
know how to do that...

I just need the code to parse the Body properly & get the data into the
right header columns in Excel.

2] Also, if you look at my previous post.....you will get the code
(which is without the body parsing code)....

"Voting Buttons & Excel " in
microsoft.public.outlook.program_vba..........

3] since i am using voting buttons to get Drop time requests from
people, whenever i send a Voting mail (Drop time request mail), you
know that, whenever someone clicks on any of the voting button, you get
a dialog, which give 2 options, a) Send the response now, b) Edit the
response.....
The person choses b) Edit the response & then has to enter the above
data ie.
Location, EmpID, Shift Time.....
Is it possible to Send a mail with Voting buttons & a small 2 column -
Table
, wherein, person enters the data first into the table & then clicks on
the Voting Button of his choice (Drop Time buttons).....

4]Lastly, each persons Outlook mail address properties shows his EmpID
& other details.....How do i extract these from the person's
mail....????What is the Command or statement in the For Each...Next
loop.....

PLEASE HELP ME WITH THESE ASAP!
 
Am 19 Apr 2006 01:26:39 -0700 schrieb Junoon:

Simple sample for the Split function:

Dim v() as string
v=Split(MailItem.Body, vbCRLF)

v is now an array with one element for each row in the mail´s body. You can
loop though that array with:

Dim i as Long
For i=0 To UBound(v)
' do here somethign with v(i)
Next

If v(i)="" then skip that.

For the rest: There´s no single function to solve what you are looking for.
All you need (and can use) are the functions Instr, Left, Right and Mid.
They all are very good explained in the VBA help.

I.e. you need to know what you are looking for. E.g. if you find one line
starting with Shift Time then you know that a time formatted string follows.
Then again you must use the mentioned functions to extract the time before
the hyphen and the one after it.

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
-- www.vbOffice.net --

hi Michael,

1] Yes i have looped thru the items using For Each...& able get the
mailitem.sendername (but with a Security dialog 1st time)....
but my main area of concern is to use the Split function & instr
functions.....
i am not able to split the lines & then compare....i am NEW to this....

Also, its possible that the persons mailing me would create empty lines
below, not necessary that they would write the above details on each
single line one below the other......for e.g. the 1st label: Location,
is an address label......

So a person could write his complete address in more than one lines &
also create a empty line space below the 1st label & start of with next
label....

like this.....

Location: 25, Evershine Apts,
Shining Lane,
Brightness City,
Malaysia- 400000

EmpID: 344654

Shift Time: 18:30-6:30

also, as per what you say, to search for ":" in the body, then Shift
Time would be again broken into Shift Time, 18,30,6,30..........i dont
know how to do that...

I just need the code to parse the Body properly & get the data into the
right header columns in Excel.

2] Also, if you look at my previous post.....you will get the code
(which is without the body parsing code)....

"Voting Buttons & Excel " in
microsoft.public.outlook.program_vba..........

3] since i am using voting buttons to get Drop time requests from
people, whenever i send a Voting mail (Drop time request mail), you
know that, whenever someone clicks on any of the voting button, you get
a dialog, which give 2 options, a) Send the response now, b) Edit the
response.....
The person choses b) Edit the response & then has to enter the above
data ie.
Location, EmpID, Shift Time.....
Is it possible to Send a mail with Voting buttons & a small 2 column -
Table
, wherein, person enters the data first into the table & then clicks on
the Voting Button of his choice (Drop Time buttons).....

4]Lastly, each persons Outlook mail address properties shows his EmpID
& other details.....How do i extract these from the person's
mail....????What is the Command or statement in the For Each...Next
loop.....

PLEASE HELP ME WITH THESE ASAP!
Click to expand...
 
Back
Top