We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

how to open Outlook from Access

mlittler
mlittler asked
on
Medium Priority
585 Views
Last Modified: 2008-02-01
I want to open Outlook when I start my Access database.

I know how to set up the event, but how do I go about opening a session of Outlook so that it is visible, but minimized?

I do not mean this...

' Create the Outlook session.
    Set objOutlook = CreateObject("Outlook.Application")

...as it does not open Outlook so it is visible.

I was thinking of opening it using a macro, but how do I set it to minimized?
Comment
Watch Question

Commented:
to make Outlook visible after it've been created use
objOutlook.Visible=True

If you want to open an outlook application minimized then use this command:
Shell OutlookPath, vbMinimizedFocus

Author

Commented:
can you use this command in a macro?

Shell OutlookPath, vbMinimizedFocus
CERTIFIED EXPERT

Commented:
Hello mlittler,

Shell "D:\program files\Microsoft Office\Office11\OUTLOOK.EXE", vbNormalFocus

Outlook does not have a .visible property

Pete

Author

Commented:
how do i check to see whether outlook is already open?

Author

Commented:
if i create a macro with the action "runapp" and put...

Shell "C:\Program Files\Microsoft Office\Office\Outlook.exe", vbNormalFocus

in the command line, it brings up an error.
CERTIFIED EXPERT
Top Expert 2006

Commented:
to check if outlook is running, you could try using the GetObject command


    Dim objOutlook As Object
   
   
    On Error Resume Next
    Set objOutlookp = GetObject(, "Outlook.Application")
    If Err <> 0 Then
            MsgBox "Outlook is not running"
    Else
        If objOutlookp Is Nothing Then
            MsgBox "Outlook is not running"
        Else
            MsgBox "Outlook is running"
        End If

        'OPTIONAL   Outlook not running start new instance
        'Set objOutlookp = CreateObject("Word.Application")
    End If

    Set objOutlookp = Nothing
CERTIFIED EXPERT
Top Expert 2006

