email from macro on close

[jsambrook]

I'm trying to email the spreadsheet on close, but I also have some other code to fit in and I'm not sure where to put it.


Private Sub Workbook_BeforeClose(cancel As Boolean)

x = MsgBox("Have you updated the Version Log", vbYesNo)
If x = vbYes Then Application.Dialogs(xlDialogSendMail).Show
Exit Sub
If x = vbNo Then cancel = True
MsgBox ("Please Update Version Log")
Sheets("Version Log").Select
Range("B5").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True

Application.Dialogs(xlDialogSendMail).Show
End Sub

I know where the problem is, its the exit sub after the vbYes, It works fine if the answer is yes, it emails fine. But if the answer is no, it still opens the email to send and I dont want this. What is the best way to do it?
thanks

 

[Ruddles]

You have a second Application.Dialogs(xlDialogSendMail).Show just as you exit the subroutine. This is what's firing when execution is allowed to fall through to the end of the subroutine.

If you set a breakpoint at the start of the code and step though it one statement at a time, you will see where your code execution is going.

 

[jsambrook]

Sorry, being a bit thick there! need to have more confidence in myself! I can work these things out! I just need to take a bit more care. Thanks for helping.
Janni

 

[Ruddles]

Being able to set breakpoints and step through code is a useful skill to acquire.