Excel has stopped working.

USAMax

Well-known Member
Joined
May 31, 2006
Messages
832
Office Version
  1. 365
Platform
  1. Windows
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.

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!
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Blade Hunter

Well-known Member
Joined
Mar 13, 2008
Messages
3,147
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

Well-known Member
Joined
May 31, 2006
Messages
832
Office Version
  1. 365
Platform
  1. Windows
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?

Code:
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

Board Regular
Joined
Jan 19, 2005
Messages
65
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?
 

Forum statistics

Threads
1,136,971
Messages
5,678,897
Members
419,788
Latest member
Mukund2903

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top