?
Solved

Error with VBA Class in Excel

Posted on 2013-06-13
11
Medium Priority
?
448 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 93

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 93

Accepted Solution

by:
Patrick Matthews earned 800 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 1200 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 1200 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

752 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