Programming Excel with VBA and .NET

Collect Review Comments

Another way to send the entire workbook as an attachment is to use SendForReview. That method composes an email message with the workbook as an attachment, plus it allows you to display the message and thus avoid the security warning. The following code sends the active workbook as a message identical to that shown in Figure 21-3:

    Sub SendForReview( )
        ThisWorkbook.SendForReview "[e-mail]", _
          "Please review the attached workbook", True, False
    End Sub

Since SendForReview is intended for collecting review comments, the method displays a dialog asking if the file should be saved as a shared workbook before composing the message. There’s no easy way around that. In fact, since saving workbooks as shared files is difficult from code, you’ve got to take these steps if you want to send a workbook out for review without any extra prompts:

1. Create a temporary copy of the workbook.

2. Open that copy and save it as a shared review copy.

3. Get a reference to the shared review copy and send that workbook for review.

4. Close the shared workbook and delete the temporary file.

The following code illustrates those steps:

 Sub SendForReview( ) Dim wb1 As Workbook, wb2 As Workbook, _ fname As String, temp As String ' Get the active workbook. Set wb1 = ActiveWorkbook ' Create a unique temporary filename temp = wb1.Path & "\temp_" & CLng(Date) & ".xls" ' Save as a temporary file. ThisWorkbook.SaveCopyAs temp ' Open the review copy. Set wb2 = Workbooks.Open(temp) ' Create the ...