Solved

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

Posted on 2014-12-10
7
422 Views
Last Modified: 2014-12-18
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
0
Comment
Question by:K_Deutsch
  • 4
  • 3
7 Comments
 
LVL 26

Expert Comment

by:Nick67
ID: 40491929
One line at the end of the sub I crafted for you before
objOutlook.ActiveInspector.WindowState = olMaximized
Sample attached
Mail-v3.xls
0
 

Author Comment

by:K_Deutsch
ID: 40492224
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
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 40492396
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:K_Deutsch
ID: 40501188
Seems to work yet the message box does come up.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40501196
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
 

Author Closing Comment

by:K_Deutsch
ID: 40508268
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
 
LVL 26

Expert Comment

by:Nick67
ID: 40508361
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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Get an idea of what you should include in an email disclaimer with these Top 5 email disclaimer tips.
Outlook Free & Paid Tools
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now