Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
email only first page
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
email only first page
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
email only first page
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" wrote in message ... 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
email only first page
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 ", _ "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" wrote in message ... 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" wrote in message ... 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
email only first page
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 ", _ "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" wrote in message ... 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" wrote in message ... 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 . . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
email only first page
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" wrote in message ... 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 ", _ "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" wrote in message ... 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" wrote in message ... 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 . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
email only first page
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" wrote in message ... 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 ", _ "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" wrote in message ... 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" wrote in message ... 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 . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Email current page | Excel Discussion (Misc queries) | |||
is there any way possible that you can send each page as an email | Excel Discussion (Misc queries) | |||
Is it possible to email only one page of an Excel workbook? | Excel Discussion (Misc queries) | |||
send page by email | Excel Programming | |||
Email one page of information on the same sheet | Excel Programming |