move contents from hundreds of emails into excel

  • Thread starter Thread starter JSpence2003
  • Start date Start date
J

JSpence2003

I have hundreds of emails that got forwarded from a web form to my email
account. Each email has the same body contents, in that they all have this
basic format below (with different data after each colon). I would like to
try to get a script that can go though this folder, and take the data the
people provided and place it all into Excel rows/columns at one time.
Currently, I copy/paste it into Excel, then I have a macro that takes it from
its current format and moves the data into appropriate fields. This is great,
but can only be done, one email at a time.

Any suggestions?

Add_to_BW_Email_List:
Anniversary_Day:
Anniversary_Month:
BirthdayDay:
Birthday_Month:
address:
city:
email:
name:
state:
submit:
zip:
 
You can loop through the Items collection of the folder to access each item.
The data is found in the Body property, use the Instr, Left, Right, or Mid
functions to find and extract what you're looking for.

Add a reference to the Excel library to your Outlook VBA project via
Tools/References. Then use the object browser (f2), switch from <All
Libraries> to Excel and see what's available: For instance, Workbooks.Open
opens a workbbok. It has a Sheets collection, and there's a Range object for
working with cells.

--
Best regards
Michael Bauer - MVP Outlook

: Outlook Categories? Category Manager Is Your Tool
: VBOffice Reporter for Data Analysis & Reporting
: <http://www.vboffice.net/product.html?pub=6&lang=en>


Am Thu, 1 Oct 2009 13:07:04 -0700 schrieb JSpence2003:
 
hi all

just a thought if the body text is divided up by colons you might want to
look at split

BodyArray=split(Myitem.body,":")

this will give you array each element will be the text between the colons

regards



Pete
 
Back
Top