Need help with VBA script

hmattert · Mar 3, 2011

I have office 07 and need a script that will export information from the body of my email to excel.

The information is in the body like this

Header info

 Contact Us" request from xxxxx.com, dated Mar 03, 2011

11.111.11.111
Al noname
4000 Edge St.
<none>
Philadelphia PA 19136
(555) 555-5555
anemailaddy@email.com

My wife and I are intrested in a this or that product or we have this complaint.

I need to get the name, street address, city and state, the phone number, and the email address into an excel sheet all with their own columns.

the column headers would look as such
Name Street City/State Phone Email Comments

Any help at all would be greatly appreciated.

Thank you so much,
Heather

hmattert · Mar 4, 2011

Yes I did.

Ruddles · Mar 4, 2011

You are running this in Excel, aren't you?

hmattert · Mar 4, 2011

yes i am

Ruddles · Mar 4, 2011

If VBA is choking at Dim ws As Worksheet, that suggests it doesn't know anything about Excel. You didn't uncheck the Excel Object Library when you selected the Outlook Object Library by any chance?

hmattert · Mar 4, 2011

I just checked and it was checked.

Ruddles · Mar 4, 2011

You should have these references checked - the first four by default and the last one you checked manually:-

<TABLE style="WIDTH: 171pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=228><COLGROUP><COL style="WIDTH: 171pt; mso-width-source: userset; mso-width-alt: 8338" width=228><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 171pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=17 width=228>Visual Basic For Applications</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=17>Microsoft Excel 12.0 Object Library</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=17>OLE Automation</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=17>Microsoft Office 12.0 Object Library</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=17>Microsoft Outlook 12.0 Object Library</TD></TR></TBODY></TABLE>

Check the line with Dim ws As Worksheet in it for obvious errors (although it looked fine to me when you re-posted it). Go Compile > VBAProject and check that it's definitely that line that's complaining. Is it red and every other line is still black?

Okay, start again from scratch. Close Excel, then re-open it and create a new workbook. Paste the code into a new standard module and add a reference to Microsoft Outlook Objects Library (Tools > References). Check you have the five I've listed. Finally change the RootFolder to point to your mailbx name and run SelectOutlookMail.

I've just gone through this procedure and it worked perfectly first time.

If it still stops at the line Dim ws As Worksheet, send me a list of the references you have checked.

Need help with VBA script

hmattert

New Member

hmattert

New Member

Excel Facts

Ruddles

Well-known Member

hmattert

New Member

Ruddles

Well-known Member

hmattert

New Member

Ruddles

Well-known Member

Similar threads

Forum statistics

Share this page

Need help with VBA script

hmattert

New Member

hmattert

New Member

Excel Facts

Ruddles

Well-known Member

hmattert

New Member

Ruddles

Well-known Member

hmattert

New Member

Ruddles

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock