email only first page

[Logan]

I am present using the following code to email an acitve
sheet. There are six pages in this sheet, I would like to
email just the first page.
Is it possible?


Sub Mail_ActiveSheet()
Dim strDate As String
Dim FName1, FName2, FName3, Fullname
FName1 = "CK0"
FName2 = Range("AU2").Value & "-"
FName3 = Range("J4").Value
Fullname = FName1 & FName2 & FName3
ActiveSheet.Copy
strDate = Format(Date, "dd-mm-yy") & " " & Format(Time,
"h-mm-ss")
ActiveSheet.SaveAs "Sheet1 for " & Fullname _
& " " & strDate & ".xls"
ActiveWorkbook.SendMail "email address", _
Fullname
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.Fullname
ActiveWorkbook.Close False
End Sub

 

[Ron de Bruin]

Hi Logan

You can send a range or the selection
http://www.rondebruin.nl/sendmail.htm#selection

 

[Logan]

Ron I used your Range macro, but the sheet I'm trying to
send is protected. I used it with the protection off but
the setup changed when received and opened.
Is there a way to email a protected sheet and hold the
formatting?

-----Original Message-----
Hi Logan

You can send a range or the selection
http://www.rondebruin.nl/sendmail.htm#selection

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Logan" <[email protected]> wrote in

message news:[email protected]...

 

[Ron de Bruin]

Hi Logan

You can unprotect the sheet and protect the sheet in the same macro.
But It will only copy the format of the cells and not from the sheet

You can copy the whole sheet and delete the rows below page 1. ( is use row 20)
This will copy your sheet settings also to the new workbook

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
wb.Sheets(1).Unprotect
wb.Sheets(1).Rows("20:" & Rows.Count).Delete
wb.Sheets(1).Protect
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

 

[Logan]

Thanks Ron that works.
I did have a Password on that sheet and when I ran the
macro it would ask for the password, the users won't have
the password so I just protected it without the password.

-----Original Message-----
Hi Logan

You can unprotect the sheet and protect the sheet in the same macro.
But It will only copy the format of the cells and not from the sheet

You can copy the whole sheet and delete the rows below page 1. ( is use row 20)
This will copy your sheet settings also to the new workbook

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
wb.Sheets(1).Unprotect
wb.Sheets(1).Rows("20:" & Rows.Count).Delete
wb.Sheets(1).Protect
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





--
Regards Ron de Bruin
http://www.rondebruin.nl


"Logan" <[email protected]> wrote in

message news:[email protected]...

 

[Ron de Bruin]

Hi Logan

You can also fill in the password in the protect/unprotect line

Protect Password:="hi"

 

[Logan]

Thanks again, Ron

-----Original Message-----
Hi Logan

You can also fill in the password in the protect/unprotect line

Protect Password:="hi"


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Logan" <[email protected]> wrote in

message news:[email protected]...