Solved

Error with VBA Class in Excel

Posted on 2013-06-13
11
439 Views
Last Modified: 2013-06-13
Option Explicit

Public BoolRange As Range
Public DateRange As Range
Public WithEvents TheMail As Outlook.MailItem


Private Sub TheMail_Send(Cancel As Boolean)
    If Not BoolRange Is Nothing Then
        BoolRange.Value = True
    End If
    If Not DateRange Is Nothing Then
        DateRange.Value = Now()
    End If
End Sub

Open in new window


Hi,

I am using the above code retrived from this site: http://stackoverflow.com/questions/2533066/vba-outlook-mail-display-recording-when-if-sent-manually

The above code is inside a class module called 'EmailWatcher'

However, whenever the code is executed, I am getting an error on this line

Public WithEvents TheMail As Outlook.MailItem

saying
'User defined type not defined'

Is there a way to correct this? If this requires enabling the outlook reference, is there a go-around? (I do not expect the (unknown) user to know how to enable outlook reference)

Thanks!
0
Comment
Question by:Shanan212
  • 5
  • 4
  • 2
11 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39245284
You have to include a reference to the Outlook application object model.

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39245293
From the VBE, select Tools->References, and find and check the Microsoft Outlook xx.0 Object Library.

Kevin
0
 
LVL 13

Author Comment

by:Shanan212
ID: 39245306
Thanks but I am looking to go around without using the reference since this is for our customers. If I use the reference, then the customers have to enable it too.

Is there anyway to do without enabling the reference?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39245321
What is the actual problem you are trying to solve?  That is, why are you trying to implement that code to begin with?

There may be more than one way to go about it :)
0
 
LVL 13

Author Comment

by:Shanan212
ID: 39245339
Ow, the actual problem is to track whether a user clicked 'send' button on an email or closed it (wihtout sending it)

The email is being displayed as such

With OutMail
.Display
End with

After googling, I found this to be recent and for outlook 2007. But since the file is going to users who may not have knowledge of 'enabling reference' I want to see if I can go around.

A sub to check 'sent' message is not an option as user can change the contents of the email AND if the user have slower network connection, it may appear real late (in the sent folder)
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 200 total points
ID: 39245395
The reference stays with the VBA Project, so if you are actually giving your users an Excel file they should be OK unless they are using an older version of Office than you are.
0
 
LVL 13

Author Comment

by:Shanan212
ID: 39245422
Matthew,

Not that I don't believe you but this goes against what I believed so far.

So you are saying that (considering versions are same) if I enabled a reference in my computer, they don't have to enable that reference in another computer to run the macro?

Thanks!
0
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 300 total points
ID: 39245432
References are created as part of the VBA project. They travel with the project and, as long as the user has that application installed, your code will function.

Kevin
0
 
LVL 13

Author Comment

by:Shanan212
ID: 39245492
Thanks

Quick small question

Set CurrWatcher = New EmailWatche

It says variable not defined on CurrWatcher. What should I define it as?

http://stackoverflow.com/questions/2533066/vba-outlook-mail-display-recording-when-if-sent-manually
0
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 300 total points
ID: 39245510
Dim CurrWatcher As EmailWatcher

Kevin
0
 
LVL 13

Author Closing Comment

by:Shanan212
ID: 39245516
Thanks both of you!
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question