Commented:
sorry, my optional comment is if you want to start outlook (Ive got the CreateObject wrong, should be Outlook.Application

Author

Commented:
rockiroads,

Thanks, but using the createobject method does not actually make outlook visible.

This vba code does...

Shell "C:\Program Files\Microsoft Office\Office\Outlook.exe", vbHide

..but even using "vbHide" it does not minimise it. It flashes up full screen in front of Access.

I want it to be visible in the Windows toolbar, but not to flash up in front of my Access application.
CERTIFIED EXPERT
Top Expert 2006

Commented:
ok, that code was in response to your comment

how do i check to see whether outlook is already open?

if you want to run Outlook but not via CreateObject then use the ShellExecute method

Author

Commented:
...but your code for checking whether Outlook is open is really good, thanks!
CERTIFIED EXPERT
Top Expert 2006

Commented:

Author

Commented:
thanks, but the method described there produces the same problem. it does not make any difference whether you specify it to open mimised or not!
CERTIFIED EXPERT
Top Expert 2006

Commented:
ok, taking that code and running notepad, it opened it up minimised


fHandleFile "notepad.exe", WIN_MIN


about to try outlook
CERTIFIED EXPERT
Top Expert 2006

Commented:
I just ran this

fHandleFile "C:\Program Files\Microsoft Office\Office\Outlook.exe", WIN_MIN

and it opened it up minized (although you see the splash screen)

Author

Commented:
strange. it shows the splash screen on mine, but then it comes up maximised.

Where did you put your declarations? I have them in the form I am working from (at the moment I am running the code from the OnOpen event on a form)
CERTIFIED EXPERT
Top Expert 2006

Commented:
I suggest you copy that sample code and put it in a module

then call the module from your form (like the calls I have made)

Author

Commented:
here is my complete code.

Note: I changed the declarations to  Private in the "'***App Window Constants***" section. I wonder if this is why?!



Option Compare Database
Private Declare Function apiShellExecute 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

'***App Window Constants***
Private Const WIN_NORMAL = 1         'Open Normal
Private Const WIN_MAX = 3            'Open Maximized
Private Const WIN_MIN = 2            'Open Minimized

'***Error Codes***
Private Const ERROR_SUCCESS = 32&
Private Const ERROR_NO_ASSOC = 31&
Private Const ERROR_OUT_OF_MEM = 0&
Private Const ERROR_FILE_NOT_FOUND = 2&
Private Const ERROR_PATH_NOT_FOUND = 3&
Private Const ERROR_BAD_FORMAT = 11&



Private Sub Form_Open(Cancel As Integer)

    Dim objOutlook As Object
   
   
    On Error Resume Next
    Set objOutlookp = GetObject(, "Outlook.Application")
    If Err <> 0 Then
        fHandleFile "C:\Program Files\Microsoft Office\Office\Outlook.exe", WIN_MIN
        MsgBox "Outlook is not running"
    Else
        If objOutlookp Is Nothing Then
        fHandleFile "C:\Program Files\Microsoft Office\Office\Outlook.exe", WIN_MIN
        MsgBox "Outlook is not running"
        Else
        MsgBox "Outlook is running"
        End If

    End If

    Set objOutlookp = Nothing

End Sub


Function fHandleFile(stFile As String, lShowHow As Long)
Dim lRet As Long, varTaskID As Variant
Dim stRet As String
    'First try ShellExecute
    lRet = apiShellExecute(hWndAccessApp, vbNullString, _
            stFile, vbNullString, vbNullString, lShowHow)
           
    If lRet > ERROR_SUCCESS Then
        stRet = vbNullString
        lRet = -1
    Else
        Select Case lRet
            Case ERROR_NO_ASSOC:
                'Try the OpenWith dialog
                varTaskID = Shell("rundll32.exe shell32.dll,OpenAs_RunDLL " _
                        & stFile, WIN_NORMAL)
                lRet = (varTaskID <> 0)
            Case ERROR_OUT_OF_MEM:
                stRet = "Error: Out of Memory/Resources. Couldn't Execute!"
            Case ERROR_FILE_NOT_FOUND:
                stRet = "Error: File not found.  Couldn't Execute!"
            Case ERROR_PATH_NOT_FOUND:
                stRet = "Error: Path not found. Couldn't Execute!"
            Case ERROR_BAD_FORMAT:
                stRet = "Error:  Bad File Format. Couldn't Execute!"
            Case Else:
        End Select
    End If
    fHandleFile = lRet & _
                IIf(stRet = "", vbNullString, ", " & stRet)
End Function

Author

Commented:
i put the code in a module called "OpenOutlook"

how do I call it from my form?!
CERTIFIED EXPERT
Top Expert 2006

Commented:
ok, the form load stays in the form

the function fHandleFile along with the constant and DLL defintions (see below) go into the module
how you are calling it seems right
also in the code to check if object is running, there is a typo

the variable is defined as objOutlook, but I have used objOutlookp, so correct that



Private Declare Function apiShellExecute 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

'***App Window Constants***
Public Const WIN_NORMAL = 1         'Open Normal
Public Const WIN_MAX = 3            'Open Maximized
Public Const WIN_MIN = 2            'Open Minimized

'***Error Codes***
Private Const ERROR_SUCCESS = 32&
Private Const ERROR_NO_ASSOC = 31&
Private Const ERROR_OUT_OF_MEM = 0&
Private Const ERROR_FILE_NOT_FOUND = 2&
Private Const ERROR_PATH_NOT_FOUND = 3&
Private Const ERROR_BAD_FORMAT = 11&



Author

Commented:
It is not the objOutlookp part that is causing the problem.

With the code in a seperate module, the form does not understand the call "fHandleFile".

Brings up error "sub or function not defined"

Commented:
I don't know what are you talking about but

Shell "C:\program files\Microsoft Office\Office\OUTLOOK.EXE", vbMinimizedFocus

works as expected with minimal focus and visible application!
CERTIFIED EXPERT
Top Expert 2006

Commented:
ok, put a public in front of it
e.g

public Function fHandleFile

Commented:
You just used wrong constant- vbHide. You should use vbMinimizedFocus!!!

Author

Commented:
you can't put "public Function" in front of it, as it creates syntax error.

goliak; i tried the "vbMinimizedFocus" method before, but it makes no difference.

CERTIFIED EXPERT
Top Expert 2006

Commented:
if you have it defined as

Public Function fHandleFile(stFile As String, lShowHow As Long)


in a module, then it should be find, what exact error do you get



Personally I prefer using ShellExecute because its also useful for opening other types like folders for example
CERTIFIED EXPERT
Top Expert 2006
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Commented:
This world is set on fire. vbMinimizedFocus perfectly works at my pc. (Win2000, office 2000). I'm off.

Author

Commented:
rockiroads,

can you do me a favour?

please paste the code you have in your module, and the code you have in your form.

cheers,
michael

Author

Commented:
actually, i just tried it with Excel and it works fine. something to do with my outlook I think.

strange.

thanks for the help though.
CERTIFIED EXPERT
Top Expert 2006

Commented:
How I just tested it was the code I just posted (the accepted answer)

there is a test function below that

Public Sub Test1()
   fHandleFile "outlook.exe", WIN_MIN
End Sub

this is the same as calling it from a form
just call fHandleFile with your parameters

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.