Add event handler to an object? 3

[1DMF]

How do I add an event handler to an object in MS Access VBA?

I have

    Dim objFolder As Object
    Set objFolder = oApp.GetNamespace("MAPI").GetDefaultFolder(olFolderSentItems)
    AddHandler objFolder.ItemAdd

It won't compile because it appears AddHandler isn't VBA.

However, I seem unable to dimension the objFolder object 'WithEvents' ?

How do I add the event handler for the 'ItemAdd' event on the 'Sent Items' MAPI folder object objFolder?

Thanks,
1DMF

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[MajP]

Here is my simple clsWidget demo raising and trapping custom events in vba.

Private Const minDelay As Single = 0.15
Private mDelay As Single
Private mColor As Double
'Define your event
Public Event turnToBlack()

Public Property Get Color() As Long
  Color = mColor
End Property

Public Property Let Color(ByVal lngColor As Long)
 mColor = lngColor
End Property

Public Sub getRandomColor()
  Dim start As Single
  start = Timer
  Do While Timer < start + mDelay
   DoEvents
  Loop
 Select Case (Int(Rnd * 6))
 Case 0
   mColor = vbRed
 Case 1
   mColor = vbYellow
 Case 2
   mColor = vbGreen
 Case 3
   mColor = vbBlue
 Case 4
   mColor = vbWhite
 Case 5
  RaiseEvent turnToBlack
  mColor = vbBlack
 End Select
End Sub

Public Property Get delay() As Single
 delay = mDelay
End Property

Public Property Let delay(ByVal snglDelay As Single)
 If snglDelay > minDelay Then
   mDelay = snglDelay
 End If
End Property

Private Sub Class_Initialize()
 mDelay = minDelay
End Sub

Here is my form using a custom class with an event

'Declare with events
Public WithEvents objWidget As clsWidget

Public Sub testObj()
  Set objWidget = New clsWidget
  objWidget.delay = 0.25
End Sub

Private Sub cmd1_Click()
 Dim count As Integer
 For count = 1 To 50
  objWidget.getRandomColor
  cmd1.Caption = count
  Me.bx1.BackColor = objWidget.Color
  Next count
End Sub

Private Sub Form_Load()
 Call testObj
End Sub

Private Sub objWidget_turnToBlack()
 'trap event here
 MsgBox "widget is turning black"
End Sub

 

[MajP]

forgot to notate where the event is raised

 Case 5
  'Raise event here
  RaiseEvent turnToBlack
  mColor = vbBlack
 End Select

 

[1DMF]

Hi MajP,

That seems to be against a custom class object with a custom event

I need to add the 'AddItem' event handler to an object that is a MAPI namespace folder?

This is a built in event that is triggered by outlook when an item is added to the 'Sent Items' folder, not a custom event?

I'm at a loss how I declare that object 'WithEvents' ?

It seems you can do it if you declare the variable as follows..

Public WithEvents myItem As Outlook.MailItem

But that is using a bound variable type of 'outlook', and I use unbound late bindings so outlook.* is not availale

I've found this discussion

http://social.msdn.microsoft.com/Fo.../thread/ef132edd-061c-4ccc-8ec8-b77f2bdf1df0/

But it doesn't provide any code or examples on how to do it?


"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[MajP]

Sorry was not paying attention to what you where doing, but now you know how to raise custom events in vba.

So as you show you can declare an object with events and trap that throughout your application. This is a really useful trick for building custom classes that extend the functionality of controls in access. see this demoed in faq702-6304.

I have never tried to trap events from other automation objects. However, some good discussion on events in vba here and automation objects. The TekTip Forum:
VBA Visual Basic for Applications (Microsoft) FAQ Index
There is a four part FAQ that is good.


So now you have late binding which would make this even more difficult. Maybe you could try declaring it as an ojbect.

private withEvents myItem as object

 

[MajP]

 Public WithEvents xlSheet As Excel.Worksheet
 'Public withEvents xlSheet as object

Private Sub cmdCreateExcel_Click()
  'function that creates an automation object and returns a new worksheet
  Set xlSheet = mdlCreateExcel.CreateNewExcelSheet
End Sub

Private Sub xlSheet_SelectionChange(ByVal Target As Excel.Range)
  MsgBox "Change"
End Sub

The above code works fine if early bound. From Acess I open up an Excel workbook and can trap the worksheet events. However I can not declare a object of type "object" with events. So not sure how to do late binding or if you can.

 

[1DMF]

It won't let you set the 'WithEvents' against a standard 'Object' type variable.

In the VBA Editor, it errors when you try to end the line with...

Compile Error
Expected : identifier

Any idea why this is?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[1DMF]

Well I looked at MSDN and found this

http://msdn.microsoft.com/en-us/library/aty3352y.aspx

Which says you can't use WithEvents on a type of Object.

So does this mean in the second decade of the 21st century it is still impossible to use Access to write an application that sends and email and then gets the email the app just sent?


"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[1DMF]

Well I added outlook to the references just to check it would work if I did bind it and the event is not being called?

So why won't the event trigger?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[strongm]

I think we need to see the exact declaration, and the exact event you are hoping to see

 

[MajP]

It won't let you set the 'WithEvents' against a standard 'Object' type variable

Sorry if I was unclear, but that was what I tried to say.

However I can not declare a object of type "object" with events. So not sure how to do late binding or if you can.

Anyways if I get time I will try with Outlook. In theory it should work because I demonstrated that I can trap events from Excel as an automation object.

 

