VBA Script emails but reboots excel

[davidfrazier]

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

 

[HackSlash]

Not sure what you mean by "Reboot". You are closing a workbook at the end. Maybe don't do that?

 

[davidfrazier]

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.

 

[HackSlash]

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".

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.

 

[davidfrazier]

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.

 

[HackSlash]

Update Excel. Contact your IT department. This is not going to be solved in code.

 

[daverunt]

Is this article relevant?

https://answers.microsoft.com/en-us...ing-mail/8ddd32fe-138e-49d8-bda4-6558f440f726