Excel 2007 Routing workbook to Outlook

[Scott Lyon]

Recently, my company (finally) began the transition from Office 2003 to
Office 2007, and I've already run into a snag:

We have an automation (little more than an Excel spreadsheet with VBA code
behind it) that is now failing under Excel 2007 (but worked under 2003).

The offending code is when it tries to route a workbook to Outlook, in order
to send it out as an attachment.

Under 2003, the code would prompt the user for the recipient's last name,
and the routing process would automatically try to find the best match (in
their Outlook contact(s) ) and allow them to pick the correct recipient.


Under 2007, it fails, apparently due to the fact that the .RoutingSlip logic
on the Workbook object has been deprecated.

Here is the code I'm using:

ActiveWorkbook.HasRoutingSlip = True
With ActiveWorkbook.RoutingSlip
.Delivery = xlOneAfterAnother
.Recipients = user1
.Subject = "FOR INITIAL TURNOVER"
.Message = "Here is the Workbook."
End With
ActiveWorkbook.Route


How can I do the equivalent (in VBA) that will work under Excel 2007?


Thanks!

 

[Scott Lyon]

Note: I am aware that I could write code that would just send an e-mail to
the recipient, but that would require either 1) that the user knows the
recipient's exact e-mail address (and doesn't make any typos when entering
it), or 2) create a UI form to search through our own databases to determine
the recipient's e-mail address.