Search code examples
excelvbaoutlook

Excel VBA email loop - server connectivity error handling

I have an automated Excel VBA loop which sends out a couple hundred e-mails daily over Outlook with a couple second intervals. Running this in Windows 10 + Office 365 environment.

While it runs fine in general, it crashes at times 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

Internet connectivity is generally OK, but can't rule out a brief disturbance. It happens once every 1-2 weeks during the loop, but is an annoyance as it stops the automation. Email syntax is what I see as quite typical practice 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 = "[email protected]"
    .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 always runs on, without exception, so making it programmatically try this line again, might be a possible solution? Do you guys have any suggestion on what would be a good practice to handle it?

Using plain On Error Resume Next and leave 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 is something I could do myself, but that's less than ideal also, since it's an hassle to compile the same email again.

Ideally it would be "take a minute and try again" approach, but are there any good practices or code examples how to do that here?

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?).