Since I am not any great Visual Basic for Applications (VBA) programmer
I am probably missing something very simple here. I searched for an
answer in this group and some items that sound related don't seem to
apply to my problem.

So here it is...

I have a VBA program in an Excel spreadsheet. This program is creating
contacts in an Outlook folder and also putting some data in a worksheet
as it goes along. This program works great.

The little issue is under some conditions the program needs to issue a
message for the person to acknowledge (OK or CANCEL). The message box
does get generated but it does NOT display directly. The Outlook
window is the one that is opened at the time and if you don't notice it
the "Excel" on the task-bar is "blinking". You have to click on the
Excel application to get it to open and then you have the "msgbox"
being displayed.

I have tried to "activate" the Excel application but even in that case
the "msgbox"is not displayed unless I click on Excel.

So what is the trick I am missing? How do I get that "msgbox" to
display so my users don't have to "look for it"?

Thanks in advance for your help.

Bernie

Am 29 Mar 2006 13:19:57 -0800 schrieb Bernie:

Bernie, the solution is not as simple as you might think.

You need to use some Win32 APIs to bring the windows into the foreground and
back again. While the Excel Application object exposes its window handle
Outlook doesn´t, so you need to find it yourself.

This sample (code in Excel) works if not Word is the e-mail editor:

Private Declare Function SetForegroundWindowA Lib "USER32" Alias
"SetForegroundWindow" (ByVal hwnd As Long) As Long
Private Declare Function FindWindow Lib "USER32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Private Function GetInspectorHandle(ByVal sCaption As String) As Long
GetInspectorHandle = FindWindow("rctrl_renwnd32", sCaption)
End Function

Sub test()
Dim app As Object
Dim mail As Object
Dim hnd As Long

Set app = GetObject(, "outlook.application")
Set mail = app.createitem(0)
mail.display

' get mail window´s handle
hnd = GetInspectorHandle(mail.GetInspector.Caption)

' bring excel into foreground
SetForegroundWindowA Application.hwnd

MsgBox "hallo"

' bring mail window into foreground again
SetForegroundWindowA hnd
End Sub


--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
-- www.vbOffice.net --


Since I am not any great Visual Basic for Applications (VBA) programmer
I am probably missing something very simple here. I searched for an
answer in this group and some items that sound related don't seem to
apply to my problem.

So here it is...

I have a VBA program in an Excel spreadsheet. This program is creating
contacts in an Outlook folder and also putting some data in a worksheet
as it goes along. This program works great.

The little issue is under some conditions the program needs to issue a
message for the person to acknowledge (OK or CANCEL). The message box
does get generated but it does NOT display directly. The Outlook
window is the one that is opened at the time and if you don't notice it
the "Excel" on the task-bar is "blinking". You have to click on the
Excel application to get it to open and then you have the "msgbox"
being displayed.

I have tried to "activate" the Excel application but even in that case
the "msgbox"is not displayed unless I click on Excel.

So what is the trick I am missing? How do I get that "msgbox" to
display so my users don't have to "look for it"?

Thanks in advance for your help.

Bernie

Thanks, Michael. I was sure hoping for something simple. But since
the concern is to move focus to Excel then we might not have to worry
about getting back to Outlook.

Thanks for the quick response....we will give it a try.

Bernie