Solved

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

Posted on 2014-12-10
7
457 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Find out what you should include to make the best professional email signature for your organization.
Read this checklist to learn more about the 15 things you should never include in an email signature.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…

856 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