Repeating Outlook Sessions

[grahamvb]

Hello Access-Programmers,

I can open Outlook one time when running the code below.
Subsequent attempts generate error 462 which I have captured and requested user action.
This method seems a bit sloppy.

Function SendEmail()
On Error Resume Next
 
Set OutApp = GetObject(, "Outlook.Application")  ' See if outlook is open
    If OutApp Is Nothing Then ' Open Outlook if not already Open
        Set OutApp = CreateObject("Outlook.Application") ' Create new instance of Outlook
    End If
 
On Error GoTo SendEmail_Err
 
Set OutMail = OutApp.CreateItem(olMailItem)
 
    With OutMail    ' Loads variables into proper sections of Outlook new email
        .To = varAddress
        .CC = varCC
        .BCC = varBCC
        .Subject = varSubject
        .Body = varBody
        .Importance = olImportanceNormal
        .Display    ' Brings Outlook new mail window into view (Deactivate for Auto Send)
'        .send      ' Activate to Auto Send
    End With
 
SendEmail_Exit:
    Exit Function
 
SendEmail_Err:
    If Err.Number = 462 Then
        MsgBox "Outlook can not be started remotely now." & Chr$(13) & _
        "Please start Outlook manually and retry sending your Email.", vbOKOnly, _
        "Outlook remote start failure"
    Resume SendEmail_Exit
    End If
    MsgBox "Public Function: SendEmail" & Chr$(13) & _
    "Error number: " & Err.Number & Chr$(13) & _
    Err.Description, vbOKOnly, "mdlPublicFunctions"
    Resume SendEmail_Exit
End Function

Is it possible that, close Outlook and Set Outmail = Nothing, commands are needed before exiting the function?

Any ideas on how to reliably open Outlook repetitively (not simultaneously) from within Access?

 

[pbaldy]

Done it several times, yes. You'd certainly need to set the mail item inside the loop.

 

[spikepl]

Replace

Set OutApp = GetObject(, "Outlook.Application")  ' See if outlook is open
If OutApp Is Nothing Then ' Open Outlook if not already Open         
   Set OutApp = CreateObject("Outlook.Application") ' Create new instance of Outlook     
End If

by

this

        Set OutApp = CreateObject("Outlook.Application") ' Create new instance of Outlook

The first code is concocted by some know-nothing and unfortunately got propagated and copied all over the web. Windows knows that Outlook only runs in a single instance, which is then grabbed if open, or created if not, by CreateObject.

Cleaning up by closing what you open and setting to nothing what you set is a good tactic (although some people here will debate the necessity of closing).

Finally, for repeating sending emails, nothing prevents you from opening Outlook once and then passing the apprrpriate object to your email subroutine.

Using

Option Explicit

at the top of each module is recommended. Follow the recommendation.

 

[grahamvb]

Thank you sir!

That solved the error 462 issue.
A comma... who knew... you did

Thanks again

 

[grahamvb]

I thought I would repost this "fixed" Outlook function in case others need a solution. (Thanks for the fix spikepl)

Function SendEmail()
On Error Resume Next

' Set OutApp = GetObject(, "Outlook.Application")  ' See if outlook is open
Set OutApp = CreateObject("Outlook.Application") ' Create new instance of Outlook
    If OutApp Is Nothing Then ' Open Outlook if not already Open
        Set OutApp = CreateObject("Outlook.Application") ' Create new instance of Outlook
    End If

On Error GoTo SendEmail_Err

Set OutMail = OutApp.CreateItem(olMailItem)

    With OutMail    ' Loads variables into proper sections of Outlook new email
        .To = varAddress
        .CC = varCC
        .BCC = varBCC
        .Subject = varSubject
        .Body = varBody
        .Importance = olImportanceNormal
        .Display    ' Brings Outlook new mail window into view (Deactivate for Auto Send)
'        .send      ' Activate to Auto Send
    End With

SendEmail_Exit:
    Exit Function

SendEmail_Err:
    If Err.Number = 462 Then
        MsgBox "Outlook can not be started remotely now." & Chr$(13) & _
        "Please start Outlook manually and retry sending your Email.", vbOKOnly, _
        "Outlook remote start failure"
    Resume SendEmail_Exit
    End If
    MsgBox "Public Function: SendEmail" & Chr$(13) & _
    "Error number: " & Err.Number & Chr$(13) & _
    Err.Description, vbOKOnly, "mdlPublicFunctions"
    Resume SendEmail_Exit
End Function

 

[spikepl]

Clean it up properly - throw out ALL the redundant code, as per my first post. Notice it wasn't the comma that made the difference, but removing the different and unneccessary function call.

 

[grahamvb]

Is this what you had in mind?

Function SendEmail()
On Error GoTo SendEmail_Err
 
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
 
' Create new instance of Outlook
Set OutApp = CreateObject("Outlook.Application")
 
' Create new Outlook Email item
Set OutMail = OutApp.CreateItem(olMailItem) 
 
    With OutMail    ' Loads Public variables into proper sections of Outlook new email
        .To = varAddress
        .CC = varCC
        .BCC = varBCC
        .Subject = varSubject
        .Body = varBody
        .Importance = varImportance
        .Display    ' Brings Outlook new mail window into view (Deactivate for 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, "ModOutlookEMail"
    Resume SendEmail_Exit
 
End Function