Outlook GAL fails to be top-most window when called from VBA UserForm

Question

i have about ten responses from StackOverflow open but none of them quite answer my problem.

i have created several UserForms in Excel VBA for this particular project. (Note: i have no formal training on VBA programming and everything i have done is self-taught or gleaned from copying other people's codes.) While interfacing with more than one of these forms, i want the user to be able to access a command to select a user-name from the company's Global Address List. With a command button on the form and the following function i am able to do this:

Public Function GetUsernameFromOutlook(sCap As String) As String
'fancy code to call Outlook dialog box to select names. 
'Badresult is the default, gives username of operator if they try to:
'   select more than one recipient
'   cancel out of the dialog box

Dim olApp As Object     ' Outlook.Application
Dim olDialog As Object  ' Outlook.SelectNamesDialog
Dim hwnd As Long

Set olApp = CreateObject("Outlook.Application")
Set olDialog = olApp.Session.GetSelectNamesDialog

With olDialog
    .Caption = sCap
    .ForceResolution = True
    .AllowMultipleSelection = False
    .NumberOfRecipientSelectors = olShowTo
    .ToLabel = "Select User"
    If .Display = False Then GoTo BadResult
    SetForegroundWindow (Excel.Application.hwnd)
    If .Recipients.Count <> 1 Then GoTo BadResult
    'Debug.Print .Recipients(1).Name
    'Debug.Print .Recipients(1).Address
    'Debug.Print .Recipients(1).AddressEntry.GetExchangeUser.Alias
    GetUsernameFromOutlook = .Recipients.Item(1).AddressEntry.GetExchangeUser.Alias
End With

 '   hwnd = FindWindow(vbNullString, sCap & ": Global Address List")


Set olApp = Nothing
Set olDialog = Nothing

Exit Function

BadResult:
    SetForegroundWindow (Excel.Application.hwnd)
    GetUsernameFromOutlook = Environ("UserName")

End Function

As you can see i attempted to use the SetForegroundWindow and FindWindow API calls as suggested in other answers. But the code doesn't even reach these lines before causing the problem.

The line If .Display = False brings up the SelectNamesDialog box from Outlook, but because my UserForm is modal (i think), it stays as the visible window. i am forced to use Alt-Tab to switch to Outlook. Then, after either selecting a name or cancelling out of the Outlook dialog box, i need to Alt-Tab again to get back to Excel.
Also, because the code is waiting for a response from the Outlook box, there is no further code execution, so SetForegroundWindow doesn't even happen until i complete all of the Alt-Tab switching.

Other solutions posted have referred to using calls to MSWord, or looking up information from or saving to a spreadsheet. i'm trying to use this call to modify the caption or text of a form control, such as a command button or text box or text label. i only need to collect the Outlook alias, since i have another function which can collect other selected information from Outlook based on the alias, so the alias is saved in a tag (unseen) on the form and converted to full name, initials or e-mail address as needed using this other function.

So far everything works great and i'd really like to release this interface to my beta-testers, but i don't want to have to explain to everyone to use Alt-Tab after they click the "select name" button. They will believe their computer has locked up and do a hard re-start. (Or call IT who will start asking questions that they cannot answer.)

i'm sorry that this question is so long, but i wanted to include as much information as possible. i'm sure there will be things i need to clarify, so please send me your questions in a response and i will do my best to explain better. Thank you for your time.

Answer 1

I just spent an evening on this, so even if this thread is one year old it should help. You should just try using:

"olApp.ActiveWindow.Activate"

It sums up to this fully working function:

enter Public Function GetUsernameFromOutlook(sCap As String) As String
'fancy code to call Outlook dialog box to select names.
  'Badresult is the default, gives username of operator if they try to:
'   select more than one recipient
'   cancel out of the dialog box

Dim olApp As Outlook.Application    ' Outlook.Application
Dim olDialog As Outlook.SelectNamesDialog
Dim hwnd As Long

Set olApp = New Outlook.Application
Set olDialog = olApp.Session.GetSelectNamesDialog
'Set olDialog = new Outlook.Application
With olDialog
.Caption = sCap
'.ForceResolution = True
.AllowMultipleSelection = False
.NumberOfRecipientSelectors = olShowTo
.ToLabel = "Select User"

olApp.ActiveWindow.Activate
 .display
If .Recipients.Count <> 1 Then GoTo BadResult
'Debug.Print .Recipients(1).Name
'Debug.Print .Recipients(1).Address
'Debug.Print .Recipients(1).AddressEntry.GetExchangeUser.Alias
GetUsernameFromOutlook = .Recipients.Item(1).AddressEntry
End With


Set olApp = Nothing
Set olDialog = Nothing

Exit Function

BadResult:

GetUsernameFromOutlook = "A voir ultérieurement"

End Function here

Answer 2

Outlook Object Model does not let you specify the parent window of the address book dialog - it will always be Outlook.

On the Extended MAPI level (C++ or Delphi), you can specify the window handle when calling IAddbook::Address, but you cannot do that from VBA.

If using Redemption (I am its author) is an option, you can set the RDOSession.ParentWindow property before using the RDOSelectNames object.

set Session = CreateObject("Redemption.RDOSession")
Session.MAPIOBJECT = Application.Session.MAPIOBJECT
Session.ParentWindow = Excel.Application.hwnd
set ABDialog = Session.GetSelectNamesDialog
ABDialog.Display true