Email error Object dosen't support this property or method

[Steved]

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 = Array("(e-mail address removed)", "(e-mail address removed)",
"(e-mail address removed)", "(e-mail address removed)", "(e-mail address removed)",
"(e-mail address removed)", "(e-mail address removed)", "(e-mail address removed)",
"(e-mail address removed)")

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.

 

[Ron de Bruin]

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

 

[Steved]

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 = Array("(e-mail address removed)", "(e-mail address removed)",
"(e-mail address removed)", "(e-mail address removed)", "(e-mail address removed)",
"(e-mail address removed)", "(e-mail address removed)", "(e-mail address removed)",
"(e-mail address removed)")

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]

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

 

[Steved]

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]

Hi Steve

Use this then

..SaveAs "C:/Audit Reports/" & Format(Date, "dd-mm-yy") & ".xls"

 

[Steved]

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 = Array("(e-mail address removed)", "(e-mail address removed)",
"(e-mail address removed)", "(e-mail address removed)", "(e-mail address removed)",
"(e-mail address removed)", "(e-mail address removed)", "(e-mail address removed)",
"(e-mail address removed)")

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]

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

 

[Steved]

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 = Array("(e-mail address removed)", "(e-mail address removed)",
"(e-mail address removed)", "(e-mail address removed)", "(e-mail address removed)",
"(e-mail address removed)", "(e-mail address removed)", "(e-mail address removed)",
"(e-mail address removed)")

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]

Strange, working correct for me

For PasteSpecial see the example here
http://www.rondebruin.nl/mail/tips1.htm

 

[Steved]

Thanks Ron.

I'll go to your site and copy the code.

Cheers.