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 ", _
"This is the Subject line"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

Thanks!

Private Sub Commandbutton1_click()
Mail_ActiveSheet
End Sub

--
Regards,
Tom Ogilvy


"Slugger" wrote in message
...
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 ", _
"This is the Subject line"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

Thanks!

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

Bob


"Tom Ogilvy" wrote in message
...
Private Sub Commandbutton1_click()
Mail_ActiveSheet
End Sub

--
Regards,
Tom Ogilvy


"Slugger" wrote in message
...
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 ", _
"This is the Subject line"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

Thanks!

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

--
Regards,
Tom Ogilvy


"Bob Phillips" wrote in message
...
That didn't work for me on a form, presumably because the form has focus?

Bob


"Tom Ogilvy" wrote in message
...
Private Sub Commandbutton1_click()
Mail_ActiveSheet
End Sub

--
Regards,
Tom Ogilvy


"Slugger" wrote in message
...
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 ", _
"This is the Subject line"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

Thanks!

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" wrote in message
...
In contrast, it worked flawlessly for me and the form did have the focus.

--
Regards,
Tom Ogilvy


"Bob Phillips" wrote in message
...
That didn't work for me on a form, presumably because the form has
focus?

Bob


"Tom Ogilvy" wrote in message
...
Private Sub Commandbutton1_click()
Mail_ActiveSheet
End Sub

--
Regards,
Tom Ogilvy


"Slugger" wrote in message
...
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 ", _
"This is the Subject line"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

Thanks!

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.

--
Regards,
Tom Ogilvy



"Bob Phillips" wrote in message
...
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" wrote in message
...
In contrast, it worked flawlessly for me and the form did have the
focus.

--
Regards,
Tom Ogilvy


"Bob Phillips" wrote in message
...
That didn't work for me on a form, presumably because the form has
focus?

Bob


"Tom Ogilvy" wrote in message
...
Private Sub Commandbutton1_click()
Mail_ActiveSheet
End Sub

--
Regards,
Tom Ogilvy


"Slugger" wrote in message
...
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 ", _
"This is the Subject line"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

Thanks!