Currently, I'm trying to create blast emails to be sent to customers, and there are some duplicate email addresses in the column list. I want to only send one email to each unique email address, but when I run my macro it creates an email for each cell in the column, regardless if there's a repetition of the address earlier/later in the column. a snippet of my code is below:
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set sh = Sheets("TestSheet")
Set OutApp = CreateObject("Outlook.Application")
For Each cell In sh.Columns("D").Cells.SpecialCells(xlCellTypeConstants)
Set rng = sh.Cells(cell.Row, 1).Range("E1:Z1")
If cell.Value Like "?*@?*.?*" And _
Application.WorksheetFunction.CountA(rng) > 0 Then
Set OutMail = OutApp.CreateItem(0)
Set Entity = cell.Offset(0, -3)
Set Quarter = cell.Offset(0, -2)
Set Year = cell.Offset(0, -1)
Set CCRecip = cell.Offset(0, 1)
strbody = "<font face = 'Calibri'><b>Hello All--</b>" & ...
signature = "<br>Thank you,<br>" & ...
.To = cell.Value
.CC = CCRecip.Value
.Subject = Entity.Value
.HTMLBody = strbody & signature
.display
End With
Set OutMail = Nothing
End If
Next cell