Using API's to bring Outlook GAL dialog to the front when calling from Access VBA

[AOB]

Hi guys,

I've written a function which opens the Outlook GAL dialog, allows users to select recipients and passes back a 2-D array (recipients and type i.e. To, CC or BCC)

Here is the code :

Public Function GetContactsFromOutlookGAL() As Variant
 
    Dim appOutlook As Object              ' Outlook Application
    Dim objNameSpace As Object            ' Outlook NameSpace
    Dim objSelectNamesDialog As Object    ' Outlook Select Names Dialog
 
    Dim arrRecipients() As Variant
    Dim itm As Variant
 
    ' Initialise the recipient array
 
    ReDim arrRecipients(1 To 2, 1 To 1)
 
    ' Connect to MS Outlook
 
    Set appOutlook = CreateObject("Outlook.Application")
    Set objNameSpace = appOutlook.GetNamespace("MAPI")
    Set objSelectNamesDialog = objNameSpace.GetSelectNamesDialog
 
    With objSelectNamesDialog
 
        .Caption = "Select recipients"
        .ShowOnlyInitialAddressList = True
 
        If .Display Then
 
            For Each itm In .Recipients
 
                ' Redimension recipient array
 
                If Not IsEmpty(arrRecipients(1, UBound(arrRecipients, 2))) Then _
                ReDim Preserve arrRecipients(1 To 2, 1 To UBound(arrRecipients, 2) + 1)
 
                arrRecipients(1, UBound(arrRecipients, 2)) = itm.Name
                arrRecipients(2, UBound(arrRecipients, 2)) = itm.Type
 
            Next itm
 
        End If
 
    End With
 
    Set objSelectNamesDialog = Nothing
    Set objNameSpace = Nothing
    Set appOutlook = Nothing
 
    GetContactsFromOutlookGAL = arrRecipients
 
End Function

The only problem I have is that when I 'display' the SelectNamesDialog, it doesn't make it the active window and bring it to the front - it just opens the dialog in Outlook and you have to navigate to the main Outlook window to find it.

I'd like for the dialog to 'popup' - ideally, without the main Outlook window dragging along behind

I believe it's possible (judging from this article) to achieve this using API's - specifically, a combination of FindWindow (to retrieve the window handle) and ShowWindowLong (to set it to a topmost window in front of all other windows)

But I'm not great at these API functions and have really only dabbled briefly in them previously.

Anybody got any suggestions as to how I could do this? These may not even be the best / most appropriate API's, I just saw them in that article and figured they sounded right. But struggling to put them together.

Thanks!

Al

 

[Bilbo_Baggins_Esq]

hmmm.

I've done some work with a few of these APIs and I'll read up on this one.

In the mean time, have you tried AppActivate?

Look it up in the VBA Library and see if that might work for you.

 

[AOB]

Thanks Bilbo

Yeah I'm familiar with AppActivate and have found it useful in other situations (mainly Shell'ing another application entirely, such as Acrobat)

But I don't think AppActivate is an option here as the dialog isn't available to activate until after .Display is fired. And as soon as it is fired, focus moves away from Access to Outlook - the code pauses until focus is returned by clicking OK or Cancel in the dialog.

But my understanding is that with the API, you can change the window properties of the dialog box before you actually go to display it. Then, when the .Display command fires, it is already prepped to move to the front, whereupon users can select recipients from the GAL and return control to Access to finish the script.

That's my understanding based on that article anyway; remember, I'm still a bit raw on API's. Personally, I don't really 'get' how you could retrieve a window handle for a window that hasn't technically opened yet. I don't know (that's why I'm on here!)

Thanks in advance for your efforts!

Al

 

[Bilbo_Baggins_Esq]

Personally, I don't really 'get' how you could retrieve a window handle for a window that hasn't technically opened yet. I don't know (that's why I'm on here!)

Well, therein lies the problem and that particular challenge is discussed in more detail further down the thread.

And, that in particular is why I'm tempted to think AppActivate might actually work.

If, for example, all you do to correct the problem (as it is now) is to select the Outlook instance on the task bar, that essentially is what AppActivate should do.
I'm wondering if you AppActivate before you fire the dialog, then when you fire it, the dialog should open on top.

anyway, just a thought and if it works, it will be a WHOLE lot easier than working in the API call process.

 

[AOB]

Okay thanks Bilbo, let me give it a whirl and see what happens

 

[Bilbo_Baggins_Esq]

If that doesn't work, I did a search using "FindWindow() VBA" in Bing and came up with quite a few good articles and threads that may be useful and adaptable for you.

In particular, this one:
http://www.everythingaccess.com/tut...external-application-window-to-the-foreground

 

[AOB]

Okay here is my first attempt :

With objSelectNamesDialog
 
    .Caption = "Select recipients"
    .ShowOnlyInitialAddressList = True
 
    [COLOR=red]AppActivate .Caption[/COLOR]
 
    If .Display Then
 
        For Each itm In .Recipients
 
            ' Redimension recipient array
 
            If Not IsEmpty(arrRecipients(1, UBound(arrRecipients, 2))) Then _
            ReDim Preserve arrRecipients(1 To 2, 1 To UBound(arrRecipients, 2) + 1)
 
            arrRecipients(1, UBound(arrRecipients, 2)) = itm.Name
            arrRecipients(2, UBound(arrRecipients, 2)) = itm.Type
 
        Next itm
 
    End If
 
