List of names and Dates, send an email automatically if it is todays date.

[PaulOPTC]

Hey! I've seen a bunch of similar threads, and I've tried some of their codes and modified them to meet my own needs, but nothing is working as planned.

Here is what I need to do:

I have a list of employees, after 90 days of being employed, they are "no longer on probation". And are offered health insurance, and a company truck etc.

After 60 days of them being employed, I want it to send me an email saying "(F5) is almost eligible for health insurance, please send them an email of our current package."

After 80 days "(F5) needs to send back the paper work by next week, please confirm they have accepted or denied coverage"


This is what it would look like


Hidden Helper columns --------------------------------------------------------------------------------------------------

First Name (A5)	Last Name (B5)	Start date (C5)	Health insurance (D5)	.....	Name (G5)	insurance first check (H5)	email sent? (I5)	second check (J5)	second sent? (K5)
					=[@[First Name]]&" "&[@[Last Name]]	=IF([@[Start Date]]>0,[@[Start Date]]+60,"")		=IF([@[Start Date]]>0,[@[Start Date]]+80,"")

It is a table, we usually only have like 20 employees at a time, and while all 20 will be on this list, the only ones we care about will be the new ones. But the table could be sorted at any point.

So I would need the code to go and look at H6 ~ H80 (for future proofing)
If there is a date, check to see if it is on or before today's date
if it is on or before today's date look in column I to see if the email was sent,
if it was already sent, do nothing
if it hasn't been sent, to send the first email, and mark off in the column that the first email was sent.
and then I need it to check j6~j80 for and repeat the same steps.



If anyone is willing to help me complete this task I would really appreciate it!

 

[Max1616]

Perhaps something like this would work?

Sub Email_Check_Send()

    Dim OutApp As Object
    Dim OutMail As Object
    Dim lrow As Integer
    Dim i As Long
    Dim Recipients As String
    Dim RecipientsClm As Integer
    
    
    RecipientsClm = 5 '******This represents the column that the email address is in
    
    lrow = Cells(Rows.Count, 7).End(xlUp).Row
    On Error Resume Next
    'Only the visible cells in the selection
    
    For i = 2 To lrow 'Loop through each record
        'First Email:
        If Cells(i, RecipientsClm) <> "" And Cells(i, 8) <> "" Then 'If Email exisits and start date isn't blank then...
            If Cells(i, 8) <= Now And Cells(i, 9) = "" Then 'If First Check date is before or on today, and the email sent is blank then...
                
                'Email Section:
                Recipients = Cells(i, RecipientsClm)
                Set OutApp = CreateObject("Outlook.Application")
                Set OutMail = OutApp.CreateItem(0)
            
                With OutMail
                    .To = Recipients
                    .CC = ""
                    .BCC = ""
                    .Subject = "Subject Goes Here"
                    .HTMLBody = "Email 1 Text goes here"
                    .Send   'or use .Display if you do not want to auto-send
                End With

                Cells(i, 9) = "X" 'Indicates the individal has been emailed
            End If
        
        End If
        
        
        'Second Email:
        If Cells(i, RecipientsClm) <> "" And Cells(i, 10) <> "" Then 'If Email exisits and start date isn't blank then...
            If Cells(i, 10) <= Now And Cells(i, 11) = "" Then 'If First Check date is before or on today, and the email sent is blank then...
                
                'Email Section:
                Recipients = Cells(i, RecipientsClm)
                Set OutApp = CreateObject("Outlook.Application")
                Set OutMail = OutApp.CreateItem(0)
            
                With OutMail
                    .To = Recipients
                    .CC = ""
                    .BCC = ""
                    .Subject = "Subject Goes Here"
                    .HTMLBody = "Email 1 Text goes here"
                    .Send   'or use .Display if you do not want to auto-send
                End With

                Cells(i, 11) = "X" 'Indicates the individal has been emailed
            End If
        
        End If
    Next i

End Sub

 

[Max1616]

Note, you need to change the first variable "RecipientsClm" to the column number where the individual's email lives. (e.g. If the individual's emails live in column P, then change 5 -> 16)

 

[PaulOPTC]

Note, you need to change the first variable "RecipientsClm" to the column number where the individual's email lives. (e.g. If the individual's emails live in column P, then change 5 -> 16)

Awesome! It worked!

Thank you so much for your help!