Vista - VBScript vs VBA for Outlook Project

I'm doing a feasibility study to determine the if it makes sense to use
automation to pull mailbox stats for our department. I've done several VBA
projects so I'm familiar with VBA. I've spun up on CDO and written some code
to pull sample data to see what we can do with it.

The test code goes through 3000 mailitems in an EAS archive and dumps the
values for 3 mapi and 10 outlook fields from each into Excel. If I can get
the stats my manager wants, I'll use an ODBC connection to dump the data
directly to a database table, essentially using VBA as the ETL language.
(Does this sound strange at all?)

The question is, can I accomplish the same thing with VBscript, or are there
solid reasons to stick with VBA? For instance I'm using on error handling to
test if the mapi field exists:

On Error Resume Next
Set CdoObjField = oMessage.Fields.Item(&H10820040)
If Err <> 0 Then
aryCdoActionDate(i) = Empty
aryCdoActionFlag(i) = Empty
Err.Clear

From what I've read, VBScript can't do that, but there may be an alternate
code, no?

I'm also using arrays to store the data before dumping it to a spreadsheet.
The code could just as easily write directly to the spreadsheet, and there
doesn't seem to be a performance hit either way. Finally, I know how to
access ODBC from VBA, and wonder if VBScript can do the same?

Any comments are appreciated

Thanks,
Jake

Vbscript's error handling is not as flexible as VBA (it does manage "on
error resume next", but can't do "on error goto...")
If you have Excel and you're already familiar with VBA, it's hard to think
of an argument to switch to vbscript, unless you just want the challenge of
a different environment.

Tim





"JakeK" <JakeK@xxxxxx> wrote in message
news:F4639782-D789-429A-8478-79746A8E3E96@xxxxxx

"JakeK" <JakeK@xxxxxx> wrote in message
news:F4639782-D789-429A-8478-79746A8E3E96@xxxxxxI don't use VBA, so I can't really compare. However, I read from Outlook,
write to MS Excel, write to databases, etc. in VBScript all the time. As
noted, VBScript supports the Err object, "On Error Resume Next", and "On
Error GoTo 0". You can connect to Excel with ODBC or Jet drivers and ADODB
objects, but I use the Excel.Application object. You may be able to copy
your code to a text file with *.vbs extension and run it as is. I run
administrative scripts at a command prompt with the cscript host. This way I
see all error messages and can redirect the output to a text file.

An simple example VBScript program that writes to Excel:

http://www.rlmueller.net/Write%20to%20Excel.htm

And an old program I used to manage my Outlook inbox:

http://www.rlmueller.net/DeleteSpam.htm

An example VBScript program to create an Access table and add a record:
==========
' Specify path and file name.
strPath = "C:\Scripts\MyDatabases"

' Connection string for MS Access database.
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& strPath & "\MyApplication.mdb"

' Connect to MS Access database.
Set adoCommand = CreateObject("ADODB.Command")
adoCommand.ActiveConnection = strConnect

' Drop table Customers if it exists.
adoCommand.CommandText = "DROP TABLE Customers"
On Error Resume Next
adoCommand.Execute
On Error GoTo 0

' Create table Customers.
adoCommand.CommandText = "CREATE TABLE Customers " _
& "(" _
& "CustomerID Integer NOT NULL, " _
& "CustomerName VARCHAR(30) NOT NULL, " _
& "CONSTRAINT CustomerID_pk " _
& "PRIMARY KEY (CustomerID)" _
& ")"
adoCommand.Execute

' Add a record to table Customers.
adoCommand.CommandText = "INSERT INTO Customers " _
& "(CustomerID, CustomerName) " _
& "VALUES(2031, 'ACME Industrial')"
adoCommand.Execute

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--

Hi

VBA is very focused on it's host applications and classes as a subset of
VB and therefore mostly suited for this task.

VBS is much more all-purpose, open and above all redistributable.

I allmost consider it a "no strings attached" programming technology from
MS. Rather rare.

Hope it will stay that way for a long time.

idle,
rusga


On Fri, 10 Jul 2009 23:24:01 +0100, JakeK
<JakeK@xxxxxx> wrote:

Thanks Tim - That confirms my first impressions.

"Tim Williams" wrote:

Richard,

I reviewed the code below and your online examples. It looks like VBS could
be made do a lot of the tasks, if we keep the code design simple and leave
the data analysis for the DB application.

Your suggestion about running the code at a command prompt is an especially
helpful idea - I can test the ODBC TD connector if and when we get to that
point. It will also help me to get familiar with scripting in general.

Thanks.
Jake Katz

"Richard Mueller [MVP]" wrote: