Outlook Automation from Access 'exhausting resources' [closed]

Question

Closed. This question needs debugging details. It is not currently accepting answers.

Edit the question to include desired behavior, a specific problem or error, and the shortest code necessary to reproduce the problem. This will help others answer the question.

Closed 8 months ago.

Improve this question

I have a MS Access application that sends email via outlook automation using vba code found here: Create an email with Outlook-Automation. I am using the code almost entirely unaltered under the heading 'Create an email with Outlook-Automation'.

After a while (a couple of days, maybe 100 emails to 1000 recipients), Outlook will hang, and thus pause all other code my Access application should be running. The message Outlooks displays (usually 3-5 of these boxes) is below.

I have tried 'killing' the outlook application before trying to send every email, but the outlook application is usually closed... and the code opens, sends, and closes again. All objects are closed and set to nothing at the end of code execution.

I am looking for a solution to prevent this from happening. The PC is not out of resources, is updated and plenty powerful. I am running 64bit Office 365. Restarting the PC on a set schedule may work, but the PC hosts an SQL Server used my others 24/7 and the restart will interrupt their work.

EDIT: Slightly altered code below - in vba, although I could not get it to format correctly without using javascript snippet. The EmailApp() function checks to see if Outlook is open or closed, so the app knows how to initialize the outlook object, and how to leave it at the end.

Public Function funSendEmail()
Dim EmailType As String
Dim myMail As Object
Dim myOutlApp As Object
Const olMailItem = 0

    EmailType = EmailApp()

    If EmailType = "OutlookOpen" Then
        Set myOutlApp = GetObject(, "Outlook.Application")
    ElseIf EmailType = "OutlookClosed" Then
        Set myOutlApp = CreateObject("Outlook.Application")
    End If
   
    Set myMail = myOutlApp.CreateItem(olMailItem)

    With myMail
        .To = "[email protected]"
        .Subject = "Test"
        .HTMLBody = "Test Message"
        .Send
    End With
 
   If EmailType = "OutlookOpen" Then
        'Do Nothing
    ElseIf EmailType = "OutlookClosed" Then
        myOutlApp.Quit
    End If

    Set myMail = Nothing
    Set myOutlApp = Nothing
End Function

You are not alone with this sort of problems. A regular reboot may be the best approach. A production SQL Server and Office automation should really NOT run on the same machine. — Andre
– Andre, Commented Apr 7 at 15:35

June7 · Accepted Answer · 2025-04-07 15:43:57Z

2

If Outlook is not already running at the time your code is executing, launching it (New Outlook.Application) and then closing (myOutlApp.Quit) can indeed cause the behavior you are experiencing after a large number of cycles (over 200).

Firstly, do not unconditionally close Outlook - if a user is working with Outlook, they will not appreciate you closing Outlook on them, check if there are open Outlook windows (Application.Inspectors.Count > 0 or Application.Explorers.Count > 0).

Secondly, create an instance of the Outlook.Application object only once and store it in a global variable. Create it only if it is not initialized or if Outlook was closed by the user and your reference to it became stale - you can test that by, for example, accessing the Application.Version property and checking if it throws an exception.

edited Apr 7 at 15:43

June7

21.7k8 gold badges28 silver badges36 bronze badges

answered Apr 7 at 15:36

Dmitry Streblechenko

67.1k4 gold badges56 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

riley3131 Apr 7 at 17:12

I think your second point indicates I shouldn't close outlook at the end (myoutlApp.quit). On the main automation pc/server, I could open Outlook initially (create object) and store the object as you indicate, and only close (quit) when the entire access app is closed, correct?

Dmitry Streblechenko Apr 8 at 15:30

Correct. Keep it referenced.

Collectives™ on Stack Overflow

Outlook Automation from Access 'exhausting resources' [closed]

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related