Hello,

My client receives emailed shipping orders sent via Goldmine software into
her Outlook 2003 user-named folder under the Personal Folders tree named
"WHEE".
(The product names and folders are spelled wrong to protect the innocent)

In the body of each email, the orders appear in a two-column table. The
format never changes, i.e.,

First Column =

BILL TO INFORMATION
Name: Lottie Dah
Address: 2222 Friggin Deal
City: Big Whoop State: BO
Phone: (222)456-1234


CREDIT CARD INFO:
Name on Card: Lottie Dah
Method of Pymt: Mastercard
Cred Card #: ################
Exp Date: ####
CVV/CVC: ###

ORDER INFORMATION:
Item 1: Super Duper Rocket To The Moon Vitamins
Qty 1: 1
Price 1: 59.76
Total 1: 59.76
Item 2: Relax After Your Landing - Green Tea
Qty 2: 1
Price 2: 35.73
Total 2: 35.73

Item 3: Bacon-Flavored Lactose Intolerance Formula
Qty 3: 1
Price 3: 25.00
Total 3: 25.00

Second Column =

ORDER INFORMATION CONTINUED:
Item 4: Sucker Punch Drink Mix
Qty 4: 1
Price 4: 5.95
Total 4: 5.95

Item 5: Veins of Tungsten Iron Tablets
Qty 5: 2
Price 5: 23.00
Total 5: 46.00

Item 6: Blow N Blast Colon Cleanser
Qty 6: 1
Price 6: 33.00
Total 6: 33.00

My client uses a software called E-Grabber to transfer the data into Access.
The only problem is, the software only regards the first Item data. Her
workers then have to manually enter the other remaining 5 records.

I've been looking diligently through the internet and have found many
"almosts" using vba to guide me into extracting the entire needed data from
the email body through the WHEE folder and exporting into Access.

I'm sure it's out there, but I've not hit upon it yet.

Many thanks in advance.

Parsing the message body properly will require diligent use of VBA string
procedures, like InStr, Mid, Left, Right, Split, etc.

You can also save the e-mail to a .txt file and use the FileSystemObject and
TextStream Microsoft Scripting Runtime library. The TextStream object has a
handy ReadLine method that can make parsing a lot easier.

However, if you alter your e-mails to include the relevant data in a
comma-delimited format as an attachment, your efforts in migrating this
information into a database will be significantly decreased.

--
Eric Legault (Outlook MVP, MCDBA, MCTS: Messaging & Collaboration)
Try Picture Attachments Wizard for Outlook:
http://www.collaborativeinnovations.ca
Blog: http://blogs.officezealot.com/legault/


"Gwhit" wrote:

Hello,

My client receives emailed shipping orders sent via Goldmine software into
her Outlook 2003 user-named folder under the Personal Folders tree named
"WHEE".
(The product names and folders are spelled wrong to protect the innocent)

In the body of each email, the orders appear in a two-column table. The
format never changes, i.e.,

First Column =

BILL TO INFORMATION
Name: Lottie Dah
Address: 2222 Friggin Deal
City: Big Whoop State: BO
Phone: (222)456-1234


CREDIT CARD INFO:
Name on Card: Lottie Dah
Method of Pymt: Mastercard
Cred Card #: ################
Exp Date: ####
CVV/CVC: ###

ORDER INFORMATION:
Item 1: Super Duper Rocket To The Moon Vitamins
Qty 1: 1
Price 1: 59.76
Total 1: 59.76
Item 2: Relax After Your Landing - Green Tea
Qty 2: 1
Price 2: 35.73
Total 2: 35.73

Item 3: Bacon-Flavored Lactose Intolerance Formula
Qty 3: 1
Price 3: 25.00
Total 3: 25.00

Second Column =

ORDER INFORMATION CONTINUED:
Item 4: Sucker Punch Drink Mix
Qty 4: 1
Price 4: 5.95
Total 4: 5.95

Item 5: Veins of Tungsten Iron Tablets
Qty 5: 2
Price 5: 23.00
Total 5: 46.00

Item 6: Blow N Blast Colon Cleanser
Qty 6: 1
Price 6: 33.00
Total 6: 33.00

My client uses a software called E-Grabber to transfer the data into Access.
The only problem is, the software only regards the first Item data. Her
workers then have to manually enter the other remaining 5 records.

I've been looking diligently through the internet and have found many
"almosts" using vba to guide me into extracting the entire needed data from
the email body through the WHEE folder and exporting into Access.

I'm sure it's out there, but I've not hit upon it yet.

Many thanks in advance.