Trap the Send-To event

[Otto Moehrbach]

Excel XP & Win XP
I need to increment an Invoice number before each time the Invoice sheet is
either printed or emailed ( File - Send-To). The print and Send-To commands
are manually, not VBA, issued
The printed part I know, but is there a way to trap the Send-To event to run
some code?
Thanks for your time. Otto

 

[Peter T]

Hi Otto,

You could trap the button click event. You didn't say which Send-to, you
have a bit control over Routing but not mail, if say user doesn't complete
the email.

'''code in normal module

Dim colButtons As Collection

Sub SinkClickEvents()
Dim ctl As CommandBarButton
Dim i As Long
Dim nID As Long
Dim vaID, vaAction
Dim cls As Class1
vaID = Array(3738, 2188, 259)
vaAction = Array("SendToMail", "SendToRecipient", "SendToRouting")

Set colButtons = New Collection
For i = LBound(vaID) To UBound(vaID)
Set ctl = Application.CommandBars.FindControl(ID:=vaID(i))
Set cls = New Class1
Set cls.pBtn = ctl
cls.psAction = vaAction(i)
colButtons.Add cls, vaAction(i)
Next
End Sub

''''''''''''''''''''''''''''''

''''code in Class1 < rename
Public WithEvents pBtn As CommandBarButton
Public psAction As String

Private Sub pBtn_Click(ByVal Ctrl As Office.CommandBarButton, _
CancelDefault As Boolean)
Dim bDlgRes As Boolean
MsgBox psAction
Select Case psAction
Case "SendToMail"
'code
Case "SendToRecipient"
'code
Case "SendToRouting"
CancelDefault = True
bDlgRes = Application.Dialogs(xlDialogRoutingSlip).Show
MsgBox "User " & IIf(bDlgRes, "processed", "pressed cancel"), , psAction
'code
End Select
End Sub

Regards,
Peter T

 

[Otto Moehrbach]

Thanks Peter. I'll go with that and see what happens. Otto