Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Email error Object dosen't support this property or method
Hello from Steved
Before I made the changes below it worked fine. The error message is "Object dosen't support this property or method" I made 2 changes to below 1 is to ask the worksheet to change to values. 2 to save file. Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value .SaveAs "Sheet " & Shname(N) _ & " " & strdate & "C:/Audit Reports/ Audit Summary.xls" Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy") Shname = Array("Summary all Depots", "City Depot (1)", "Roskill Depot (2)", "Papakura Depot (3)", "Wiri Depot (4)", "Shore Depot (5)", "Orewa Depot (6)", "Swanson Depot (7)", "Panmure Depot (8)") Addr = ", ", ", ", ", ", ", ", ") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value .SaveAs "Sheet " & Shname(N) _ & " " & strdate & "C:/Audit Reports/ Audit Summary.xls" .SendMail Addr(N), _ "Audit Summary Report" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Next N Application.ScreenUpdating = True End Sub Thankyou. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Email error Object dosen't support this property or method
Hi Steved
You try to save ActiveSheet.UsedRange instead of ActiveWorkbook(or wb) Use it like this Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value End With With wb .SaveAs "Sheet .................... End With -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello from Steved Before I made the changes below it worked fine. The error message is "Object dosen't support this property or method" I made 2 changes to below 1 is to ask the worksheet to change to values. 2 to save file. Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value .SaveAs "Sheet " & Shname(N) _ & " " & strdate & "C:/Audit Reports/ Audit Summary.xls" Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy") Shname = Array("Summary all Depots", "City Depot (1)", "Roskill Depot (2)", "Papakura Depot (3)", "Wiri Depot (4)", "Shore Depot (5)", "Orewa Depot (6)", "Swanson Depot (7)", "Panmure Depot (8)") Addr = ", ", ", ", ", ", ", ", ") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value .SaveAs "Sheet " & Shname(N) _ & " " & strdate & "C:/Audit Reports/ Audit Summary.xls" .SendMail Addr(N), _ "Audit Summary Report" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Next N Application.ScreenUpdating = True End Sub Thankyou. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Email error Object dosen't support this property or method
Hello Ron from Steved
Ron It is highliting .SendMail of the below and is giving me a error Invalid or unqualified reference Please what have i got wrong thankyou. ..SendMail Addr(N), _ Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy") Shname = Array("Summary", "City Depot (1)", "Roskill Depot (2)", "Papakura Depot (3)", "Wiri Depot (4)", "Shore Depot (5)", "Orewa Depot (6)", "Swanson Depot (7)", "Panmure Depot (8)") Addr = ", ", ", ", ", ", ", ", ") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value End With With wb .SaveAs "C:/Audit Reports/ Audit Summary.xls" End With .SendMail Addr(N), _ "Audit Summary Report" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Next N Application.ScreenUpdating = True End Sub "Ron de Bruin" wrote: Hi Steved You try to save ActiveSheet.UsedRange instead of ActiveWorkbook(or wb) Use it like this Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value End With With wb .SaveAs "Sheet .................... End With -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello from Steved Before I made the changes below it worked fine. The error message is "Object dosen't support this property or method" I made 2 changes to below 1 is to ask the worksheet to change to values. 2 to save file. Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value .SaveAs "Sheet " & Shname(N) _ & " " & strdate & "C:/Audit Reports/ Audit Summary.xls" Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy") Shname = Array("Summary all Depots", "City Depot (1)", "Roskill Depot (2)", "Papakura Depot (3)", "Wiri Depot (4)", "Shore Depot (5)", "Orewa Depot (6)", "Swanson Depot (7)", "Panmure Depot (8)") Addr = ", ", ", ", ", ", ", ", ") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value .SaveAs "Sheet " & Shname(N) _ & " " & strdate & "C:/Audit Reports/ Audit Summary.xls" .SendMail Addr(N), _ "Audit Summary Report" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Next N Application.ScreenUpdating = True End Sub Thankyou. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Email error Object dosen't support this property or method
Hi Steve
You must remove the End with after the SaveAs line (there is one after the close line) Only add this part in the original macro (example 2) http://www.rondebruin.nl/mail/folder1/mail2.htm With ActiveSheet.UsedRange .Value = .Value End With -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello Ron from Steved Ron It is highliting .SendMail of the below and is giving me a error Invalid or unqualified reference Please what have i got wrong thankyou. .SendMail Addr(N), _ Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy") Shname = Array("Summary", "City Depot (1)", "Roskill Depot (2)", "Papakura Depot (3)", "Wiri Depot (4)", "Shore Depot (5)", "Orewa Depot (6)", "Swanson Depot (7)", "Panmure Depot (8)") Addr = ", ", ", ", ", ", ", ", ") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value End With With wb .SaveAs "C:/Audit Reports/ Audit Summary.xls" End With .SendMail Addr(N), _ "Audit Summary Report" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Next N Application.ScreenUpdating = True End Sub "Ron de Bruin" wrote: Hi Steved You try to save ActiveSheet.UsedRange instead of ActiveWorkbook(or wb) Use it like this Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value End With With wb .SaveAs "Sheet .................... End With -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello from Steved Before I made the changes below it worked fine. The error message is "Object dosen't support this property or method" I made 2 changes to below 1 is to ask the worksheet to change to values. 2 to save file. Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value .SaveAs "Sheet " & Shname(N) _ & " " & strdate & "C:/Audit Reports/ Audit Summary.xls" Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy") Shname = Array("Summary all Depots", "City Depot (1)", "Roskill Depot (2)", "Papakura Depot (3)", "Wiri Depot (4)", "Shore Depot (5)", "Orewa Depot (6)", "Swanson Depot (7)", "Panmure Depot (8)") Addr = ", ", ", ", ", ", ", ", ") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value .SaveAs "Sheet " & Shname(N) _ & " " & strdate & "C:/Audit Reports/ Audit Summary.xls" .SendMail Addr(N), _ "Audit Summary Report" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Next N Application.ScreenUpdating = True End Sub Thankyou. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Email error Object dosen't support this property or method
Hello Ron From Steved
Excellent thankyou one question please ..SaveAs "C:/Audit Reports/ Audit Summary.xls" can I change it to ..SaveAs "C:/Audit Reports/ dd-mm-yy.xls" The reason is that when saved it is given a name automatically in this case a date. "Ron de Bruin" wrote: Hi Steve You must remove the End with after the SaveAs line (there is one after the close line) Only add this part in the original macro (example 2) http://www.rondebruin.nl/mail/folder1/mail2.htm With ActiveSheet.UsedRange .Value = .Value End With -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello Ron from Steved Ron It is highliting .SendMail of the below and is giving me a error Invalid or unqualified reference Please what have i got wrong thankyou. .SendMail Addr(N), _ Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy") Shname = Array("Summary", "City Depot (1)", "Roskill Depot (2)", "Papakura Depot (3)", "Wiri Depot (4)", "Shore Depot (5)", "Orewa Depot (6)", "Swanson Depot (7)", "Panmure Depot (8)") Addr = ", ", ", ", ", ", ", ", ") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value End With With wb .SaveAs "C:/Audit Reports/ Audit Summary.xls" End With .SendMail Addr(N), _ "Audit Summary Report" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Next N Application.ScreenUpdating = True End Sub "Ron de Bruin" wrote: Hi Steved You try to save ActiveSheet.UsedRange instead of ActiveWorkbook(or wb) Use it like this Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value End With With wb .SaveAs "Sheet .................... End With -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello from Steved Before I made the changes below it worked fine. The error message is "Object dosen't support this property or method" I made 2 changes to below 1 is to ask the worksheet to change to values. 2 to save file. Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value .SaveAs "Sheet " & Shname(N) _ & " " & strdate & "C:/Audit Reports/ Audit Summary.xls" Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy") Shname = Array("Summary all Depots", "City Depot (1)", "Roskill Depot (2)", "Papakura Depot (3)", "Wiri Depot (4)", "Shore Depot (5)", "Orewa Depot (6)", "Swanson Depot (7)", "Panmure Depot (8)") Addr = ", ", ", ", ", ", ", ", ") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value .SaveAs "Sheet " & Shname(N) _ & " " & strdate & "C:/Audit Reports/ Audit Summary.xls" .SendMail Addr(N), _ "Audit Summary Report" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Next N Application.ScreenUpdating = True End Sub Thankyou. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Email error Object dosen't support this property or method
Hi Steve
Use this then ..SaveAs "C:/Audit Reports/" & Format(Date, "dd-mm-yy") & ".xls" -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello Ron From Steved Excellent thankyou one question please .SaveAs "C:/Audit Reports/ Audit Summary.xls" can I change it to .SaveAs "C:/Audit Reports/ dd-mm-yy.xls" The reason is that when saved it is given a name automatically in this case a date. "Ron de Bruin" wrote: Hi Steve You must remove the End with after the SaveAs line (there is one after the close line) Only add this part in the original macro (example 2) http://www.rondebruin.nl/mail/folder1/mail2.htm With ActiveSheet.UsedRange .Value = .Value End With -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello Ron from Steved Ron It is highliting .SendMail of the below and is giving me a error Invalid or unqualified reference Please what have i got wrong thankyou. .SendMail Addr(N), _ Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy") Shname = Array("Summary", "City Depot (1)", "Roskill Depot (2)", "Papakura Depot (3)", "Wiri Depot (4)", "Shore Depot (5)", "Orewa Depot (6)", "Swanson Depot (7)", "Panmure Depot (8)") Addr = ", ", ", ", ", ", ", ", ") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value End With With wb .SaveAs "C:/Audit Reports/ Audit Summary.xls" End With .SendMail Addr(N), _ "Audit Summary Report" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Next N Application.ScreenUpdating = True End Sub "Ron de Bruin" wrote: Hi Steved You try to save ActiveSheet.UsedRange instead of ActiveWorkbook(or wb) Use it like this Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value End With With wb .SaveAs "Sheet .................... End With -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello from Steved Before I made the changes below it worked fine. The error message is "Object dosen't support this property or method" I made 2 changes to below 1 is to ask the worksheet to change to values. 2 to save file. Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value .SaveAs "Sheet " & Shname(N) _ & " " & strdate & "C:/Audit Reports/ Audit Summary.xls" Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy") Shname = Array("Summary all Depots", "City Depot (1)", "Roskill Depot (2)", "Papakura Depot (3)", "Wiri Depot (4)", "Shore Depot (5)", "Orewa Depot (6)", "Swanson Depot (7)", "Panmure Depot (8)") Addr = ", ", ", ", ", ", ", ", ") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value .SaveAs "Sheet " & Shname(N) _ & " " & strdate & "C:/Audit Reports/ Audit Summary.xls" .SendMail Addr(N), _ "Audit Summary Report" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Next N Application.ScreenUpdating = True End Sub Thankyou. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Email error Object dosen't support this property or method
Hello Ron from Steved
Ron You have explained to me what I should be doing, But I am still missing something here 1. It will not save, but if I delete the below it will save each email which I only need it to save the complete workbook once. ..ChangeFileAccess xlReadOnly Kill .FullName 2. When I test the email and open it it has not valued the worksheet hence it is looking for ref etc. I thankyou for your patience with me, I know Ron I am close so I am hoping you will look at the below and tell me where I have gone wrong or not doing correctly. Thankyou once again for your time on my issue. Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy") Shname = Array("Summary", "City Depot (1)", "Roskill Depot (2)", "Papakura Depot (3)", "Wiri Depot (4)", "Shore Depot (5)", "Orewa Depot (6)", "Swanson Depot (7)", "Panmure Depot (8)") Addr = ", ", ", ", ", ", ", ", ") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value End With With wb .SaveAs "C:/Audit Reports/" & Format(Date, "dd-mm-yy") & ".xls" .SendMail Addr(N), _ "Audit Summary Report" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Next N Application.ScreenUpdating = True End Sub "Ron de Bruin" wrote: Hi Steve Use this then ..SaveAs "C:/Audit Reports/" & Format(Date, "dd-mm-yy") & ".xls" -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello Ron From Steved Excellent thankyou one question please .SaveAs "C:/Audit Reports/ Audit Summary.xls" can I change it to .SaveAs "C:/Audit Reports/ dd-mm-yy.xls" The reason is that when saved it is given a name automatically in this case a date. "Ron de Bruin" wrote: Hi Steve You must remove the End with after the SaveAs line (there is one after the close line) Only add this part in the original macro (example 2) http://www.rondebruin.nl/mail/folder1/mail2.htm With ActiveSheet.UsedRange .Value = .Value End With -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello Ron from Steved Ron It is highliting .SendMail of the below and is giving me a error Invalid or unqualified reference Please what have i got wrong thankyou. .SendMail Addr(N), _ Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy") Shname = Array("Summary", "City Depot (1)", "Roskill Depot (2)", "Papakura Depot (3)", "Wiri Depot (4)", "Shore Depot (5)", "Orewa Depot (6)", "Swanson Depot (7)", "Panmure Depot (8)") Addr = ", ", ", ", ", ", ", ", ") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value End With With wb .SaveAs "C:/Audit Reports/ Audit Summary.xls" End With .SendMail Addr(N), _ "Audit Summary Report" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Next N Application.ScreenUpdating = True End Sub "Ron de Bruin" wrote: Hi Steved You try to save ActiveSheet.UsedRange instead of ActiveWorkbook(or wb) Use it like this Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value End With With wb .SaveAs "Sheet .................... End With -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello from Steved Before I made the changes below it worked fine. The error message is "Object dosen't support this property or method" I made 2 changes to below 1 is to ask the worksheet to change to values. 2 to save file. Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value .SaveAs "Sheet " & Shname(N) _ & " " & strdate & "C:/Audit Reports/ Audit Summary.xls" Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy") Shname = Array("Summary all Depots", "City Depot (1)", "Roskill Depot (2)", "Papakura Depot (3)", "Wiri Depot (4)", "Shore Depot (5)", "Orewa Depot (6)", "Swanson Depot (7)", "Panmure Depot (8)") Addr = ", ", ", ", ", ", ", ", ") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value .SaveAs "Sheet " & Shname(N) _ & " " & strdate & "C:/Audit Reports/ Audit Summary.xls" .SendMail Addr(N), _ "Audit Summary Report" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Next N Application.ScreenUpdating = True End Sub Thankyou. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Email error Object dosen't support this property or method
Hi Steve
It will create a file from each sheet and save this new sheet in the loop wb is the workbook you save/send with one sheet If you want to keep the files you must delete this lines .ChangeFileAccess xlReadOnly Kill .FullName But then you must also use the time in the filename to create unique file names. ..SaveAs "C:/Audit Reports/" & Format(Now, "dd-mm-yy hh-mm-ss") & ".xls" Remember that you can also check the file you send in the mails in your outbox. You have a copy there if you need it. 2. When I test the email and open it it has not valued the worksheet hence it is looking for ref etc. Test it again Steve -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello Ron from Steved Ron You have explained to me what I should be doing, But I am still missing something here 1. It will not save, but if I delete the below it will save each email which I only need it to save the complete workbook once. .ChangeFileAccess xlReadOnly Kill .FullName 2. When I test the email and open it it has not valued the worksheet hence it is looking for ref etc. I thankyou for your patience with me, I know Ron I am close so I am hoping you will look at the below and tell me where I have gone wrong or not doing correctly. Thankyou once again for your time on my issue. Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy") Shname = Array("Summary", "City Depot (1)", "Roskill Depot (2)", "Papakura Depot (3)", "Wiri Depot (4)", "Shore Depot (5)", "Orewa Depot (6)", "Swanson Depot (7)", "Panmure Depot (8)") Addr = ", ", ", ", ", ", ", ", ") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value End With With wb .SaveAs "C:/Audit Reports/" & Format(Date, "dd-mm-yy") & ".xls" .SendMail Addr(N), _ "Audit Summary Report" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Next N Application.ScreenUpdating = True End Sub "Ron de Bruin" wrote: Hi Steve Use this then ..SaveAs "C:/Audit Reports/" & Format(Date, "dd-mm-yy") & ".xls" -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello Ron From Steved Excellent thankyou one question please .SaveAs "C:/Audit Reports/ Audit Summary.xls" can I change it to .SaveAs "C:/Audit Reports/ dd-mm-yy.xls" The reason is that when saved it is given a name automatically in this case a date. "Ron de Bruin" wrote: Hi Steve You must remove the End with after the SaveAs line (there is one after the close line) Only add this part in the original macro (example 2) http://www.rondebruin.nl/mail/folder1/mail2.htm With ActiveSheet.UsedRange .Value = .Value End With -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello Ron from Steved Ron It is highliting .SendMail of the below and is giving me a error Invalid or unqualified reference Please what have i got wrong thankyou. .SendMail Addr(N), _ Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy") Shname = Array("Summary", "City Depot (1)", "Roskill Depot (2)", "Papakura Depot (3)", "Wiri Depot (4)", "Shore Depot (5)", "Orewa Depot (6)", "Swanson Depot (7)", "Panmure Depot (8)") Addr = ", ", ", ", ", ", ", ", ") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value End With With wb .SaveAs "C:/Audit Reports/ Audit Summary.xls" End With .SendMail Addr(N), _ "Audit Summary Report" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Next N Application.ScreenUpdating = True End Sub "Ron de Bruin" wrote: Hi Steved You try to save ActiveSheet.UsedRange instead of ActiveWorkbook(or wb) Use it like this Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value End With With wb .SaveAs "Sheet .................... End With -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello from Steved Before I made the changes below it worked fine. The error message is "Object dosen't support this property or method" I made 2 changes to below 1 is to ask the worksheet to change to values. 2 to save file. Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value .SaveAs "Sheet " & Shname(N) _ & " " & strdate & "C:/Audit Reports/ Audit Summary.xls" Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy") Shname = Array("Summary all Depots", "City Depot (1)", "Roskill Depot (2)", "Papakura Depot (3)", "Wiri Depot (4)", "Shore Depot (5)", "Orewa Depot (6)", "Swanson Depot (7)", "Panmure Depot (8)") Addr = ", ", ", ", ", ", ", ", ") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value .SaveAs "Sheet " & Shname(N) _ & " " & strdate & "C:/Audit Reports/ Audit Summary.xls" .SendMail Addr(N), _ "Audit Summary Report" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Next N Application.ScreenUpdating = True End Sub Thankyou. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Email error Object dosen't support this property or method
Hello Ron From Steved
Yes RTon it now saves but it is not valuing the sheet ie past special values. Could you help me on this please I know it works but I must have done something to your code. Would you be so kind and look at the code and tell me what I've done wrong. Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy") Shname = Array("Summary", "City Depot (1)", "Roskill Depot (2)", "Papakura Depot (3)", "Wiri Depot (4)", "Shore Depot (5)", "Orewa Depot (6)", "Swanson Depot (7)", "Panmure Depot (8)") Addr = ", ", ", ", ", ", ", ", ") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value End With With wb .SaveAs "C:/Audit Reports/" & Format(Now, "dd-mm-yy hh-mm-ss") & ".xls" .SendMail Addr(N), _ "Audit Summary Report" .Close False End With Next N Application.ScreenUpdating = True End Sub Thanks Ron "Ron de Bruin" wrote: Hi Steve It will create a file from each sheet and save this new sheet in the loop wb is the workbook you save/send with one sheet If you want to keep the files you must delete this lines .ChangeFileAccess xlReadOnly Kill .FullName But then you must also use the time in the filename to create unique file names. ..SaveAs "C:/Audit Reports/" & Format(Now, "dd-mm-yy hh-mm-ss") & ".xls" Remember that you can also check the file you send in the mails in your outbox. You have a copy there if you need it. 2. When I test the email and open it it has not valued the worksheet hence it is looking for ref etc. Test it again Steve -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello Ron from Steved Ron You have explained to me what I should be doing, But I am still missing something here 1. It will not save, but if I delete the below it will save each email which I only need it to save the complete workbook once. .ChangeFileAccess xlReadOnly Kill .FullName 2. When I test the email and open it it has not valued the worksheet hence it is looking for ref etc. I thankyou for your patience with me, I know Ron I am close so I am hoping you will look at the below and tell me where I have gone wrong or not doing correctly. Thankyou once again for your time on my issue. Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy") Shname = Array("Summary", "City Depot (1)", "Roskill Depot (2)", "Papakura Depot (3)", "Wiri Depot (4)", "Shore Depot (5)", "Orewa Depot (6)", "Swanson Depot (7)", "Panmure Depot (8)") Addr = ", ", ", ", ", ", ", ", ") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value End With With wb .SaveAs "C:/Audit Reports/" & Format(Date, "dd-mm-yy") & ".xls" .SendMail Addr(N), _ "Audit Summary Report" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Next N Application.ScreenUpdating = True End Sub "Ron de Bruin" wrote: Hi Steve Use this then ..SaveAs "C:/Audit Reports/" & Format(Date, "dd-mm-yy") & ".xls" -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello Ron From Steved Excellent thankyou one question please .SaveAs "C:/Audit Reports/ Audit Summary.xls" can I change it to .SaveAs "C:/Audit Reports/ dd-mm-yy.xls" The reason is that when saved it is given a name automatically in this case a date. "Ron de Bruin" wrote: Hi Steve You must remove the End with after the SaveAs line (there is one after the close line) Only add this part in the original macro (example 2) http://www.rondebruin.nl/mail/folder1/mail2.htm With ActiveSheet.UsedRange .Value = .Value End With -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello Ron from Steved Ron It is highliting .SendMail of the below and is giving me a error Invalid or unqualified reference Please what have i got wrong thankyou. .SendMail Addr(N), _ Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy") Shname = Array("Summary", "City Depot (1)", "Roskill Depot (2)", "Papakura Depot (3)", "Wiri Depot (4)", "Shore Depot (5)", "Orewa Depot (6)", "Swanson Depot (7)", "Panmure Depot (8)") Addr = ", ", ", ", ", ", ", ", ") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value End With With wb .SaveAs "C:/Audit Reports/ Audit Summary.xls" End With .SendMail Addr(N), _ "Audit Summary Report" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Next N Application.ScreenUpdating = True End Sub "Ron de Bruin" wrote: Hi Steved You try to save ActiveSheet.UsedRange instead of ActiveWorkbook(or wb) Use it like this Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value End With With wb .SaveAs "Sheet .................... End With -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello from Steved Before I made the changes below it worked fine. The error message is "Object dosen't support this property or method" I made 2 changes to below 1 is to ask the worksheet to change to values. 2 to save file. Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value .SaveAs "Sheet " & Shname(N) _ & " " & strdate & "C:/Audit Reports/ Audit Summary.xls" Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy") Shname = Array("Summary all Depots", "City Depot (1)", "Roskill Depot (2)", "Papakura Depot (3)", "Wiri Depot (4)", "Shore Depot (5)", "Orewa Depot (6)", "Swanson Depot (7)", "Panmure Depot (8)") Addr = ", ", ", ", ", ", ", ", ") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value .SaveAs "Sheet " & Shname(N) _ & " " & strdate & "C:/Audit Reports/ Audit Summary.xls" .SendMail Addr(N), _ "Audit Summary Report" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Next N Application.ScreenUpdating = True End Sub Thankyou. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Email error Object dosen't support this property or method
Strange, working correct for me
For PasteSpecial see the example here http://www.rondebruin.nl/mail/tips1.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello Ron From Steved Yes RTon it now saves but it is not valuing the sheet ie past special values. Could you help me on this please I know it works but I must have done something to your code. Would you be so kind and look at the code and tell me what I've done wrong. Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy") Shname = Array("Summary", "City Depot (1)", "Roskill Depot (2)", "Papakura Depot (3)", "Wiri Depot (4)", "Shore Depot (5)", "Orewa Depot (6)", "Swanson Depot (7)", "Panmure Depot (8)") Addr = ", ", ", ", ", ", ", ", ") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value End With With wb .SaveAs "C:/Audit Reports/" & Format(Now, "dd-mm-yy hh-mm-ss") & ".xls" .SendMail Addr(N), _ "Audit Summary Report" .Close False End With Next N Application.ScreenUpdating = True End Sub Thanks Ron "Ron de Bruin" wrote: Hi Steve It will create a file from each sheet and save this new sheet in the loop wb is the workbook you save/send with one sheet If you want to keep the files you must delete this lines .ChangeFileAccess xlReadOnly Kill .FullName But then you must also use the time in the filename to create unique file names. ..SaveAs "C:/Audit Reports/" & Format(Now, "dd-mm-yy hh-mm-ss") & ".xls" Remember that you can also check the file you send in the mails in your outbox. You have a copy there if you need it. 2. When I test the email and open it it has not valued the worksheet hence it is looking for ref etc. Test it again Steve -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello Ron from Steved Ron You have explained to me what I should be doing, But I am still missing something here 1. It will not save, but if I delete the below it will save each email which I only need it to save the complete workbook once. .ChangeFileAccess xlReadOnly Kill .FullName 2. When I test the email and open it it has not valued the worksheet hence it is looking for ref etc. I thankyou for your patience with me, I know Ron I am close so I am hoping you will look at the below and tell me where I have gone wrong or not doing correctly. Thankyou once again for your time on my issue. Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy") Shname = Array("Summary", "City Depot (1)", "Roskill Depot (2)", "Papakura Depot (3)", "Wiri Depot (4)", "Shore Depot (5)", "Orewa Depot (6)", "Swanson Depot (7)", "Panmure Depot (8)") Addr = ", ", ", ", ", ", ", ", ") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value End With With wb .SaveAs "C:/Audit Reports/" & Format(Date, "dd-mm-yy") & ".xls" .SendMail Addr(N), _ "Audit Summary Report" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Next N Application.ScreenUpdating = True End Sub "Ron de Bruin" wrote: Hi Steve Use this then ..SaveAs "C:/Audit Reports/" & Format(Date, "dd-mm-yy") & ".xls" -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello Ron From Steved Excellent thankyou one question please .SaveAs "C:/Audit Reports/ Audit Summary.xls" can I change it to .SaveAs "C:/Audit Reports/ dd-mm-yy.xls" The reason is that when saved it is given a name automatically in this case a date. "Ron de Bruin" wrote: Hi Steve You must remove the End with after the SaveAs line (there is one after the close line) Only add this part in the original macro (example 2) http://www.rondebruin.nl/mail/folder1/mail2.htm With ActiveSheet.UsedRange .Value = .Value End With -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello Ron from Steved Ron It is highliting .SendMail of the below and is giving me a error Invalid or unqualified reference Please what have i got wrong thankyou. .SendMail Addr(N), _ Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy") Shname = Array("Summary", "City Depot (1)", "Roskill Depot (2)", "Papakura Depot (3)", "Wiri Depot (4)", "Shore Depot (5)", "Orewa Depot (6)", "Swanson Depot (7)", "Panmure Depot (8)") Addr = ", ", ", ", ", ", ", ", ") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value End With With wb .SaveAs "C:/Audit Reports/ Audit Summary.xls" End With .SendMail Addr(N), _ "Audit Summary Report" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Next N Application.ScreenUpdating = True End Sub "Ron de Bruin" wrote: Hi Steved You try to save ActiveSheet.UsedRange instead of ActiveWorkbook(or wb) Use it like this Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value End With With wb .SaveAs "Sheet .................... End With -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello from Steved Before I made the changes below it worked fine. The error message is "Object dosen't support this property or method" I made 2 changes to below 1 is to ask the worksheet to change to values. 2 to save file. Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value .SaveAs "Sheet " & Shname(N) _ & " " & strdate & "C:/Audit Reports/ Audit Summary.xls" Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy") Shname = Array("Summary all Depots", "City Depot (1)", "Roskill Depot (2)", "Papakura Depot (3)", "Wiri Depot (4)", "Shore Depot (5)", "Orewa Depot (6)", "Swanson Depot (7)", "Panmure Depot (8)") Addr = ", ", ", ", ", ", ", ", ") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value .SaveAs "Sheet " & Shname(N) _ & " " & strdate & "C:/Audit Reports/ Audit Summary.xls" .SendMail Addr(N), _ "Audit Summary Report" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Next N Application.ScreenUpdating = True End Sub Thankyou. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Email error Object dosen't support this property or method
Thanks Ron.
I'll go to your site and copy the code. Cheers. "Ron de Bruin" wrote: Strange, working correct for me For PasteSpecial see the example here http://www.rondebruin.nl/mail/tips1.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello Ron From Steved Yes RTon it now saves but it is not valuing the sheet ie past special values. Could you help me on this please I know it works but I must have done something to your code. Would you be so kind and look at the code and tell me what I've done wrong. Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy") Shname = Array("Summary", "City Depot (1)", "Roskill Depot (2)", "Papakura Depot (3)", "Wiri Depot (4)", "Shore Depot (5)", "Orewa Depot (6)", "Swanson Depot (7)", "Panmure Depot (8)") Addr = ", ", ", ", ", ", ", ", ") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value End With With wb .SaveAs "C:/Audit Reports/" & Format(Now, "dd-mm-yy hh-mm-ss") & ".xls" .SendMail Addr(N), _ "Audit Summary Report" .Close False End With Next N Application.ScreenUpdating = True End Sub Thanks Ron "Ron de Bruin" wrote: Hi Steve It will create a file from each sheet and save this new sheet in the loop wb is the workbook you save/send with one sheet If you want to keep the files you must delete this lines .ChangeFileAccess xlReadOnly Kill .FullName But then you must also use the time in the filename to create unique file names. ..SaveAs "C:/Audit Reports/" & Format(Now, "dd-mm-yy hh-mm-ss") & ".xls" Remember that you can also check the file you send in the mails in your outbox. You have a copy there if you need it. 2. When I test the email and open it it has not valued the worksheet hence it is looking for ref etc. Test it again Steve -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello Ron from Steved Ron You have explained to me what I should be doing, But I am still missing something here 1. It will not save, but if I delete the below it will save each email which I only need it to save the complete workbook once. .ChangeFileAccess xlReadOnly Kill .FullName 2. When I test the email and open it it has not valued the worksheet hence it is looking for ref etc. I thankyou for your patience with me, I know Ron I am close so I am hoping you will look at the below and tell me where I have gone wrong or not doing correctly. Thankyou once again for your time on my issue. Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy") Shname = Array("Summary", "City Depot (1)", "Roskill Depot (2)", "Papakura Depot (3)", "Wiri Depot (4)", "Shore Depot (5)", "Orewa Depot (6)", "Swanson Depot (7)", "Panmure Depot (8)") Addr = ", ", ", ", ", ", ", ", ") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value End With With wb .SaveAs "C:/Audit Reports/" & Format(Date, "dd-mm-yy") & ".xls" .SendMail Addr(N), _ "Audit Summary Report" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Next N Application.ScreenUpdating = True End Sub "Ron de Bruin" wrote: Hi Steve Use this then ..SaveAs "C:/Audit Reports/" & Format(Date, "dd-mm-yy") & ".xls" -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello Ron From Steved Excellent thankyou one question please .SaveAs "C:/Audit Reports/ Audit Summary.xls" can I change it to .SaveAs "C:/Audit Reports/ dd-mm-yy.xls" The reason is that when saved it is given a name automatically in this case a date. "Ron de Bruin" wrote: Hi Steve You must remove the End with after the SaveAs line (there is one after the close line) Only add this part in the original macro (example 2) http://www.rondebruin.nl/mail/folder1/mail2.htm With ActiveSheet.UsedRange .Value = .Value End With -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello Ron from Steved Ron It is highliting .SendMail of the below and is giving me a error Invalid or unqualified reference Please what have i got wrong thankyou. .SendMail Addr(N), _ Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy") Shname = Array("Summary", "City Depot (1)", "Roskill Depot (2)", "Papakura Depot (3)", "Wiri Depot (4)", "Shore Depot (5)", "Orewa Depot (6)", "Swanson Depot (7)", "Panmure Depot (8)") Addr = ", ", ", ", ", ", ", ", ") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value End With With wb .SaveAs "C:/Audit Reports/ Audit Summary.xls" End With .SendMail Addr(N), _ "Audit Summary Report" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Next N Application.ScreenUpdating = True End Sub "Ron de Bruin" wrote: Hi Steved You try to save ActiveSheet.UsedRange instead of ActiveWorkbook(or wb) Use it like this Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value End With With wb .SaveAs "Sheet .................... End With -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello from Steved Before I made the changes below it worked fine. The error message is "Object dosen't support this property or method" I made 2 changes to below 1 is to ask the worksheet to change to values. 2 to save file. Set wb = ActiveWorkbook With ActiveSheet.UsedRange .Value = .Value |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
runtime error 434, object does not support this method or property | Excel Discussion (Misc queries) | |||
VBA error: "object doesn't support this property or method" | Excel Discussion (Misc queries) | |||
Object doesn't support this property or method (Error 438) | Excel Discussion (Misc queries) | |||
Object doesn't support this property or method | Excel Programming | |||
Run Time Error 438 - Object doesn't support the property or method | Excel Programming |