Solved

Error with VBA Class in Excel

Posted on 2013-06-13
11
441 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
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

821 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