Excel has stopped working.

[USAMax]

I have a macro that does not have any issues until it hits the following module that I have used many times before.

The program does everything it was intended to do and emails the completed document to the next person. Then it closes the workbook using the following code.

Public Function CloseWorkbook(wb As String)
    Application.DisplayAlerts = False
    Workbooks(wb).Close
    Application.DisplayAlerts = True
End Function

Unlike every other program I have written with this code, the user gets the message, "Excel has stopped working." The only difference is that this follows an Outlook function that sends this 1 meg file. I am wondering if it is waiting for Outlook to send the file? The users told me they had other Excel workbooks open when they got the error, does that have something to do with it?

I cannot reproduce the error so any suggestion will be appreciated!

 

[Blade Hunter]

I assume you are using the display alerts property to supress the "Save Changes" box? If so why not can that and have

Workbooks(wb).Close False</pre>

 

[USAMax]

Thank you Blade, I did not know about that option so that will be very helpful but I don't think it is the reason for Excel not responding.

I am wondering if the problem is within the Outlook routine. If it is having problems saving the file and we ask it to close before it saves it that might cause it to fail, right?

Function Mail_workbook_Outlook(Recipient As String)
'Working in Excel 2000-2013
'Mail a copy of the ActiveWorkbook with another file name
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
    Dim wb1 As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim OutApp As Object
    Dim OutMail As Object

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Set wb1 = ActiveWorkbook

    'Make a copy of the file/Open it/Mail it/Delete it
    'If you want to change the file name then change only TempFileName
    TempFilePath = Environ$("temp") & "\"
    TempFileName = "Ulta New Item Form " & Format(Now, "mm-dd-yy")
    FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))

    wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr

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

    'On Error Resume Next
    Select Case Recipient
        Case "Buyer"
            With OutMail
                .To = Range("SF.BuyerEmail")
                .cc = ""
                .BCC = ""
                .Subject = "Ulta New Item Form " & Range("SF.SupplierContactName") & " " & Range("SF.SupplierInformation")(1) & " " & Format(Now(), "mm/dd/yyyy")
                .Body = "Attention " & Range("SF.BuyerName") & " -  Attached is the Ulta New Item Form for your review."
                .Attachments.Add TempFilePath & TempFileName & FileExtStr
                .Send   'or use .Display
            End With
        Case "Supplier"
            With OutMail
                .To = Range("SF.SupplierEmail")
                .cc = ""
                .BCC = ""
                .Subject = "Ulta New Item Form " & Range("SF.SupplierContactName") & " " & Range("SF.SupplierInformation")(1) & " " & Format(Now(), "mm/dd/yyyy")
                .Body = "Attention " & Range("SF.SupplierContactName") & " -  Attached is the Ulta New Item Form for your review."
                .Attachments.Add TempFilePath & TempFileName & FileExtStr
                .Send   'or use .Display
            End With
        Case "Final"
            With OutMail
                .To = "MDSM@ulta.com"
                .cc = ""
                .BCC = ""
                .Subject = "Ulta New Item Form " & Range("SF.BuyerName") & " " & Range("SF.SupplierInformation")(1) & " " & Format(Now(), "mm/dd/yyyy")
                .Body = "Attention " & Range("SF.SupplierContactName") & " -  Attached is the Ulta New Item Form for your review."
                .Attachments.Add TempFilePath & TempFileName & FileExtStr
                .Send   'or use .Display
            End With
        Case Else
            End
    End Select
    On Error GoTo 0

    'Delete the file
    Kill TempFilePath & TempFileName & FileExtStr

    Set OutMail = Nothing
    Set OutApp = Nothing

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Function

 

[jswhip]

I also am experiencing the same problem. I use the same routine to close an Excel file without saving changes (wrk.Close savechanges:=False). It works for every file except one - I can't see anything unusual about the one errant file.

The odd part is that the macro worked in Excel 2007, but not in Excel 2013. To really complicate things, the macro works in the debugger mode in Excel 2013.

Were you able to make any headway on this?