Method 'MailEnvelope' of object '_Worksheet' failed

[consulttech2004@hotmail.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 = "me@me.com"

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

[Ron de Bruin]

See reply in your other thread

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


<consulttech2004@hotmail.com> wrote in message news:1133965004.689424.107410@g49g2000cwa.googlegroups.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 = "me@me.com"
>
> 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
>