• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 626
  • Last Modified:

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.
  • 4
  • 3
1 Solution
One line at the end of the sub I crafted for you before
objOutlook.ActiveInspector.WindowState = olMaximized
Sample attached
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.
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

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
Free tool for managing users' photos in Office 365

Easily upload multiple users’ photos to Office 365. Manage them with an intuitive GUI and use handy built-in cropping and resizing options. Link photos with users based on Azure AD attributes. Free tool!

K_DeutschAuthor Commented:
Seems to work yet the message box does come up.
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
I don't know how much truncating-- but it is inevitable -- the title will get!
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.
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now