Excel VBA email loop - server connectivity error handling-CodePudding

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

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.

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?

CodePudding user response：

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

CodePudding user response：

Outlook provides two basic connectivity modes when you are connected to Exchange Server: Cached Exchange Mode and Online Mode.

Cached Exchange Mode gives users a seamless online and offline Outlook experience by caching the user's mailbox and the Offline Address Book (OAB) locally. With Cached Exchange Mode, which is the default setting for users, Outlook no longer depends on continuous network connectivity for access to user information. When a user is connected, Outlook continuously updates users' mailboxes so that the mailboxes are kept up to date. If a user disconnects from the network, for example, by moving to an area without Wi-Fi access, the user can continue to access the last available email data.

Online Mode works by using information directly from the server, and, as the name implies, it requires a connection. Mailbox data is only cached in memory and never written to disk. So, if you have connection problems with Exchange in the online mode, you may get such errors. I'd suggest enabling the cached mode to avoid any issues with a connection, see Turn on Cached Exchange Mode for more information.

Microsoft recommends always using Cached Exchange Mode with a Microsoft 365 account.