Macro to send email to multiple recipients separately where the subject line is the corresponding topic for that email address.

trami · Dec 10th 2021

I have a pivot table that includes email addresses and topics corresponding to each email address. I need to create a macro that will send an individual email to each email address and use the corresponding topic as the subject line. For example,

Email Topic

[email protected] Auto Insurance

[email protected] Professional license

I would expect to send and email to [email protected] with a subject line of auto insurance and so on down the list. I have this working with the exception that it is duplicating each email. I think this is due to my range being read through twice but I am unsure how to fix it. Please advise on how to stop the duplicate emails

Code

Sub Email()
Dim i As Integer, n As Integer, Mail_Object, Email_Subject, o As Variant, lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
lra = Cells(Rows.Count, "B").End(xlUp).Row
Set Mail_Object = CreateObject("Outlook.Application")

For i = 7 To lr
For n = 7 To lra
        With Mail_Object.CreateItem(o)
            .Subject = Range("B" & n).Value
            .To = Range("A" & i).Value
            .Body = "Please send updated information to Tiffany and Bill"
            '.Send
            .display 'disable display and enable send to send automatically
    End With
  Next n
Next i
        MsgBox "E-mail successfully sent", 64
        Application.DisplayAlerts = False
Set Mail_Object = Nothing
End Sub

Display More

**royUK** · Dec 11th 2021

Attach an example workbook

Mumps · Dec 11th 2021

Try:

Code

Sub Email()
    Dim Email As Range, Mail_Object As Object, lr As Long
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    Set Mail_Object = CreateObject("Outlook.Application")
    For Each Email In Range("A7:A" & lr)
        With Mail_Object.CreateItem(o)
            .To = Email.Value
            .Subject = Email.Offset(, 1).Value
            .Body = "Please send updated information to Tiffany and Bill"
            .display
        End With
    Next Email
    MsgBox "E-mail successfully sent", 64
End Sub

Display More

Macro to send email to multiple recipients separately where the subject line is the corresponding topic for that email address.

Participate now!

Share

Tags