It should send a workbook containing the single sheet Usage.

--
Regards,
Tom Ogilvy
"Pete" wrote in message
ups.com...
Can anyone tell me why this Sub sends the Whole Workbook in Excel 97,
what I want to achieve is to send only the "Usage" sheet in all
versions of Excel.

Thanks

Pete


Private Sub MailUsageSheet()
Dim rng As Range
Dim wb As Workbook
Dim Arr() As String
Dim N As Integer
Dim cell As Range
Dim strdate As String

strdate = Format(Sheets("Usage").Range("B6"), "ddd dd mmm yyyy")
Set rng =

Sheets("DropDownLists").Columns("Q").Cells.Special Cells(xlCellTypeConstants)
ReDim Preserve Arr(1 To rng.Cells.Count)
N = 0
For Each cell In rng
If cell.EntireRow.Hidden = False And cell.Value Like "*@*" Then
N = N + 1
Arr(N) = cell.Value
End If
Next cell
ReDim Preserve Arr(1 To N)
Sheets("Usage").Copy
Cells.Copy
Cells.PasteSpecial xlPasteValues
Cells(1).Select
Application.CutCopyMode = False
ActiveWindow.Zoom = 75
Set wb = ActiveWorkbook
With wb
.SaveAs "Sack Usage.xls"
On Error Resume Next
.SendMail Arr, Subject:="Sack Usage " & strdate
On Error GoTo 0
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
End Sub