Hello from Steved

I've upgraded from Office 2000 to Office 2007

Below has 2 issues firstly when I email to a client they can open the file
only to get a corrupt worksheet and secondly Kill .FullName gets highlited
please help.

Yes I save the files as a Excel 97-2003 Workbook, as othe only have either
Office 2000 or Office 2003

Sub AnnullEmail()
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 h-mm-ss")
Shname = Array("1-City", "2-Roskill", "3-Papakura", "4-Wiri", "5-Shore",
"6-Orewa", "7-Swanson", "8-Panmure")
Addr = Array("1-Depot", "2-Depot", "4-Depot", "4-Depot", "5-Depot",
"5-Depot", "7-Depot", "1-Depot")

Application.ScreenUpdating = False

For N = LBound(Shname) To UBound(Shname)
Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Sheet " & Shname(N) _
& " " & strdate & ".xls"
.SendMail Addr(N), _
"Depot Annulments"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Next N
Application.ScreenUpdating = True
End Sub

Thankyou.

See the new code here
http://www.rondebruin.nl/mail/folder1/mail2.htm

See example 2

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Steved" wrote in message ...
Hello from Steved

I've upgraded from Office 2000 to Office 2007

Below has 2 issues firstly when I email to a client they can open the file
only to get a corrupt worksheet and secondly Kill .FullName gets highlited
please help.

Yes I save the files as a Excel 97-2003 Workbook, as othe only have either
Office 2000 or Office 2003

Sub AnnullEmail()
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 h-mm-ss")
Shname = Array("1-City", "2-Roskill", "3-Papakura", "4-Wiri", "5-Shore",
"6-Orewa", "7-Swanson", "8-Panmure")
Addr = Array("1-Depot", "2-Depot", "4-Depot", "4-Depot", "5-Depot",
"5-Depot", "7-Depot", "1-Depot")

Application.ScreenUpdating = False

For N = LBound(Shname) To UBound(Shname)
Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Sheet " & Shname(N) _
& " " & strdate & ".xls"
.SendMail Addr(N), _
"Depot Annulments"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Next N
Application.ScreenUpdating = True
End Sub

Thankyou.

Thankyou Ron

"Ron de Bruin" wrote:

See the new code here
http://www.rondebruin.nl/mail/folder1/mail2.htm

See example 2

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Steved" wrote in message ...
Hello from Steved

I've upgraded from Office 2000 to Office 2007

Below has 2 issues firstly when I email to a client they can open the file
only to get a corrupt worksheet and secondly Kill .FullName gets highlited
please help.

Yes I save the files as a Excel 97-2003 Workbook, as othe only have either
Office 2000 or Office 2003

Sub AnnullEmail()
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 h-mm-ss")
Shname = Array("1-City", "2-Roskill", "3-Papakura", "4-Wiri", "5-Shore",
"6-Orewa", "7-Swanson", "8-Panmure")
Addr = Array("1-Depot", "2-Depot", "4-Depot", "4-Depot", "5-Depot",
"5-Depot", "7-Depot", "1-Depot")

Application.ScreenUpdating = False

For N = LBound(Shname) To UBound(Shname)
Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Sheet " & Shname(N) _
& " " & strdate & ".xls"
.SendMail Addr(N), _
"Depot Annulments"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Next N
Application.ScreenUpdating = True
End Sub

Thankyou.