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.

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.

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.

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.

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.

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.