Solved

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

Posted on 2014-12-10
7
444 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

777 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