Open/Close Outlook within Access (1 Viewer)

cambonner

Member
Local time
Today, 13:13
Joined
Nov 30, 2012
Messages
36
Background: I manage an Access 2016 db for our local food pantry, which is staffed by (mostly senior and mostly PC-neophyte) volunteers. I mention this since my approach is to make the db interface as user-friendly as possible.
Problem: I've recently added a feature that allows our volunteers to send an email from within the db, using the pantry's default Gmail address. For example, an email to our bread supplier that we're out of stock, or to our facilities manage to report a leaky faucet. The names of possible addressees is accessed via a report (based on a query) with hyperlinked email addresses. I added vba code that works, but the email is not sent unless/until Outlook is opened outside of the db. I then added Outlook to the PC startup menu so emails would be sent out immediately. The problem is that Outlook now appears as the opening screen when Windows 10 starts up. This has annoyed/confused our users who are used to only seeing the db sign-in page.
Question: If there a way to have Outlook open and stay in minimize state, and thus be "hidden" to the user, or perhaps more elegantly, a way to add some vba code within Access that opens Outlook when the user presses the Send Email button, and then either closes or minimizes it when the current screen is closed? Here is the code I am using now for sending emails from within Access:

Private Sub Email_Click()

Dim MSG As String
MSG = "PLEASE ADD YOUR NAME TO THE BODY OF THIS EMAIL."

' Remember to add REFERENCE to Microsoft Outlook Object Library

Dim O As Outlook.Application
Dim M As Outlook.MailItem

Set O = New Outlook.Application
Set M = O.CreateItem(olMailItem)

With M
.BodyFormat = olFormatHTML
.HTMLBody = MSG
.To = Email
.Subject = "Please Help with the Following " & Now()
.Display

End With


Set M = Nothing
Set O = Nothing

End Sub
 

cambonner

Member
Local time
Today, 13:13
Joined
Nov 30, 2012
Messages
36
No, I haven’t. I’ve only used shell:Startup and shell:AppsFolder to get Outlook to open on Windows 10 startup. But it opens in maximum view. What does Shell() do, and does it need an argument?
Thanks!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:13
Joined
Oct 29, 2018
Messages
21,358
No, I haven’t. I’ve only used shell:Startup and shell:AppsFolder to get Outlook to open on Windows 10 startup. But it opens in maximum view. What does Shell() do, and does it need an argument?
Thanks!
Yes, the Shell() function has a WindowStyle argument, so you can tell if you want to open the application as a minimized window.
 

isladogs

MVP / VIP
Local time
Today, 17:13
Joined
Jan 14, 2017
Messages
18,186
Consider using CDO to send email direct from access without involving Outlook
 

cambonner

Member
Local time
Today, 13:13
Joined
Nov 30, 2012
Messages
36
I'll try the shell(), but I'm also quite unskilled in such matters. This command would be inserted in my vba email routine, right? Can you tell me what the arguments would be for launching Outlook and then minimizing it?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:13
Joined
Feb 19, 2002
Messages
42,971
I would take @isladogs suggestion and use CDO

I have done what you are trying to do numerous times but NOT with GMail. I seem to remember some incapability but don't quote me on that one. Do you use Outlook to manage the GMail account or do you use the web interface? You might try adding the GMail account to outlook to see if that solves the problem
 

cambonner

Member
Local time
Today, 13:13
Joined
Nov 30, 2012
Messages
36
Yes, I've linked our Gmail account to Outlook, using imap and smtp server settings.
 

KitaYama

Well-known member
Local time
Tomorrow, 02:13
Joined
Jan 6, 2022
Messages
1,489
I had the exact same problem.
It's what I'm doing now.
Before sending mail, I check if outlook is running or not. If not, I launch Outlook, and then send the mail.

Code:
'********************************************************************
    ' Micorosoft Outlook doesn't send the mail if it's closed
    ' if the user uses Outlook then I have to launch it maually
'********************************************************************
    Dim Pth As String
    Dim sApp As Variant
    Dim varApp As Variant

    'Check if Instance of MS Oulook is Running in the machine
    sApp = "Outlook.Application"
    If IsAppRunning(sApp)  Then
        'MsgBox "Application is Running"
    Else  
        Pth = "C:\Program Files\Microsoft Office\root\Office16\OUTLOOK.EXE"
        varApp = Shell(Pth, vbMinimizedFocus)
    End If

And this is the function to check if Outlook is running or not

Code:
Function IsAppRunning(ByVal sAppName) As Boolean
    Dim oApp As Object
    On Error Resume Next
    Set oApp = GetObject(, sAppName)
    If Not oApp Is Nothing Then
        Set oApp = Nothing
        IsAppRunning = True
    End If
    On Error GoTo 0
End Function


Edit: I don't know why, but windows allows us to open several instances of Outlook. To prevent another instance of Outlook being launched, I check if it's running or not before opening it.
 
Last edited:

KitaYama

Well-known member
Local time
Tomorrow, 02:13
Joined
Jan 6, 2022
Messages
1,489
Forgot to say.
If you want to close Outlook after the mail is sent, you can run the following procedure:

