VBA Script emails but reboots excel

davidfrazier

New Member
Joined
Mar 1, 2018
Messages
3
Hey guys,

I've worked hard to build a spreadsheet for ordering print job. It calculates cost, allows a file to be attached, and emails to our shop however, once it sends the email it reboots excel. Not sure why it does it but it always does. This is my VBS code that is attached to a button. It checks to ensure C38 has data if not it sends a message, if data is present it sends email. I've stripped everything out of it minus the " ActiveWorkbook.SendMail Recipients:="abody@somewhere.com" portion and it still reboots.

Private Sub CommandButton2_Click()
If Range("C38").Value = "" Then
'Message Box with just prompt message
MsgBox ("Chief Approval Required")
Wscript.Quit
End If
ActiveWorkbook.SendMail Recipients:="abody@somewhere.com"
'Message Box with just prompt message
MsgBox ("Thank you for your order")
Workbooks("PrintRequestClean_2_23_18.XLSM").Close SaveChanges:=False
End Sub


Thanks for any advice
David
 
Last edited by a moderator:

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Not sure what you mean by "Reboot". You are closing a workbook at the end. Maybe don't do that?
 
Upvote 0
Not sure what you mean by "Reboot". You are closing a workbook at the end. Maybe don't do that?


Excel closes the file but then within a few seconds it shuts down Excel and starts it back up. I've removed the part that closes the workbook and it still shuts down excel and restarts.
 
Upvote 0
Sounds like an Excel bug. Check for updates to Excel. Then make a new workbook and paste the code in a fresh workbook. Your code is fairly simple. It shouldn't crash Excel.

Except for the "Wscript.Quit" part. I'm not sure what you're doing there. I tested that code and it says "object required". I'm guessing you have wscript imported where I don't. If you want to end the sub at that point use "Exit Sub".

Code:
Private Sub CommandButton2_Click()
    If Range("C38").Value = "" Then
        'Message Box with just prompt message
        MsgBox ("Chief Approval Required")
        Exit Sub
    End If
    ActiveWorkbook.SendMail Recipients:="abody@somewhere.com"
    'Message Box with just prompt message
    MsgBox ("Thank you for your order")
    Workbooks("PrintRequestClean_2_23_18.XLSM").Close SaveChanges:=False
End Sub

When I use the .SendMail method I get a fancy popup warning me that a script is attempting to send e-mail. I've never seen that one before. Excel does not crash for me testing your code.
 
Upvote 0
HackSlash, it happens after I send the email. Feel free to send the test email to me. Is there a way for me to send the workbook to you? I guess this could be something to do with the security on our network?

Thanks for your help
David

Excel closes the file but then within a few seconds it shuts down Excel and starts it back up. I've removed the part that closes the workbook and it still shuts down excel and restarts.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,184
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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
Back
Top