Programming Excel with VBA and .NET

Route Workbooks

Routing a workbook is similar to sending it for review, except the workbook can be sent to the email addresses serially instead of all at once. To route a workbook from code:

1. Get a reference to the workbook’s RoutingSlip object

2. Set the RoutingSlip properties.

3. Call the workbook’s Route method.

The following code illustrates those steps:

    ' Don't run this from VBE!
    Sub RouteWorkbook( )
        Dim wb As Workbook, rs As RoutingSlip
        ' Trap error in case user cancels send.
        On Error Resume Next
        Set wb = ActiveWorkbook
        ' Get the routing slip.
        Set rs = wb.RoutingSlip
        ' Clear it.
        rs.Reset
        ' Set the routing properties.
        rs.Delivery = xlOneAfterAnother
        rs.Recipients = Array("[e-mail]", _
          "[e-mail]")
        rs.Subject = wb.Name
        rs.Message = "Please review and route on."
        rs.TrackStatus = True
        rs.ReturnWhenDone = True
        ' Send the message.
        wb.Route
        On Error GoTo 0
    End Sub

Like the SendMail example, running the preceding code from the Visual Basic Editor can lock up Excel. You should run it only from the Macro dialog box or from an event while Excel is active. Also like SendMail, routing will display the security prompt (Figure 21-1).

Routing has the following differences from other ways of sending workbooks:

• The workbook is attached to the message as a read-only file. If the user wants to make changes, he must first save the file as read/write. The new file retains its routing slip, however, and can be sent on to the next recipient.

• The recipient list is sent as an array of ...