Code:
Public Sub CloseOutLook()

    Dim oServ As Object
    Dim cProc As Object
    Dim oProc As Object
    StrProcessName = "Outlook.exe"
    Set oServ = GetObject("winmgmts:")
    Set cProc = oServ.ExecQuery("select * from win32_process")
    For Each oProc In cProc
        If InStr(1, oProc.Name, StrProcessName, vbTextCompare) <> 0 Then
            If InStr(1, oProc.CommandLine, NtpPath, vbTextCompare) <> 0 Then
            oProc.Terminate
            End If
        End If
    Next
End Sub
 

cambonner

Member
Local time
Today, 13:13
Joined
Nov 30, 2012
Messages
36
Thanks so much KitaYama. I'll try your suggested code; seems exactly what I was looking for.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:13
Joined
Feb 28, 2001
Messages
26,999
It has been a while, but it used to the be case that you could try to find a copy of Outlook already open on your machine and connect to it. I bring it up because if there is a copy of Outlook ALREADY OPEN on your system, your attempt to create a new Outlook object will fail. Unlike Word or Excel, you are not allowed to have multiple sessions of Outlook open on a given system. Outlook itself will object to the attempt.

Therefore, if you attempt to find Outlook and it succeeds, you can just use the session you found - and should NOT close it, because you weren't the one who opened it. On the other hand, you could surely close it if you opened it. Therefore, KitaYama's "IsRunning" test is highly relevant.

Having said that, I agree with Isladogs that CDO might be simpler because it doesn't have quite as much required code overhead as Outlook. I.e. it works with less overall coding effort. The only fly in the ointment is that with Outlook, you CAN look up someone's info whereas with CDO you need to already have the addresses you are going to use.
 

cambonner

Member
Local time
Today, 13:13
Joined
Nov 30, 2012
Messages
36
I had the exact same problem.
It's what I'm doing now.
Before sending mail, I check if outlook is running or not. If not, I launch Outlook, and then send the mail.

Code:
'********************************************************************
    ' Micorosoft Outlook doesn't send the mail if it's closed
    ' if the user uses Outlook then I have to launch it maually
'********************************************************************
    Dim Pth As String
    Dim sApp As Variant
    Dim varApp As Variant

    'Check if Instance of MS Oulook is Running in the machine
    sApp = "Outlook.Application"
    If IsAppRunning(sApp)  Then
        'MsgBox "Application is Running"
    Else 
        Pth = "C:\Program Files\Microsoft Office\root\Office16\OUTLOOK.EXE"
        varApp = Shell(Pth, vbMinimizedFocus)
    End If

And this is the function to check if Outlook is running or not

Code:
Function IsAppRunning(ByVal sAppName) As Boolean
    Dim oApp As Object
    On Error Resume Next
    Set oApp = GetObject(, sAppName)
    If Not oApp Is Nothing Then
        Set oApp = Nothing
        IsAppRunning = True
    End If
    On Error GoTo 0
End Function


Edit: I don't know why, but windows allows us to open several instances of Outlook. To prevent another instance of Outlook being launched, I check if it's running or not before opening it.
Hi KitaYama,
I've been trying to incorporate your code into my db, but am having a problem. I'm sure it's my ignorance as I'm completely new to vba programming. In the first procedure the type is not mentioned, but I'm assuming "function". So do I add Function (???) at the top, and End Function at the bottom? Also, I've changed the Pth line to reflect the path on my PC (C:\ProgramFiles(x86)\Microsoft Office\Office16\Outlook.exe. Does this seem correct to you?
Thanks for whatever guidance you can provide.
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:13
Joined
Sep 21, 2011
Messages
14,044
It has been a while, but it used to the be case that you could try to find a copy of Outlook already open on your machine and connect to it. I bring it up because if there is a copy of Outlook ALREADY OPEN on your system, your attempt to create a new Outlook object will fail. Unlike Word or Excel, you are not allowed to have multiple sessions of Outlook open on a given system. Outlook itself will object to the attempt.

Therefore, if you attempt to find Outlook and it succeeds, you can just use the session you found - and should NOT close it, because you weren't the one who opened it. On the other hand, you could surely close it if you opened it. Therefore, KitaYama's "IsRunning" test is highly relevant.

Having said that, I agree with Isladogs that CDO might be simpler because it doesn't have quite as much required code overhead as Outlook. I.e. it works with less overall coding effort. The only fly in the ointment is that with Outlook, you CAN look up someone's info whereas with CDO you need to already have the addresses you are going to use.
@The_Doc_Man, I have used the code below for years, and my Outlook is always running due to interactive use?
You can see that I thought that as well initially. :)
Code:
' See if Outlook is open, otherwise open it
    'If fIsOutlookRunning = False Then
    Set objOutlook = CreateObject("Outlook.Application")
    'Call OpenOutlook
    'Pause (5)
    ' Else
    'Set objOutlook = GetObject(, "Outlook.Application")
    'End If

Code:
Proc_Exit:
    Set objOutlook = Nothing
    Set objOutlookMsg = Nothing
    Set objOutlookRecip = Nothing
    Set objOutlookAttach = Nothing
    Set rs = Nothing
    Set rsCW = Nothing
    Set db = Nothing
    SetStatusBar (" ")
    Exit Sub
 

Users who are viewing this thread

Top Bottom