Solved

how to open Outlook from Access

Posted on 2004-03-30
30
473 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?
0
Comment
Question by:mlittler
  • 13
  • 12
  • 4
  • +1
30 Comments
 
LVL 4

Expert Comment

by:goliak
ID: 10712987
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
0
 

Author Comment

by:mlittler
ID: 10713064
can you use this command in a macro?

Shell OutlookPath, vbMinimizedFocus
0
 
LVL 77

Expert Comment

by:peter57r
ID: 10713076
Hello mlittler,

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

Outlook does not have a .visible property

Pete
0
 

Author Comment

by:mlittler
ID: 10713081
how do i check to see whether outlook is already open?
0
 

Author Comment

by:mlittler
ID: 10713091
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.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10713124
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
0
 
LVL 65

Expert Comment

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

0
 

Author Comment

by:mlittler
ID: 10713209
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.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10713233
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

0
 

Author Comment

by:mlittler
ID: 10713239
...but your code for checking whether Outlook is open is really good, thanks!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10713259
0
 

Author Comment

by:mlittler
ID: 10713339
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!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10713364
ok, taking that code and running notepad, it opened it up minimised


fHandleFile "notepad.exe", WIN_MIN


about to try outlook
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10713376
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)
0
 

Author Comment

by:mlittler
ID: 10713544
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)
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 65

Expert Comment

by:rockiroads
ID: 10713608
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)

0
 

Author Comment

by:mlittler
ID: 10713610
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
0
 

Author Comment

by:mlittler
ID: 10713682
i put the code in a module called "OpenOutlook"

how do I call it from my form?!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10713782
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&



0
 

Author Comment

by:mlittler
ID: 10713813
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"
0
 
LVL 4

Expert Comment

by:goliak
ID: 10713897
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!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10713912
ok, put a public in front of it
e.g

public Function fHandleFile
0
 
LVL 4

Expert Comment

by:goliak
ID: 10713923
You just used wrong constant- vbHide. You should use vbMinimizedFocus!!!
0
 

Author Comment

by:mlittler
ID: 10713994
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.

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10714024
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
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 10714026
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&


Public 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






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

Expert Comment

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

Author Comment

by:mlittler
ID: 10722202
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
0
 

Author Comment

by:mlittler
ID: 10722248
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.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10722324
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

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

708 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

16 Experts available now in Live!

Get 1:1 Help Now