End With

Jumps to my error handler at the line in red with the following :

Err 5
Invalid procedure call or argument

I wasn't sure whether this was because the window wasn't available to activate yet OR if .Caption was insufficient to adequately identify the window - the reason being, no matter what caption string I set via the object properties, the visible window shows that caption followed by " : Global Address List"

But that extra string doesn't appear in the .Caption property so thought that may be what's causing the problem. So I tried again, concatenating that extra piece onto the end, but still got the same error.

So I think AppActivate is not going to work here unfortunately...

 

[AOB]

Cool, thanks for that link Bilbo, let me do some 'light reading'...

 

[AOB]

Hi Bilbo

No dice unfortunately...

Public Function GetContactsFromOutlookGAL(strReportName As String) As Variant
 
    Dim objSelectNamesDialog As Object
 
    With objSelectNamesDialog
 
        .ShowOnlyInitialAddressList = True
 
        [COLOR=red]If Not FnSetForegroundWindow(.Caption) Then GoTo Exit_GetContactsFromOutlookGAL[/COLOR]
 
        If .Display Then
 
            ....

The FnSetForegroundWindow function returns False and the code exits

It seems it falls over for the same reasons that AppActivate won't suffice (i.e. the window hasn't been activated yet, therefore the EnumWindowProc function never finds the dialog window?

I added a Debug.Print line to the EnumWindowProc function just to be sure but I can't see anything that would relate to the Outlook dialog :

Private Function EnumWindowProc(ByVal hwnd As Long, _
                               lParam As FindWindowParameters) As Long
 
    Dim strWindowTitle As String
 
    strWindowTitle = Space(260)
 
    Call GetWindowText(hwnd, strWindowTitle, 260)
 
    strWindowTitle = TrimNull(strWindowTitle)      ' Remove extra null terminator
 
    [COLOR=red]Debug.Print strWindowTitle[/COLOR]
 
    If strWindowTitle Like lParam.strTitle Then
 
        lParam.hwnd = hwnd                         ' Store the result for later
        EnumWindowProc = 0                         ' This will stop enumerating more windows
 
    Else
 
        EnumWindowProc = 1
 
    End If
 
End Function

Any more suggestions?...

Thanks

Al

 

[AOB]

Hey Bilbo,

Having messed around with this for a while, I think activating the main Outlook window just prior to displaying the Select Names dialog is the easiest solution :

Dim appOutlook As Object
Dim objNameSpace As Object
Dim objSelectNamesDialog As Object
 
Set appOutlook = CreateObject("Outlook.Application")
Set objNameSpace = appOutlook.GetNamespace("MAPI")
Set objSelectNamesDialog = objNameSpace.GetSelectNamesDialog
 
With objSelectNamesDialog
 
    .ShowOnlyInitialAddressList = True
    .Caption = "Choose recipients"
 
    [COLOR=red]AppActivate appOutlook.ActiveExplorer.Caption[/COLOR]
 
    If .Display Then
 
        ....

Only downside to this is (apart from the fact that the main Outlook window shows rather than just the dialog...), once the contacts have been selected from the GAL, the focus remains on Outlook.

I need to (re)activate Access so that the focus returns there?

I've tried the following but it has no effect :

AppActivate CurrentDb.Properties("AppTitle")

But it has no effect (Outlook remains the active window)

Any suggestions as to how I can return the user to Access automatically?

Thanks!

Al

 

[AOB]

Guys,

Stumbled upon a solution to this - and killed two birds with one stone in the process - hopefully others will find this useful...

The trick is to minimise the Outlook window before you activate the GAL. That way only the GAL window appears (the Outlook window remains minimised) And when the GAL is closed by the user, the next window down is Access (the Outlook window is still minimised)

Remarkably simple solution to the problem :

' Connect to MS Outlook
 
Set appOutlook = CreateObject("Outlook.Application")
Set objNameSpace = appOutlook.GetNamespace("MAPI")
Set objSelectNamesDialog = objNameSpace.GetSelectNamesDialog
 
' Minimise Outlook window (ensures only the GAL appears and not the full Outlook window space)
 
appOutlook.ActiveWindow.WindowState = 1
 
With objSelectNamesDialog
 
    .ShowOnlyInitialAddressList = True
    .Caption = "Choose recipients for '" & strReportName & "' "
 
    ' Activate Outlook GAL
 
    AppActivate appOutlook.ActiveExplorer.Caption

 

[vbaInet]

The same question was answered here:

http://www.access-programmers.co.uk/forums/showthread.php?t=267009

 

[AOB]

Thanks vbaInet - I still prefer my solution as it doesn't involve API's - not that there's anything wrong with them, I just try to avoid using them if I can 'do it myself'

Small addition to my own solution - I recently realised that this only minimises the current active window of Outlook - which may or may not be the 'parent' explorer. And even if it is, if there are other Outlook windows open (i.e. opened messages etc.) then they will also appear along with the GAL when activating.

To get around this, I also minimise these thusly :

Dim ins As Variant
 
With appOutlook
  For Each ins In .Inspectors
    ins.WindowState = 1
  Next ins
  .ActiveExplorer.WindowState = 1
End With

This effectively minimises all windows relating to Outlook along with the parent - so the GAL is the only window that pops up from Access.

Just thought I should share...