CODEWITHSUNDEEP
excelvbaoutlook
Jj Blevins
Jj Blevins

Reputation: 395

Sending e-mail with addresses in worksheet column

I have a sheet called "Test" where column A is a list of e-mail addresses.
I want to send an e-mail to all addresses.

Sub EmailSend()
    
    Dim objOutlook As Object
    Dim objMail As Object
    Dim i As Integer
    
    Set objOutlook = CreateObject("Outlook.Application")
    Set objMail = objOutlook.CreateItem(0)
    
    For i = 1 To 10
        With objMail
           .to = Sheets("Test").Range("A" & i).Value
           .Subject = "hi"
           .body = _ 
              "Hi " & Sheets("Test").Range("B" & i) & Sheets("links").Range("G" & 1)
           .send
        End With
    Next i
    
End Sub

The macro sends an e-mail to the address in A1 and then an error

element was moved or deleted.

appears in the line

.to = Sheets("Test").Range("A" & i).Value

Upvotes: 1

Views: 120

Answers (1)

rohrl77
rohrl77

Reputation: 3337

My guess that the second line you are trying to send might have an error. Therefore, use the code below and check what it shows in the Immediate window.

UPDATE: I've added the CreateItem line within the For loop and am resetting it for each mail. Possible it is the mail item that the error message is complaining about.

Sub EmailSend()

Dim objOutlook As Object
Dim objMail As Object
Dim i As Integer

Set objOutlook = CreateObject("Outlook.Application")

For i = 1 To 10
Set objMail = objOutlook.CreateItem(0)

With objMail
    Debug.Print Sheets("Test").Range("A" & i).Value
   .to = Sheets("Test").Range("A" & i).Value
   .Subject = "hi"
   .body = _
      "Hi " & Sheets("Test").Range("B" & i) & Sheets("links").Range("G" & 1)
   .display
   '.send
End With
Set objMail = Nothing

Next i

End Sub

Upvotes: 2

Related Questions