Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
 
Posts: n/a
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.misc
Ron de Bruin
 
Posts: n/a
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
runtime error 1004 method range of object '_global failed valdesd Excel Discussion (Misc queries) 2 October 6th 05 07:26 PM
HELP - 'Add' of object 'CommandBarControls' failed LAF Excel Discussion (Misc queries) 1 September 20th 05 01:53 AM
Method 'Add' of object 'CommandBarControls' failed LAF Excel Discussion (Misc queries) 0 September 12th 05 02:02 PM
Help: runtime error - Method seriescollection object_chart failed huangx06 Charts and Charting in Excel 3 July 9th 05 12:27 AM
Method 'Paste' of object '_Worksheet' failed markline Excel Discussion (Misc queries) 7 May 28th 05 05:02 AM


All times are GMT +1. The time now is 09:33 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"