Copying subject and body text of .msg files to excel

I receive around 40 emails per day, all in the following format:

Subject: "Team x.x - date"
Body text: "x files sent"

where x represents a number.

What I'm hoping to be able to do is drop all these emails into a folder (in .msg format), and use excel to look through them and extract the team number from the subject, and the number of files sent from the body text for each message.

Unfortunately, while I class myself as reasonably competent with excel vba, working with emails or .msg files is new to me, so I genuinely haven't a clue how to do this, or if it's even possible.

Can anyone help?

hi,

What email application are you using eg Outlook (/Express), Lotus Notes...?
If it's Outlook, did you know that you Outlook has its own object model which can be manipulated through VBA?
Ooopps - reread your post & ".msg" files suggests it's a MS application. Is it Outlook?
(Try searching for "outlook vba" & ?)
I think you'd be able to do this more effectively from the Outlook side (using defined Rules & VBA) & it may even make you reasonably competent with Outlook VBA

Here is a link to Ron DeBruin's site which is probably worth exploring for the excel side: http://www.rondebruin.nl/sendmail.htm

hth
Rob

Rather than Excel dragging the messages to a folder Outlook can push them.

There will be many others, but "Sue Mosher" is one Outlook MVP so it may pay to include her name in your Search terms.

Let us know how you go - I may see what I can come up with for you at some stage later in the week.

Good luck
Rob

Also probably worth looking through the Outlook Forum(s) here ... though worth noting that although trapping the occurrence of new mail is trivial determining the new mail item can itself be a bit of a pain (somewhat oddly)

The decision regards coding in OL and pushing data to XL or coding in XL and pulling data from OL will invariably depend upon the workflow specifics (ie run once a day, run when new mail received etc etc...)

brilliant - I'm pleased we were able to point you in the right direction
Thanks for marking the post as solved.


Rob