Outlook Focus

[grahamvb]

Hello Access Programmers,

This issue has come up here before, under slightly different circumstances, but I do not recall seeing it solved.

http://www.access-programmers.co.uk/forums/showthread.php?t=159151
http://www.access-programmers.co.uk/forums/showthread.php?t=249217

I have an Access application that uses Outlook to send email. When I run the Access application with Outlook open and minimized to the taskbar, Outlook behaves as it should; when .display is executed the email window opens on top of the application.

However, if Outlook is not open, the email window opens behind the Access application, with the taskbar icon blinking.

I think the issue may be with Outlook’s Object Model Guard http://msdn.microsoft.com/en-us/library/office/ff864479.aspx but, my antivirus is registered and up to date.

Does anyone know of a method to bring the Outlook email window into focus or any other solution or workaround to this issue?

Function SendEmail()
On Error GoTo SendEmail_Err
 
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
 
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
 
    With OutMail
        .To = varAddress
        .CC = varCC
        .BCC = varBCC
        .Subject = varSubject
        .Body = varBody
        .Importance = varImportance
        .Display    ' (Deactivate to Auto-Send)
'        .send      ' (Activate to Auto-Send)
    End With
 
SendEmail_Exit:
    Exit Function
    
SendEmail_Err:
    MsgBox "Public Function: SendEmail" & Chr$(13) & _
    "Error number: " & Err.Number & Chr$(13) & _
    Err.Description, vbOKOnly, "modPublic"
    Resume SendEmail_Exit
    
End Function

 

[bwellbor]

If you could first check whether or not Outlook is open and then open Outlook if it is closed, would this solve your problem?

 

[bwellbor]

If this would solve your problem, you could try some of this code:

Put this code at the top of a module:

Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
    (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, _
    ByVal lpParameters As String, ByVal lpDirectory As String, _
    ByVal nShowCmd As Long) As Long

This code will check if Outlook is open:

Public Function fIsOutlookRunning() As Boolean
Dim W As Object
Dim processes As Object
Dim process As Object

fIsOutlookRunning= False

Set W = GetObject("winmgmts:")
Set processes = W.execquery("SELECT * FROM win32_process")

For Each process In processes
    If process.Name = "OUTLOOK.EXE" Then
        
        fIsOutlookRunning= True
        Exit For
        
    End If
Next

Set W = Nothing
Set processes = Nothing
Set process = Nothing

End Function

This code will open Outlook:

Public Sub OpenOutlook()

    'Depending on your version of Access, Access.hWndAccessApp could also be Application.hWnd
    If ShellExecute(Access.hWndAccessApp, vbNullString, "Outlook", vbNullString, "C:\", 1) < 33 Then 
        MsgBox "Outlook not found."
    End If
End Sub

So then your code would look like this:

Function SendEmail()
On Error GoTo SendEmail_Err
 
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
 
[COLOR="Red"]If fIsOutlookRunning = False Then Call OpenOutlook[/COLOR]

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
 
    With OutMail
        .To = varAddress
        .CC = varCC
        .BCC = varBCC
        .Subject = varSubject
        .Body = varBody
        .Importance = varImportance
        .Display    ' (Deactivate to Auto-Send)
'        .send      ' (Activate to Auto-Send)
    End With
 
SendEmail_Exit:
    Exit Function
    
SendEmail_Err:
    MsgBox "Public Function: SendEmail" & Chr$(13) & _
    "Error number: " & Err.Number & Chr$(13) & _
    Err.Description, vbOKOnly, "modPublic"
    Resume SendEmail_Exit
    
End Function

I hope that helps.

 

[grahamvb]

That is brilliant and does exactly what is supposed to do! It provides a level of control over Outlook I did not realize was possible. Without working too hard do you think there is a way to minimize and restore the main Outlook window and the email window with VBA. That should resolve any other focus issues that crop up with Outlook and give me as much control over Outlook as I would need in my application.

 

[bwellbor]

You can use the AppActivate statement to set the focus to Outlook or return the focus to Access.

'Set focus to Outlook
AppActivate "Inbox - Microsoft Outlook"

'Return focus to Access
AppActivate "Microsoft Access"

 

[demicay2669]

This looks like what I'm looking for to use for email in my database. Where would I incorporate this code to make it work for me?

 

[grahamvb]

Paste all bwellbor's Functions and Sub's into a module called something like modOutlook. You could declare some public variables such as, varAddress, varCC, varBCC, varSubject, varBody, varGreeting and varIportance. Within your page code give values to any variables you wish included in your email then Call SendEmail.

A sample database is attached.


If you find this useful please give a thumbs up.

 

[demicay2669]

I keep getting this compiler error. I copied all of it exactly as it is on the screen in the code boxes.

 

[grahamvb]

In your Visual Basic Editor, on the menu at the top, click Tools > References in the window that comes up locate Microsoft Outlook 14.0 Object Library, check the box to the left of it and then click the Ok button. That should solve your Outlook compiler issues.

 

[demicay2669]

The Microsoft Outlook 14.0 Object Library is already checked. Any thing else you can think of?

 

[demicay2669]

Ok change that I found another instance of the Library to check farther down. How do I get it to automatically attach the form I am on?

 

[grahamvb]

When you check a library it automatically applies to that entire database. If you have two libraries checked for outlook, uncheck the one that is not Microsoft Outlook 14.0. Microsoft Outlook 14.0 is the only outlook library that should be checked.

You can examine the database I attached above to see what is active and how the modules are coded. See the attached image below for a list of libraries that are active in the example database attached above.

 

[demicay2669]

I think I was misreading the screen the other one that was checked is Microsoft Access 14.0 Object Library. I did find Microsoft Outlook. And my email button does work. Many thanks for your help with that.

Do you know anything about attaching the current record from the form your looking at to the Email you are creating.

 

[grahamvb]

If you assigned a global variable for the email body, such as varBody, you could write a public subroutine that is called from your email button code.

Public Sub SetUpBody()
 
varBody = "Name: " & NameField & Chr$(13) & _
"Address: " & StreetField & Chr$(13) & _
"City/State/Zip: " & CityField & ", " & StateField & " " & ZipField & Chr$(13) & _
"And so forth ... " 
 
Call SendEmail ' modPublic SendEmail
 
End Sub

This is also interesting http://word.mvps.org/FAQs/MacrosVBA/PrtSc.htm.

If this reply has been helpful please click the thumbs up for thanks button --->

 

[demicay2669]

Your first part of the code for email has been quite helpful. But as i'm not too good with code, i'm not sure how to incorporate the SetUpBody code. I understand it should be in my button code and that you have a list of fields to include from the form if i'm reading that correctly. But how do i make that part of the email button code?

 

[grahamvb]

From the button code use "Call SetUpBody"

If you wish to include (pass) variables use "Call SetUpBody(var1, var2, var3, etc.)

The variables should be public if they need to be available beyond the current module.