excelvbaoutlook

Server connectivity error handling in automated email loop

I have automated Excel VBA code which sends a couple hundred Outlook e-mails daily with a couple second intervals.

Running in Windows 10 + Office 365 environment.

It crashes once every 1-2 weeks with error message:

We can't complete this because we can't contact the server right now. Please try again later

enter image description here

Email syntax is what I see as typical in Excel VBA -> Outlook email (stripped down version):

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

With OutMail

    .Display
    
    .To = Range("Array_emp_email")       
    .SentOnBehalfOfName = "mailbox@domain.com"
    .Subject = "mySubject"
    .HTMLBody = "mailBody" & Signature
    .Attachments.Add docPath & ".pdf"
    .Send
    
End With

Set OutMail = Nothing
Set OutApp = Nothing

I'm stopped at Set OutMail before a new Outlook mail item should be opened.

enter image description here

When I press Debug and then Run/Continue, it runs on, so making it try this line again, might be a solution.

Using On Error Resume Next and leaving one e-mail unsent is not an option.

Leaving e-mail unsent, but logging an error through On Error GoTo ErrHandler and closing the sub I could do myself, but it is a hassle to compile the email again.

Ideally it would be "take a minute and try again" approach.

Solution

  • If you are using online (as opposed to cached) in Outlook, it is guaranteed you will run into issues are this - network errors are unavoidable and must be expected.

    You need to either turn the cached mode on (Outlook will deal with any network errors when it syncs your changes) or you need handle the error and retry later (seconds? minutes?).