[1DMF]

Well I couldn't do any more testing from home, as the app is in Access 2010 format and I only have Access 2007 at home! ( I used a special Access 2010 only Nav Control )

However I have decided that I didn't want to go down the bound route anyway. I spent a lot of time a few years back refactoring all the apps to run via late bindings due to the mixed MS Office environment and so using bound objects isn't a feasible option. Though I have made progress coming at it from a different angle!

I have some test code I've been playing with and I now have the email sent , then retrieve it from the sentitems collection, then save it. It corectly saves as .msg and can be opened again in Outlook from the hard drive, including all attachments.

Now I know before you pick holes in the following code, it's rough and I need to tidy it up, I also need to work out the best way to check not just the drafts folder in case they didn't click send (which it does already), but also monitor the outbox (for emails that may take a while to actually send) without ending in an infinate loop while also not having to pause the app too often.

I am going to refactor it into the EmailWrapper class as it's currently just a button click event and will probably add a 'Save' method to the EmailWrapper passing in the file name and path (and include a delete method). I also want to add a progress indicator so the user can see what's going on!

Here's what I currently have

Private Sub Command0_Click()

On Error Resume Next

Dim oEmail As New EmailWrapper
Dim oApp As Object
Dim oItem As Object

oEmail.Subject = "test email - Ref(12345)"
oEmail.addRecip ("me@mydomain.com")
oEmail.Body = "this is an email body"
oEmail.DispEmail = True
oEmail.Send

' don't forget to implement progress window and add progress information

If vbYes = MsgBox("Are you happy?", vbYesNo) Then
    
    Set oApp = CreateObject("Outlook.Application")
        
    Sleep 3000
    
    Set oItem = oApp.GetNamespace("MAPI").GetItemFromID(oEmail.EntryID)

    If Not oItem Is Nothing Then
        If vbYes = MsgBox("You haven't sent the email, do you wish to send the email?", vbYesNo) Then
            oItem.Send
            Sleep 3000
        Else
            oItem.Delete
            MsgBox "Email has been deleted"
        End If
    Else

        Do While oItem.Subject <> oEmail.Subject
            Sleep 3000
            Set oItem = oApp.GetNamespace("MAPI").GetDefaultFolder(5).Items.GetLast
        Loop
            oItem.SaveAs "h:\Test.msg", 3
            MsgBox "Email Saved"
    End If
           
End If

Set oEmail = Nothing
Set oApp = Nothing
Set oItem = Nothing

End Sub

So far it seems to work and does what I need, and at least it isn't a 'bound to Outlook version xxxx' solution!

If you have any pointers or suggestions they would be appreciated.

Regards,
1DMF

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[strongm]

You could always create an ActiveX library that only contains a single object - a proxie that only implements the eventhandler. It can early bind, and you can early bid to it without impacting the late binding to Outlook

 

[1DMF]

I wouldn't know where to start to do that?

Plus doesn't that still make the app dependent on a component that is bound to specific version of outlook?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[strongm]

Early binding does not bind to a specific version per se. It binds to a version and, thanks to the magic of COM, all later versions (it is just earlier versions that cause a problem). The trick is to early bind to the earliest version you need to be compatible with. Of course this might not contain a bunch of new methods and properties that a later version has available, but this is not always an issue if you do not use those methods and properties.

However, I don't think we can easily get the idea I suggested to work in Access. I was thinking VB.

 

[1DMF]

Early binding does not bind to a specific version per se.

It does in Access doesn't it?

Well If I go to the references section in the VBA editor, it specifically only has an option for Outlook 14 (which is 2010), as that's what Office I have installed.

Maybe it works differently in VB.NET? - Which in the new year I will be getting to grips with and hopefully porting our apps to as I think I have outgrown Access!

Though I am also in the middle of learning Catalyst and starting to develop a new web app with this MVC framework as well as porting over a legacy system from standard 'spaghetti junction' procedural perl code!

So VB.NET isn't a priority and VBA will have to suffice for the time being!

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[strongm]

>Well If I go to the references section in the VBA editor, it specifically only has an option for Outlook 14

Yes - which will bind it to 14 (Outlook 2010) and (generally) continue to work with subsequent versions. Because what it is actually binding to is an interface rather than the application itself, and later versions implement the same interface, and extend it with new methods and properties (which you cannot use from the old interface, because they don't exist in the interface definition)

So, if you early bind to Outlook 2000, code will work Outlook 2000, Outlook XP, Outlook 2003, Outlook 2007, Outlook 2010, Outlook 2013 ...
It won't work with any version of Outlook prior to Outlook 2000.

 

[1DMF]

OIC, so If I had 2003 installed and bound it to that, those with later versions would be fine!

Oh well, I only have 2010 installed, so it's a non-starter, all software here is OEM, so as machines get replaced, we lose the old office software!

I've even been promised a new PC in the new year so I can have Windows 8, currently finding it a bit difficult to support members with an OS I haven't even seen yet!

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[PHV]

Bottom line, if you use early binding for a mixed deployment environment you have to develop with the older environment you have to deal with.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[1DMF]

Yeah, only it doesn't work when you have no control over the client machines and so it could be ANY version of Office!

I certainly don't want to devel with Office 97!

I'm liking some of the new things in Access 2010, so everyone runs with the Access 2010 Runtime and all Office integration is done via late bindings , which if I recall corrrectly it was your good self PHV who turned me onto it

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads