CODEWITHSUNDEEP
excelvbaoutlook
Phil Skeldon
Phil Skeldon

Reputation: 11

How to use Outlook's Application_AdvancedSearchComplete event handler in Excel VBA?

I wrote VBA code in Outlook to use AdvancedSearch. It worked.

When I moved it to Excel to be part of a larger routine, the event handlers stopped working.

The main code looks something like this.

Public gblnProcessAttachmentsDone As Boolean

Public gblnProcessAttachmentsStopped As Boolean

Sub ProcessAttachmentsSub()

' this routine performs the advanced search on a folder

...
    
    gblnProcessAttachmentsDone = False
    gblnProcessAttachmentsStopped = False

...

    'perform search
    Set objSearch = objOL.AdvancedSearch(strScope, strFilter, True, "ProcessAttachments")
        
    Do Until gblnProcessAttachmentsDone
        DoEvents
    Loop

These are the event handlers.

Private Sub Application_AdvancedSearchComplete(ByVal SearchObject As Outlook.Search)

' this routine identifies the search that has just completed

    If SearchObject.Tag = "ProcessAttachments" Then
        Debug.Print "Search completed at " & Time
        gblnProcessAttachmentsDone = True
    End If

End Sub

Private Sub Application_AdvancedSearchStopped(ByVal SearchObject As Outlook.Search)

' this routine identifies the search that has just been stopped by the user

    If SearchObject.Tag = "ProcessAttachments" Then
        Debug.Print "Search stopped at " & Time
        gblnProcessAttachmentsStopped = True
        gblnProcessAttachmentsDone = True
    End If

End Sub

I tried placing them in 'ThisWorkbook' and a Class module, but in both cases the events never get caught.

Upvotes: 1

Views: 391

Answers (2)

Eugene Astafiev
Eugene Astafiev

Reputation: 49395

To start an Outlook Automation session, you can use either early or late binding. Late binding uses either the Visual Basic GetObject function or the CreateObject function to initialize Outlook. For example, the following code sets an object variable to the Outlook Application object, which is the highest-level object in the Outlook object model. All Automation code must first define an Outlook Application object to be able to access any other Outlook objects.

Dim objOL as Object 
Set objOL = CreateObject("Outlook.Application")

To use early binding, you first need to set a reference to the Outlook object library. Use the Reference command on the Visual Basic for Applications (VBA) Tools menu to set a reference to Microsoft Outlook xx.x Object Library, where xx.x represents the version of Outlook that you are working with. You can then use the following syntax to start an Outlook session.

Dim objOL as Outlook.Application 
Set objOL = New Outlook.Application

To handle Outlook Application-level events in external applications:

  1. First, you must declare a variable using the WithEvents keyword to identify the object whose event you want to handle.
Dim WithEvents objOL as Outlook.Application 
Set objOL = New Outlook.Application
  1. You can then select an Outlook application instance object in the Objects list of the module window and then select the event in the procedure list. The Visual Basic Editor will then add the template for the event procedure to the module window. You can then type the code you want to run when the event occurs.
Private Sub objOL_AdvancedSearchComplete(ByVal SearchObject As Outlook.Search)

' this routine identifies the search that has just completed

    If SearchObject.Tag = "ProcessAttachments" Then
        Debug.Print "Search completed at " & Time
        gblnProcessAttachmentsDone = True
    End If

End Sub

Read more about the AdvancedSearch method in the Advanced search in Outlook programmatically: C#, VB.NET article.

Upvotes: 1

Dmitry Streblechenko
Dmitry Streblechenko

Reputation: 66235

In Excel VBA, Application intrinsic variable points to Excel.Application, not Outlook.Application. Your event handler (Application_AdvancedSearchStopped) will not be automatically hooked up. Declare objOL with events and set up the event handler,.

Upvotes: 1

Related Questions