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:

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

HackSlash

Active Member
Joined
Nov 18, 2016
Messages
360
Not sure what you mean by "Reboot". You are closing a workbook at the end. Maybe don't do that?
 

davidfrazier

New Member
Joined
Mar 1, 2018
Messages
3
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

Active Member
Joined
Nov 18, 2016
Messages
360
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.
 

davidfrazier

New Member
Joined
Mar 1, 2018
Messages
3

ADVERTISEMENT

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

Active Member
Joined
Nov 18, 2016
Messages
360
Update Excel. Contact your IT department. This is not going to be solved in code.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,020
Messages
5,526,296
Members
409,694
Latest member
bastos21

This Week's Hot Topics

Top