If you're selecting all, could you just send the sheet as an email? The following code I use to send a sheet by email, doesn't require outlook. Reading it a bit more, it will send the selection as well, so you could send "select all"
Sub SendIt()
Dim Sendrng As Range
Dim Claimnum
Claimnum = Cells(8, 4).Value
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'Note: if the selection is one cell it will send the whole worksheet
Set Sendrng = Selection
'Create the mail and send it
With Sendrng
ActiveWorkbook.EnvelopeVisible = True
With .Parent.MailEnvelope
' Set the optional introduction field thats adds
' some header text to the email body.
.Introduction = "This is an assessment request for " & Claimnum
' In the "With .Item" part you can add more options
' See the tips on this Outlook example page.
' http://www.rondebruin.nl/mail/tips2.htm
With .Item
'Get email address from cell values
.To = Cells(5, 1).Value
If Cells(6, 1).Value <> "" Then
.CC = Cells(6, 1).Value
End If
.Subject = "Subject"
.Send
End With
End With
End With
'Cleanup code
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
ActiveWorkbook.EnvelopeVisible = False
End Sub
Edit: I'm an idiot, forgot code tags.
Bookmarks