Excel VBA create new Oulook message with window always on top?

Attached file has macro which creates a new Outlook email message. It works, but the email window pops up in the background. Please provide the exact code to add (and where) so that the new message always pops up to the forefront.
The only Outlook related reference turned on is Microsoft Outlook 15.0 Object Library.
Book2.xlsm
K_DeutschAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nick67Commented:
One line at the end of the sub I crafted for you before
objOutlook.ActiveInspector.WindowState = olMaximized
Sample attached
Mail-v3.xls
0
K_DeutschAuthor Commented:
Not worth a lot of time to chase after the minor inconvenience of a buried new message window, but could there be environmental variables that would impact this (especially multi display scenarios)? Even with the new line included, I'm testing on three machines and see inconsistent results. For some, the first time they run the macro the window gets buried but subsequent attempts and the window flies to the top. For others it works just I want it to every time.
0
Nick67Commented:
I tested in a multi-display environment, but threw everything onto a single monitor.
but could there be environmental variables that would impact this
Numerous.  It is Windows and not VBA that's in charge of the whole shooting match.
If you use VBA to fire an app and maximize it -- and then the code ends -- you'll get that app to the fore.
If the VBA continues, though, then the host of the VBA may stay in the fore.

You can use Windows API to force the issue -- but with Outlook, that'll be harder because the child window of the message is what is really desired.  The Office apps are especially good at remembering their past state, so they tend to open in the same size, space and location as before.
Still, look here
http://support.microsoft.com/kb/186431

You may have joy in Excel in massaging that idea.
The window title to get a handle to is the hard part
It may -- I said may -- be TheSubjectCellsContents & " - Message"

so  this may work

      Public THandle As Long
      Public iret As Long

      Private Declare Function BringWindowToTop Lib "user32" (ByVal _
         hwnd As Long) As Long

      Private Declare Function FindWindow Lib "user32" Alias _
         "FindWindowA" (ByVal lpClassName As Any, ByVal lpWindowName _
         As Any) As Long

      Private Sub BringForward
      Dim TheWindowTitle As String
      TheWindowTitle = Sheets(1).Cells(ActiveCell.Row, 4).Value & " - Message"
         THandle = FindWindow(vbEmpty, TheWindowTitle)
         If THandle = 0 Then
            MsgBox "No handle to bring forward was obtained"
            Exit Sub
         End If
                  iret = BringWindowToTop(THandle)
      End Sub

Open in new window


Sample Attached
Mail-v4.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

K_DeutschAuthor Commented:
Seems to work yet the message box does come up.
0
Nick67Commented:
This messagebox?

MsgBox "No handle to bring forward was obtained"
Remember that
TheWindowTitle = Sheets(1).Cells(ActiveCell.Row, 4).Value & " - Message"
represents the title in the titlebar of the Outlook mailitem window.

My sample subject was very terse
'd'
I don't know how much truncating-- but it is inevitable -- the title will get!
0
K_DeutschAuthor Commented:
In the end, this does what I need it to - bring the email to the forefront, , but as a caveat - at least for me personally, the "no handle obtained..." message box always pops up. Your efforts are much appreciated.
0
Nick67Commented:
You're welcome!
I take it you commented the msgbox out in the end?
I built and tested on O2003
I don't know why another version would be different, but perhaps there's where the problem lies
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.