Solved

Error with VBA Class in Excel

Posted on 2013-06-13
11
443 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Way to transfer 29 62
Create email hyperlinks from regular text 3 29
Office 365 adding a domain 3 34
Last "space" before nth "period" in a string 18 54
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

734 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