Solved

Error with VBA Class in Excel

Posted on 2013-06-13
11
438 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Compile Error 7 41
Consolidate xl 2010 worksheets with text 2 23
MS Excel IF AND OR statement 3 26
Help Updated Qtr 2 9
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

911 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

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now