How to not crash Outlook when processing multiple emails?

Question

I've a macro-enabled Word document to provide mail merge functionality (attachments, delayed delivery, etc). It interacts with Outlook (which must already be open) to send email where the content of the Word document has been pasted into the message.

There are a specific set of requirements to my use-case that mean we've had to go down this route. I am aware that other tools (paid or otherwise) exist to do this and that a macro-enabled document is a bit of a bodge. If you ignore my question and suggest another method please consider this first!

Some people, after a few emails have been sent, get an error message and are directed to debug/end.

I have not been able to reproduce this error although have seen it over a screen-share.

My best guess is that the code is executing too quickly for my colleagues' Outlook application to keep up. Eventually this causes an instruction to be passed to Outlook while it is still executing the previous instruction, and this causes the crash. The fact that I have been unable to reproduce the error might suggest that their particular setup is slower than mine? The crash seems to be occurring at the point where the next email message is being created, suggesting that the '.Send' is happening too slowly.

I have been able to implement a quick fix: https://www.myonlinetraininghub.com/pausing-or-delaying-vba-using-wait-sleep-or-a-loop

For instance I can add a short pause (1000 milliseconds, for instance) before the next email message is created. This appears to have mostly prevented this problem, but I can't be completely sure.

This is far from ideal as I can't reproduce the error and so optimizing the waiting time is difficult. Whatever time I choose it would still be possible for it to take longer! Also I am hoping to extend some of the functionality and would need to understand whether this will cause further problems.

I am looking for is a way to interract with Outlook from the code in the Word document that ensures that Outlook is ready to execute the next instruction, thereby avoiding the crash.

If I am not correct in my diagnosis, it would be helpful to understand what might be causing the crash on other users' machines, but not my own, and how I might be able to reproduce and address it.

I have added in Microsoft Outlook 16.0 Object Library.

I have stripped away much of the code to keep the example short:

Private Sub InterractWithOutlook()
    
    Dim oa As Outlook.Application

    Set oa = CreateObject("Outlook.Application")
    ' in my program Outlook is necessarily already running and I run a function
    ' to fetch it rather than open a new instance

    Dim n As Integer
    n = 10
    ' would actually be calculated directly from the data source being used
    Dim current As Integer
    current = 1

    Dim eml As MailItem

    Do While True
    ' done as a Do While loop in my code as I am interracting with a merge data source
        
        Set eml = oa.CreateItem(olMailItem)
        ' ^^^^ this line is where the code is crashing, 
        ' after an unpredictable number of iterations of the Do While loop

        With eml
            .Subject = "A test email"
            .To = "[email protected]"
            .Body = "Some test text"
            ' in my program there are many more instructions (for Outlook) being processed in this With block

            .Send
        End With

        current = current + 1
        If current > n Then Exit Do
    Loop

    oa.Quit

End Sub

I am looking to first confirm whether my diagnosis is correct. After that I would like to interract with Outlook from Word in a 'safer' manner.

Answer 1

I would suggest you add an error handler that only pauses your code if an error occurs (say up to a maximum 3 times) like this:

Private Sub InterractWithOutlook()
    ' Enable the error handler
    On Error GoTo ErrorHandler
    Dim lErrorCount As Long
    
    Dim oa As Outlook.Application

    Set oa = CreateObject("Outlook.Application")
    ' in my program Outlook is necessarily already running and I run a function
    ' to fetch it rather than open a new instance

    Dim n As Integer
    n = 10
    ' would actually be calculated directly from the data source being used
    Dim current As Integer
    current = 1

    Dim eml As MailItem

    Do While True
    ' done as a Do While loop in my code as I am interracting with a merge data source

        ' Reset the error count
        lErrorCount = 0

        Set eml = oa.CreateItem(olMailItem)
        ' ^^^^ this line is where the code is crashing,
        ' after an unpredictable number of iterations of the Do While loop

        With eml
            .Subject = "A test email"
            .To = "[email protected]"
            .Body = "Some test text"
            ' in my program there are many more instructions (for Outlook) being processed in this With block

            .Send
        End With

        current = current + 1
        If current > n Then Exit Do
    Loop

    oa.Quit

    Exit Sub
ErrorHandler:
    ' You can also add the error number(s) to the condition like this:
    ' If i < 3 And (Err.Number = num1 Or Err.Number = num2) Then
    If lErrorCount < 3 Then
       lErrorCount = lErrorCount + 1
       
       ' Wait for a second and resume from the line where the error occurred
       Application.Wait Now + TimeValue("00:00:01")
       Resume
    Else
        MsgBox "An error has occurred. Please contact your system adminstrator." & _
               Chr(10) & Chr(10) & _
               "Error Number: " & Err.Number & Chr(10) & _
               "Description: " & Err.Description
    End If
End Sub

Another thing you can do instead of waiting for a second is to log the error number and description in an error log (spreadsheet). i.e. *open your log spreadsheet, *append the error number and description and *close your log spreadsheet. This would probably take a second or two.