PDA

View Full Version : MS Outlook Mail body info to Excel



Scorpiko
05-30-2006, 08:27 PM
Hi,

I'm new to programming with outlook and vb but with simple background on C++ and Java.

Basically i'm doing a registration page for my company and i need to complie the info from the email the page sends to me into an excel file. Hundreds of people register per day and manually inserting the mail body information into each column is taking up almost all my time leaving me with no time to do anything else :(

Long story short, some of the information is as follows

1) The email will come with a Subject: "Registration" (without the "").
The program should scan through the subjects and only saves those with the correct subject.

2) Body of the mail is as follows:

======== Product ========
Product Type : xxxxx
Model Number : xxxxx
Serial number : xxxxx
Full Name : xxxxx
Contact Num : xxxxx
=======================

** the xxxxx is the information i need to save into the excel file

3) Need to save it to an excel file c:\Registration.xls. Auto update(ie append) the old data must NOT be deleted. And i believe that a single worksheet has a limit to how many it can accept will it be a problem?

4) The titles in Registration.xls will be titled according to the info's title.
ie. Product / Model / Serial / etc.

4) I'm using MS Outlook 2000 SR-1 (if this helps)

5) The mails should be placed into the trash/deleted item folder.

Sorry for the long post.
I couldn't find any post with this topic i might have missed it.
Many thanks to who ever that can end my misery.

Regards,
Scorp

Killian
05-31-2006, 03:49 AM
Hi and welcome to VBAX :hi:

You should be able to automate the whole process by combining an Outlook rule (to determine the emails to process) and some VBA to parse the body text and output to Excel.

First you need to write a routine that parses the body text of a mail item passed to it and output it to Excel

Then you can set up a rule that applies to mails with the subject "Registration"
Actions for the rule will be to "run a script" (the routine you've written) and then delete the item.

With the code, if you include a mailitem object as an argument to the routine you select as an action in rules, it will refer to the mailitem the rule is applied to.
There's a thread here (http://www.vbaexpress.com/forum/showthread.php?t=7858) that deals with a similar problem. You should just need to adjust the GetText function to work with your output.

Post back if you get stuck :)