Code for copying to new workbook--2007 issue

[froggiebeckie]

I've been using some code to copy the active sheet and send it as an attachment in Outlook. Everything was working fine, and then Along Came Excel 2007.

I did a bit of debugging (change in file locations, etc.) and all seemed to be right with the world, but of course, it couldn't be that easy.
The version that is created can't be opened by most folks, giving the "unrecognized format" error, even though it has the correct .xls extension.
I can manually copy the active sheet to a new wb, save it as 97-2003 version and send it through Outlook and everything is fine.
Any idea why I can't do it through code anymore?

Here's the code. The relevant portion starts with 'Make a copy of...:

Sub EmailWithOutlook()
'Variable declaration
Dim oApp As Object, _
oMail As Object, _
WB As Workbook, _
FileName As String

'Turn off screen updating
Application.ScreenUpdating = False

'Make a copy of the active sheet and save it to
'a temporary file
ActiveSheet.Copy
Set WB = ActiveWorkbook
FileName = "Vac Sched.xls"
On Error Resume Next
Kill "c:\Documents and Settings\Desktop\" & FileName
On Error GoTo 0
WB.SaveAs FileName:="C:\Documents and Settings\Desktop\" & FileName

'Create and show the outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
'Uncomment the line below to hard code a recipient
.To= ";APPROPRIATE ADDRESSES"

'Uncomment the line below to hard code a subject
.Subject = "Current Vac Sched.!"
.Attachments.Add WB.FullName
.Display
End With

'Delete the temporary file
WB.ChangeFileAccess Mode:=xlReadOnly
Kill WB.FullName
WB.Close SaveChanges:=False

'Restore screen updating and release Outlook
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing
End Sub

Thanks in advance,
BeckieO

 

[froggiebeckie]

POOOOO, Brain dead moment here.
I realized, all I needed to do was add the formatting info (, FileFormat:=xlExcel8) so that the line reads:

WB.SaveAs FileName:="C:\Documents and Settings\Desktop\" & FileName
, FileFormat:=xlExcel8

Anyway, thanks for looking and maybe this will help someone else.

BeckieO