Compatible with Office 97
 

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

Compatible with Office 97
 

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

Compatible with Office 97
 

Hi Tom,

When I Test this out at home on Excel 2003, I get at you describe, but
when sent from a PC with Office 97 on it at Work, I get an exact
replica of the workbook it is sending the "Usage" sheet from, I can't
understand why.

Pete

Compatible with Office 97
 

Sheets("Usage").Copy

Would create a single sheet workbook with the worksheet "Usage" in both
xl97 and xl2003. That workbook would be the activeworkbook in both. So I
don't see any obvious cause for the observed difference.

--
Regards,
Tom Ogilvy



"Pete" wrote in message
ups.com...
Hi Tom,

When I Test this out at home on Excel 2003, I get at you describe, but
when sent from a PC with Office 97 on it at Work, I get an exact
replica of the workbook it is sending the "Usage" sheet from, I can't
understand why.

Pete

Compatible with Office 97
 

thanks, Tom. I'm back at Work tomorrow, so will observe more closely,
and may post back.

Pete