Email macro not working

[tankerman]

I have used Ron de Bruin's code for emailing a worksheet in Outlook 2003 for
quite sometime and it works perfect, (I use the .Send not the .display). Now
for the problem I have been asked to use the .display now instead of .send
because we are now having to add an extra address (not very often maybe once
a month or so).

All I changed was the comment out of .Send and the uncommenting of .Display
and it seemed to work until I hit the SEND button in Outlook and I get this
error message

"Operation can not be performed because the object has been deleted"

If I reverse the commenting it sends OK. Below is the code I am using



Sub Mail_ActiveSheet()
'Working in 2000-2007
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim OutApp As Object
Dim OutMail As Object
Exit Sub
With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set Sourcewb = ActiveWorkbook

'Copy the sheet to a new workbook
ActiveSheet.Copy
Set Destwb = ActiveWorkbook
Destwb.Colors = Sourcewb.Colors
'Determine the Excel version and file extension/format
With Destwb
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
'We exit the sub when your answer is NO in the security dialog
that you only
'see when you copy a sheet from a xlsm file with macro's
disabled.
If Sourcewb.Name = .Name Then
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
MsgBox "Your answer is NO in the security dialog"
Exit Sub
Else
Select Case Sourcewb.FileFormat
Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
Case 52:
If .HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
Case 56: FileExtStr = ".xls": FileFormatNum = 56
Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
End Select
End If
End If
End With

' 'Change all cells in the worksheet to values if you want
' With Destwb.Sheets(1).UsedRange
' .Cells.Copy
' .Cells.PasteSpecial xlPasteValues
' .Cells(1).Select
' End With
' Application.CutCopyMode = False

'Save the new workbook/Mail it/Delete it
TempFilePath = Environ$("temp") & "\"
TempFileName = "" & Sourcewb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = ""
.Body = ""
.Attachments.Add Destwb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use
'.display
End With
On Error GoTo 0
.Close SaveChanges:=False
End With

'Delete the file you have send
Kill TempFilePath & TempFileName & FileExtStr

Set OutMail = Nothing
Set OutApp = Nothing

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub


Any help is greatly appreciated

Danny

 

[Ron de Bruin]

Hi tankerman

Do you have the same problem when you test one of the
the example workbook on my site for the Outlook object model
http://www.rondebruin.nl/sendmail.htm

 

[tankerman]

Hi Ron

Thanks for answering, I went back and tried to use mail sheet in outlook
body, the .display worked fine it mailed the sheet to my address just fine.

I tried using send workbook last saved version, workbook newly created, send
only activesheet, and send sheet index on all of these I got the message
"Operation cannot be performed because object has been deleted".

If I click OK and hit send again I get the same message, I tried to attach
another file and got the same message, BUT if I deleted the attachement
before or after the error message I was able to send the message as a blank
message or attach another file.

Could it be how our Outlook is setup, I don't know.

Thank You

Tankerman

 

[Ron de Bruin]

After using Google I see a few people fixed it by unchecking a Outlook add-in

*****************************************************************
The problem is solved. The causing comonent was an Add-In in Outlook.

Tools-Options...-Other-Advanced Options...-Add-In Manager... Removing

it and everything worked fine. ...or fix the add-in ;-)

 

[tankerman]

Thanks Ron,

I found that "MaX Compression" add-in was my problem, when I un-checked it
your code worked great.

Thanks again
Tankerman

 

[Ron de Bruin]

Hi Tankerman

Thanks for the feedback
Good to hear that it is working now