Subscript Out of Range when closing a workbook

[Andibevan]

I have the following code that opens up a creates a copy of the open
workbook and then removes specific sheets and lines from the copy. It then
sends attaches the workbook to an e-mail.

I am having problems with the line wbCopy.Close - when it gets there it
throughs a 'Subscript out of range error' and then Excel crashes.

Any ideas why?

TIA

Andi

Debug.Print "Now = " & Now()
Dim wbCopy As Excel.Workbook
Dim sCRNT_Workbook As Excel.Workbook: Set sCRNT_Workbook =
ActiveWorkbook '.Name

'#####Temp Name#####
Dim sTime As String
Dim sSaveAs As String
sTime = Int(Format(Now, "hh"))
sSaveAs = Format(Now, "dd") & OrdinalSuffix(Day(Now)) & " " &
Format(Now, "mmmm") & " - " & sTime & "00.xls"
sSaveAs = "SMV Project Log_Lite_" & sSaveAs
' Debug.Print "sSaveAs = " & sSaveAs
Dim sBookPath As String: sBookPath = ActiveWorkbook.path & "\" &
sSaveAs
ActiveWorkbook.SaveCopyAs sBookPath
'#################################

' OPEN THE COPIED WORKBOOK
Set wbCopy = Workbooks.Open(sBookPath)
'wbCopy.Application.Visible = False
wbCopy.Application.ScreenUpdating = False
wbCopy.Application.Calculation = xlCalculationManual



' REMOVE UNWANTED ROWS AND SHEETS
DeleteUnwantedSheets sCRNT_Workbook, wbCopy

RemoveUnwantedRows wbCopy, "Project Log Form",
(Find_Col_Let("Project Log Form", 8, "Status: Open Closed")), 9, "CLOSED"
RemoveUnwantedRows wbCopy, "Risk Management Plan",
(Find_Col_Let("Risk Management Plan", 6, "Risk Status (Open, Mitigated,
Closed)")), 10, "CLOSED"
RemoveUnwantedRows wbCopy, "Change Register",
(Find_Col_Let("Change Register", 8, "Review")), 7, "N"

DeleteModules wbCopy

'wbCopy.Application.Visible = True
wbCopy.Save
wbCopy.Application.ScreenUpdating = True
wbCopy.Application.Calculation = xlCalculationAutomatic
wbCopy.Application.Calculate
'wbCopy.Close
'WaitTilClose wbCopy.Name
Mail_workbook_Outlook sCRNT_Workbook, wbCopy.FullName
wbCopy.Close
Set wbCopy = Nothing
Debug.Print "Now = " & Now()

 

[Bill Pfister]

Andi, first step is to focus on which routines specifically are crashing.
Start by commenting out the four subroutine calls and add them back one at a
time to determine where the culprit lies. The code doesn't crash without the
DeleteUnwantedSheets, RemoveUnwantedRows, DeleteModules,
Mail_workbook_Outlook subroutines. Can you post these routines?

Regards,
Bill

 

[Andibevan]

Bill,

Unfortunately I left the file at work so can't list the specific code but it
is an adaptation of the outlooksend code from http://www.rondebruin.nl .
where it creates an e-mail and attaches the workbook to the e-mail. From my
debugging it appears that the .close statement runs and then the code moves
immediately onto the next step (attach it to an e-mail).

It appears that because the file isn't fully closed when it tries to attach
it, that is what causes it to crash. If I stop the code at the .close
statement then allow it to continue (with a short break) it all works fine
(presumably because the file has had chance to fully close).

Hopefully this makes sense.

TIA

Andi

 

[JLGWhiz]

Andi, I had a similar problem at work a few years ago and found this function
worked pretty good:

Private Sub WaitTime()
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
sitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait sitTime
End Sub

To call it, simply insert "WaitTime" in your code after the "SendMail" call.
To set it for less than ten seconds, simply change the "newSecond =
Second(Now()) + Your preference." Don't use the quotation marks when you use
it in code, of course.