Email Syntax error

[Steved]

Hello from Steved

On the below line I'm receivinfg a compile error Syntax error

..SendMail"(e-mail address removed)",_

Would somebody be kind enough to point out to me please what is it that I'm
not getting right.

Thankyou.

Sub Mail_Todepot()
Dim wb As Workbook
Dim strdate As String

strdate = Format(Now, "dd-mm-yy")

Application.ScreenUpdating = False

Sheets("To Depot").Copy
ActiveSheet.Copy
Cells.Copy
Cells.PasteSpecial xlPasteValues
Cells(1).Select
Worksheets(1).Select
Application.CutCopyMode = False

Set wb = ActiveWorkbook
With wb

.SaveAs "C:/To Depots/Kilometres.xls"

.SendMail"(e-mail address removed)",_

"Kilometres Per Bus"

.ChangeFileAccessxlReadOnly
Kill.FullName

.Close False

End With

Application.ScreenUpdating = True
End Sub

 

[Ron de Bruin]

Hi Steve

First you have two copy lines

Sheets("To Depot").Copy
ActiveSheet.Copy

remove the second one

.SaveAs "C:/To Depots/Kilometres.xls"

.SendMail"(e-mail address removed)",_

Must be

.SendMail "(e-mail address removed)", _
"Kilometres Per Bus"

note the space before the _

 

[Steved]

Hello Ron From Steved

this part of below Worksheets(1).Select gives me a run error 1004

Ron The macro copies the sheet too a new workbook, I am fine with that
but I do not want my origional workbook to copy, past special values, this is
sugguesting to me that i've not quiet got this correct.

In my mind I believe it should copy the worksheet named "To Depots" to a new
workbook which it is doing, question at this point can the macro then copy
past special value.

I thankyou

Cells.Copy
Cells.PasteSpecial xlPasteValues
Cells(1).Select
Worksheets(1).Select
Application.CutCopyMode = False




Sub Mail_Todepot()
Dim wb As Workbook
Dim strdate As String

strdate = Format(Now, "dd-mm-yy")

Application.ScreenUpdating = False

Sheets("To Depots").Copy

Cells.Copy
Cells.PasteSpecial xlPasteValues
Cells(1).Select
Worksheets(1).Select
Application.CutCopyMode = False

Set wb = ActiveWorkbook
With wb

.SendMail "(e-mail address removed)", _
"Kilometres Per Bus"

.Close False

End With

Application.ScreenUpdating = True
End Sub

 

[Ron de Bruin]

Hi Steve

If you use this

Sheets("To Depots").Copy

It will create a new workbook with this sheet
The one sheet workbook is the active one now so you can use this to make values

Cells.Copy
Cells.PasteSpecial xlPasteValues
Cells(1).Select
Application.CutCopyMode = False


Have you try it ???

 

[Steved]

Hello Ron from Steved

The below is what I am running

two issues
1 when I run it I get a error 400

2 it values the sheet that it is copying from then creates a new workbook.

Sub Mail_Todepot()
Dim wb As Workbook
Dim strdate As String

strdate = Format(Now, "dd-mm-yy")

Application.ScreenUpdating = False

Sheets("To Depots").Copy

Cells.Copy
Cells.PasteSpecial xlPasteValues
Cells(1).Select
Application.CutCopyMode = False

Set wb = ActiveWorkbook
With wb


.SendMail "(e-mail address removed)", _
"Kilometres Per Bus"

.Close False

End With

Application.ScreenUpdating = True
End Sub

 

[Ron de Bruin]

Send me the workbook private Steve
I will look at your problem tomorrow after work

 

[Steved]

Thanks Ron

Before I do what you requested I'll going to try an idea.

because this is part of a workbook with many sheets I am going to

put it in a file off its own then work through What I am not doing correctly.

Cheers.

 

[Steved]

Hello Ron from Steved

Ron I seperated out the worksheet to depots to a individual workbook

I then ran the macro and had no issues as you have designed it to do.

So the issue I have to fiquire is why will it not operate on multiple sheets.

I'll work on it hopping I can workout what I've done different.

Thankyou.

 

[Steved]

Hello Ron from Steved

oops I forgot what you advised me to do, and that is make sure the macro is
in the right module. Thanks Ron as soon as you said that you would take a
look for me
I knew in my mind I better trace my steps and work it out for myself. I have
other situations where your good macro will help.

Have a nice day. and Once again thanks.