Hi, I have set up a reminder date for each recipients. Once due date is trigerred, reminder email will be sent out to them. However, I wish that only one email is being sent out to multiple recipients, and avoid sending duplicate emails to similar recipients. (Note: there are duplicates email address in the list)
Please help! Thank You =)
Sub datesexcelvba()
Dim OutlookApp As Object, OutlookMail As Object
Dim mydate1 As Date
Dim mydate2 As Long
Dim datetoday1 As Date
Dim datetoday2 As Long
Dim x As Long
lastRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For x = 2 To lastRow
mydate1 = Cells(x, 9).Value
mydate2 = mydate1
Cells(x, 12).Value = mydate2
datetoday1 = Date
datetoday2 = datetoday1
Cells(x, 13).Value = datetoday2
If mydate2 - datetoday2 = 0 Then
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)
OutlookMail.To = Cells(x, 6).Value
With OutlookMail
.CC = "someone@somewhere.com"
.Subject = "<<URGENT ACTION REQUIRED>>"
.HTMLBody = "Dear All,<p>Please complete above action before the deadline to avoid any unnecessary escalation."
.Importance = olImportanceHigh
.ReadReceiptRequested = True
.Display
'.send
End With
Cells(x, 10) = "Yes"
Cells(x, 10).Interior.ColorIndex = 3
Cells(x, 10).Font.ColorIndex = 2
Cells(x, 10).Font.Bold = True
Cells(x, 11).Value = mydate2 - datetoday2
End If
Next
Set OutlookApp = Nothing
Set OutlookMail = Nothing
End Sub