Hi,

I have the below vba code. This code selects a range from excel sheet and sends that as a email body text to the list of emails mentioned in one of the she.

If I run this code without the For Next loop, it works fine. Basically it works fine for one instance of sending the email. But with the For Next loop I get the below error:

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

the debug points to the line: With Selection.Parent.MailEnvelope.Item

When I step through the code, I get the error in above line after the email is sent to the first recipient.


Sub email_sender()

'there are three sheets
' sheet 1 code name: shMain - here I have the email subject line mentioned in a cell C16 and some other details
' sheet 2 code name: shEmails - here I have the excel table with email addresses in column B
' sheet 3 code name: shContent - here I have the range for the email body

Dim LR As Long 'last row in shEmails
Dim BodyContent As Range
Dim i As Long

LR = shEmails.Range("A" & Application.Rows.Count).End(xlUp).Row

Set BodyContent = Excel.Application.InputBox("Please select the range in sheet Emails to be sent as body of the email", _
"Select the email body range", , , , , , 8) 'here I select the sheet shContent and select the range

shContent.Activate

For i = 2 To LR
shContent.Range(BodyContent.Address).Select
With Selection.Parent.MailEnvelope.Item
.To = shEmails.Range("B" & i).Value
.Subject = shMain.Range("C16").Value
.send
End With
Next i

End Sub