Method 'MailEnvelope' of object '_Worksheet' failed
 

Greetings!

The code below works once (the first time). The second time it runs,
it gives this error:

Run-time error '-2147467259 (80004005)':
Method 'MailEnvelope' of object '_Worksheet' failed

Debugging shows that the code is failing at

With Application.ActiveSheet.MailEnvelope

Outlook 2003 is my mail client.

I am using Excel 2003.

I have set a reference to Microsoft Outlook 11.0.

I have verified through Task Manager/Processes that there is not a
separate process of Outlook or Excel "hanging."

Microsoft Word is not my email editor.

On other groups, I have seen similar posts about this problem with no
solution. Has anyone run across this, and has anyone come up with a
solution?

I can see a security risk for all this (looping through a list of
addresses and sending mail without a security warning.) Thanks.

Option Explicit
Sub SendFile()

Dim wkb1 As Workbook
Dim wkb2 As Workbook
Dim wks1 As Worksheet
Dim rng1 As Range
Dim strEmail As String

strEmail = "

Set wkb1 = Application.ActiveWorkbook
Set rng1 = Selection
rng1.Copy
Sheets.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

Set wks1 = wkb1.ActiveSheet

Debug.Print wks1.Name

Call usbSendMail(strEmail)

wks1.Delete

End Sub

Sub usbSendMail(strRecipient As String)

'Use a With...End With block to reference the MsoEnvelope object.
With Application.ActiveSheet.MailEnvelope

'Add some introductory text before the body of the e-mail.
.Introduction = "Please read this and send me your comments."

'Return a Microsoft Outlook MailItem object that
'you can use to send the document.
With .Item

'All of the mail item settings are saved with the document.
'When you add a recipient to the Recipients collection
'or change other properties, these settings will persist.
.Recipients.Add strRecipient
.Subject = "Here is the document."

'The body of this message will be
'the content of the active document.
.Send
End With
End With
End Sub

Method 'MailEnvelope' of object '_Worksheet' failed
 

See reply in your other thread

--
Regards Ron de Bruin
http://www.rondebruin.nl


wrote in message oups.com...
Greetings!

The code below works once (the first time). The second time it runs,
it gives this error:

Run-time error '-2147467259 (80004005)':
Method 'MailEnvelope' of object '_Worksheet' failed

Debugging shows that the code is failing at

With Application.ActiveSheet.MailEnvelope

Outlook 2003 is my mail client.

I am using Excel 2003.

I have set a reference to Microsoft Outlook 11.0.

I have verified through Task Manager/Processes that there is not a
separate process of Outlook or Excel "hanging."

Microsoft Word is not my email editor.

On other groups, I have seen similar posts about this problem with no
solution. Has anyone run across this, and has anyone come up with a
solution?

I can see a security risk for all this (looping through a list of
addresses and sending mail without a security warning.) Thanks.

Option Explicit
Sub SendFile()

Dim wkb1 As Workbook
Dim wkb2 As Workbook
Dim wks1 As Worksheet
Dim rng1 As Range
Dim strEmail As String

strEmail = "

Set wkb1 = Application.ActiveWorkbook
Set rng1 = Selection
rng1.Copy
Sheets.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

Set wks1 = wkb1.ActiveSheet

Debug.Print wks1.Name

Call usbSendMail(strEmail)

wks1.Delete

End Sub

Sub usbSendMail(strRecipient As String)

'Use a With...End With block to reference the MsoEnvelope object.
With Application.ActiveSheet.MailEnvelope

'Add some introductory text before the body of the e-mail.
.Introduction = "Please read this and send me your comments."

'Return a Microsoft Outlook MailItem object that
'you can use to send the document.
With .Item

'All of the mail item settings are saved with the document.
'When you add a recipient to the Recipients collection
'or change other properties, these settings will persist.
.Recipients.Add strRecipient
.Subject = "Here is the document."

'The body of this message will be
'the content of the active document.
.Send
End With
End With
End Sub