CODEWITHSUNDEEP
excelvba
Jaffet León
Jaffet León

Reputation: 77

How to run properly this loop to send massive emails?

I want to send massive mails to the mail adresses that are saved on column G (from G:10) until the end of the column. Note that the body of the message and the subject is the same for every email to be sent. This is the code:

Sub Email_From_Excel_Basic()

    Dim emailApplication As Object

    Dim emailItem As Object

    Set emailApplication = CreateObject("Outlook.Application")
    Set emailItem = emailApplication.CreateItem(0)


    Dim cell As Range
    Set myDataRng = Range("G10:G15")
    
    ' Arquitectura del mail.
    For Each cell In myDataRng
        emailItem.To = cell.Value
        emailItem.Subject = Range("H5").Value
        emailItem.Body = Range("J5").Value
        ' Accion ejecutoria de envio de mail
        emailItem.send
    Next cell
    ' Descomentar la siguiente linea si quieres ver el correo antes de enviarse.
    ' emailItem.Display

    Set emailItem = Nothing

    Set emailApplication = Nothing

    
End Sub

But when running the error 'the item has been moved or deleted' is shown and only is sent the message to the first mail of the g column, any idea?

Upvotes: 0

Views: 41

Answers (2)

Jaffet León
Jaffet León

Reputation: 77

The answer is:

For Each cell In myDataRng
    Set emailItem = emailApplication.CreateItem(0)
    With emailItem
        .To = cell.Value
        .Subject = Range("H5").Value
        .Body = Range("J5").Value
        .send
        
    End With
    
Next

A With structure properly written solved the issue of massive sending to the mails in the G column

Upvotes: 0

norie
norie

Reputation: 9857

myDataRng is declared as a Range, so you need to change this,

Set myDataRng = Range("G10:G15").Value

to this.

Set myDataRng = Range("G10:G15")

Upvotes: 1

Related Questions