PDA

View Full Version : Solved: How to stop it closing??!!



tomsweddy
06-23-2009, 08:15 AM
hi,

I have taken the following code from the net. It basically makes a copy of the active sheet and then attatches it to an Outlook email ready for sending. However, for some reason, everytime I run it, my main workbook closes and I am left with just my email. I dont want my workbook to close. Can someone tell me which part of the code needs to be edited for this to work correctly??

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.Cells.Copy
Set WB = ActiveWorkbook
FileName = "Contract.xls"
On Error Resume Next
Kill "C:\" & FileName
On Error GoTo 0
WB.SaveAs FileName:="C:\" & 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 = "someone@somedomain.com"
'Uncomment the line below to hard code a subject
.Subject = "Employee Contract"
.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!

tomsweddy
06-23-2009, 08:17 AM
Remove : WB.Close SaveChanges:=False