PDA

View Full Version : Excel send to mail recipients help



Joeryan101
06-03-2008, 01:28 PM
I have found a code that pops up with the send to mail recipients window and then automatically sends the sheet as a body. I need help figuring out how to have vba enter an email address and subject into this window within excel. All help would be much appreciated. Below is my current code:

Option Explicit

Sub SendSheet()

Application.DisplayAlerts = False
With Application
' .ScreenUpdating = False
On Error Resume Next
Select Case ActiveWorkbook.BuiltinDocumentProperties("Comments")
Case Is <> "SentBefore"
'//show the mail envelope\\
With ActiveWorkbook
.EnvelopeVisible = True
'//mark the workbook as being sent previously\\
.BuiltinDocumentProperties("Comments") = "SentBefore"
End With
Case "SentBefore"
'//activate the mail window (screen updating=false keeps it hidden)\\
ActiveWorkbook.EnvelopeVisible = True

With .CommandBars("Send To")
'//IF the control "Send Now" is missing the code will error out, so add it\\
.Controls.Add Type:=msoControlButton, ID:=3708

With .Controls("Send Now")

'//sends activesheet in email body\\
.Execute

'//remove the "Send Now" control added for this procedure\\
.Delete

End With
End With
End Select
' .ScreenUpdating = True
End With
Application.DisplayAlerts = True
End Sub

Simon Lloyd
06-03-2008, 06:42 PM
Google "Ron De Bruin Send Mail"