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

Hi

If you try this do you have the same problem then

Sub Send_Range()

' Select the range of cells on the active worksheet.
ActiveSheet.Range("A1:B5").Select

' Show the envelope on the ActiveWorkbook.
ActiveWorkbook.EnvelopeVisible = True

' Set the optional introduction field thats adds
' some header text to the email body. It also sets
' the To and Subject lines. Finally the message
' is sent.
With ActiveSheet.MailEnvelope
.Introduction = "This is a sample worksheet."
.Item.To = "E-Mail_Address_Here"
.Item.Subject = "My subject"
.Item.Send
End With
End Sub


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


wrote in message ups.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