Like BeforePrint, but for Emailing?

[JustCallMeNelson]

Hi everyone,

I have an excel order from for customers that has a BeforePrint that works well and I am happy with:

Private Sub Workbook_BeforePrint(cancel As Boolean)
If Range("D9").Value = "" Or Range("G9").Value = "" Then
MsgBox ("Please enter your Account Name and Account Number")
cancel = True
Exit Sub
End If

If Range("G10") = "" Then
MsgBox ("Please enter an Order Reference")
cancel = True
Exit Sub
End If

If Range("G11") = "" Then
whatduedate = MsgBox("You have not stated when you need these goods (Due Date). Would you like them ASAP?", vbYesNo, "Due Date?")

End If

If whatduedate = vbYes Then
Range("G11").Value = "ASAP"
End If
If whatduedate = vbNo Then
MsgBox ("Please enter the date you would like this order delivered:")
cancel = True
Exit Sub
End If

End Sub

As I said I'm really happy with this and all I need to do is duplicate what it does, but for emails.

Can anyone help?

 

[RoryA]

What exactly do you mean by "but for emails"?

 

[JustCallMeNelson]

Sorry, was that a bit unclear?

I'd like exactly the same code to run when somebody tries to email the workbook. People will either be printing this order form off and faxing it in, or completing it and then emailing it in.

I'd like the same checks to run when they try to email it via the 'File' - 'Send To' menu as when they try to print it.

Thanks!!

 

[RoryA]

There are no events related to sending, unfortunately. You could try and hook into the File-SendTo menu as long as you reset it before closing or deactivating the workbook, but you would still have the issue that they could attach it to an email outside of Excel. Is it an option to just use the BeforeSave event?

 

[JustCallMeNelson]

Hi Rory,

Could you give more info on the File-SendTo menu that you mentioned?

 

[RoryA]

Assuming you are interested in the Send as attachment button, you would need a class module containing code like this:

Public WithEvents btn As CommandBarButton
Private Sub btn_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
   Dim blnStop as boolean
   blnStop = ValidateData()
   if blnStop then
      msgbox "Data is invalid and must be corrected"
   CancelDefault = True
   end if
End Sub

then you need some code to hook this up - for example (assuming your class is called Class1) in a normal module you would have:

 Dim objHook As Class1
Sub hookit()
   Dim ctl As CommandBarControl
   Set objHook = New Class1
   Set ctl = Application.CommandBars.FindControl(ID:=2188)
   Set objHook.btn = ctl
End Sub
Sub unhookit()
   Set objHook = Nothing
End Sub

You would need to call the hookit procedure from the workbook open event. You would also need to change your existing code so that it is a function that returns True if the data is valid, and False otherwise.

HTH.