Use command button to send email

[Guest]

Hello-
How do I incorporate the following Ron Debruin code to be triggered with a
command button on a user form?

Sub Mail_ActiveSheet()
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
.SendMail "(e-mail address removed)", _
"This is the Subject line"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

Thanks!

 

[Tom Ogilvy]

Private Sub Commandbutton1_click()
Mail_ActiveSheet
End Sub

 

[Bob Phillips]

That didn't work for me on a form, presumably because the form has focus?

Bob

 

[Tom Ogilvy]

In contrast, it worked flawlessly for me and the form did have the focus.

 

[Bob Phillips]

How odd. Which Excel version do you have? I am running on 2000. It failed on
the SendMail command, and so I assumed that was the problem the OP
encountered.


Bob

 

[Tom Ogilvy]

Does it run when not executed from a userform? Sounds like your mail
program isn't registered to work with Sendmail.

the original poster didn't state that they were having any problem with the
code. Simply that they wanted to execute it from a commandbutton on a
userform. If it was working for them before, it should work with what I
suggested.

xl2003, but I wouln't expect that to be a consideration for this code.

 

[Ron de Bruin]

Hi Tom/Bob

Does it run when not executed from a userform? Sounds like your mail
program isn't registered to work with Sendmail.

Possible problems are
http://www.rondebruin.nl/mail/problems.htm

Number 2 I think

 

[Bob Phillips]

Does it run when not executed from a userform? Sounds like your mail
program isn't registered to work with Sendmail.

Although it is n ot something that I ever do, I can send mail from Excel
using Sen dTo. Is SendMail something different (I had assumed not)?

the original poster didn't state that they were having any problem with the
code. Simply that they wanted to execute it from a commandbutton on a
userform.

No, but it is such a simple next step, I thought the OP had taken it.

xl2003, but I wouln't expect that to be a consideration for this code.

Nor I, but all considerations.