Making the nightly close-out macro survive a missing network drive.
Ride Me Cycles' nightly close-out macro saved a PDF to a network drive and emailed it through Outlook, both of which occasionally weren't ready yet when the macro ran unattended overnight. A retry loop keeps it running instead of leaving the close-out undone until someone notices in the morning.
BlackTor Group LtdDartmoor, UK
A note on this page
A reference, not a client write-up.
Illustrative, per the note on the main VBA page: not real client work. It sits alongside the Frame-Number Check and Warranty Claims pages as a deeper dive on one routine that comes up often enough to be worth its own write-up.
The issue
A resource that's usually there isn't always there at midnight.
The macro runs unattended overnight, straight after the machine wakes on a schedule. A few seconds after waking, the network drive isn't always reconnected yet, and Outlook isn't always fully started. Both are almost always ready within moments, but the macro as originally written failed outright the instant either one wasn't ready at that exact moment, leaving the whole close-out undone.
The fix isn't more error handling around a single attempt, it's giving the same attempt a few more chances, a short pause apart, before actually giving up.
Retry with backoff
RunNightlyCloseOut: retry a transient failure instead of giving up on the first one
Option Explicit
' RunNightlyCloseOut
' ------------------
' Saves the day's close-out pack as a PDF to the shared drive and emails
' it, retrying each attempt on failure instead of giving up outright:
' both the network drive and Outlook can be transiently unavailable a
' few seconds after the scheduled task wakes the machine, and are
' almost always back within the time this loop allows.
Private Const MAX_RETRY_ATTEMPTS As Integer = 5
Private Const RETRY_DELAY_SECONDS As Integer = 10
Public Sub RunNightlyCloseOut()
Dim strPdfPath As String
strPdfPath = "\\FILESERVER\CloseOut\CloseOut-" & Format(Date, "yyyymmdd") & ".pdf"
If Not TryWithRetry(strPdfPath) Then
LogFailure "Close-out pack could not be saved or sent after " & _
MAX_RETRY_ATTEMPTS & " attempts."
End If
End Sub
Private Function TryWithRetry(ByVal strPdfPath As String) As Boolean
Dim intAttempt As Integer
For intAttempt = 1 To MAX_RETRY_ATTEMPTS
If SaveAndEmailCloseOut(strPdfPath) Then
TryWithRetry = True
Exit Function
End If
' A short wait gives a reconnecting network drive or a still-
' starting Outlook instance time to come up before the next
' attempt, rather than hammering a resource that isn't ready yet.
Application.Wait Now + TimeSerial(0, 0, RETRY_DELAY_SECONDS)
Next intAttempt
TryWithRetry = False
End Function
Private Function SaveAndEmailCloseOut(ByVal strPdfPath As String) As Boolean
On Error GoTo HandleFailure
ThisWorkbook.Worksheets("Close-Out Pack").ExportAsFixedFormat _
Type:=xlTypePDF, Filename:=strPdfPath, Quality:=xlQualityStandard
Dim objOlApp As Object
Dim objOlMail As Object
Set objOlApp = CreateObject("Outlook.Application")
Set objOlMail = objOlApp.CreateItem(0) ' 0 = olMailItem
With objOlMail
.To = "enquiries@blacktorgroup.com"
.Subject = "Close-out pack, " & Format(Date, "dd mmmm yyyy")
.Body = "Attached: tonights close-out pack, generated automatically."
.Attachments.Add strPdfPath
.Send
End With
SaveAndEmailCloseOut = True
Exit Function
HandleFailure:
SaveAndEmailCloseOut = False ' let TryWithRetry decide whether to try again
Set objOlMail = Nothing
Set objOlApp = Nothing
End Function
Private Sub LogFailure(ByVal strMessage As String)
Dim intFileNumber As Integer
intFileNumber = FreeFile
Open "\\FILESERVER\CloseOut\failure-log.txt" For Append As #intFileNumber
Print #intFileNumber, Format(Now, "yyyy-mm-dd hh:nn:ss") & " -- " & strMessage
Close #intFileNumber
End Sub
SaveAndEmailCloseOut reports success or failure through its return value rather than letting an error propagate and stop the routine, which is what lets TryWithRetry decide what happens next instead of the macro simply ending. This is the same discipline the main VBA page's error-handling note describes: every error branch cleans up its own objects before returning, so a failed attempt never leaves an Outlook object half-built behind it.
The wait between attempts only happens after a failure, not before the first attempt, so the routine loses no time at all on a normal night when everything is ready first time.
LogFailure exists because nobody is watching a macro that runs at midnight: if every attempt genuinely fails, the routine still needs to leave a record somewhere a person will actually look the next morning, rather than failing silently a second time.
Result
A brief delay most nights beats a missed close-out most weeks.
The close-out pack now reliably saves and sends even when the network drive or Outlook take a few extra seconds to be ready, and a genuine failure leaves a clear record instead of an empty inbox nobody notices until it's asked about.
