Hello All,
I am hoping someone can help me here. I am using the code below to send an email in outlook from excel. The general idea is that the sheet would have people's names in columns A&B, email address in column C, and the date that their training is due in column D. What I would like to know is if there is a way that I can have an email generated when the date reaches 90 days away from the date in column D. However, since there are about 40 different names on the list, there will be several different dates in column D. Is there a way to send the email only to those who have hit the 90 day criteria, without sending it to everybody on the list? Any help is greatly appreciated, and thank you in advance.
Ryan
I am hoping someone can help me here. I am using the code below to send an email in outlook from excel. The general idea is that the sheet would have people's names in columns A&B, email address in column C, and the date that their training is due in column D. What I would like to know is if there is a way that I can have an email generated when the date reaches 90 days away from the date in column D. However, since there are about 40 different names on the list, there will be several different dates in column D. Is there a way to send the email only to those who have hit the 90 day criteria, without sending it to everybody on the list? Any help is greatly appreciated, and thank you in advance.
Ryan
Code:
Sub SendEmail() Dim OutlookApp As Outlook.Application
Dim MItem As Outlook.MailItem
Dim cell As Range
Dim Subj As String
Dim EmailAddr As String
Dim Recipient As String
Set OutlookApp = New Outlook.Application
For Each cell In Columns("C").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "*@*" Then
Subj = "Training Dates"
Recipient = cell.Offset(0, -2).Value
EmailAddr = cell.Value
TrainingDate = Format(cell.Offset(0, 1).Value, "mm/dd/yy")
Msg = "Dear " & Recipient & "," & vbCrLf & vbCrLf
Msg = Msg & "Your Quarterly Firearms training is due on: " & vbCrLf & vbCrLf
Msg = Msg & TrainingDate & vbCrLf & vbCrLf
Msg = Msg & "Please schedule this training with management or the training coordinator." & vbCrLf & vbCrLf
Msg = Msg & "Thank you," & vbCrLf & vbCrLf
Msg = Msg & "NAME" & vbCrLf
Msg = Msg & "TITLE" & vbCrLf
Msg = Msg & "DEPARTMENT"
Set MItem = OutlookApp.CreateItem(olMailItem)
With MItem
.To = EmailAddr
.Subject = Subj
.Body = Msg
.Send
End With
End If
Next
End Sub