send auto email work anniversary

[kerm007]

hello
how can my HR girls auto send email to all when its the work anniversary of some one from an excel sheet populated with the name and the anniversay date of an employee ?
Thanks

 

[kerm007]

how did you do the text in C1/2 cannot get it they way you set it

 

[kerm007]

the date format on B is month / day /year ?

 

[Wookiee]

i also get invalid name error in D4

OK. I assumed that since you only mentioned the formula for E4 that the formula in D4 was working as intended.

In that case, it seems the issue is with the named range nrToday. You can change the formula to avoid that by using these formulas which reference the cell directly without using a named range.

D4

=IF(AND(MONTH(B4)=MONTH($B$1),DAY(B4)=DAY($B$1)),"Anniversary","")

E4

=DATEDIF([@Hired],$B$1,"Y")

As long as that's where your date is stored, that should allow

 

[kerm007]

hello
i assume i have to replace someting on this line ?
For Each rngCell In Range("tblX[Name]")

Thanks

 

[kerm007]

ok no more error but when i open there error in : For Each rngCell In Range("tblX[Name]")

Ty

 

[Wookiee]

hello
i assume i have to replace someting on this line ?
For Each rngCell In Range("tblX[Name]")

Thanks

Not if you've properly named the table (tblX) and Cell A3 = "Name"

 

[kerm007]

Cell A3 Yes but the table ????
not sure i did it right

 

[kerm007]

ok i found it one thing is instead of saying
Congratulations, yo! can we get the name of the person and instead of the employee mail i will replace it with the global all emplyoee group mail

 

[Wookiee]

ok i found it one thing is instead of saying
Congratulations, yo! can we get the name of the person and instead of the employee mail i will replace it with the global all emplyoee group mail

Private Sub Workbook_Open()

Dim rngCell     As Range
Dim lngSpace    As Long
Dim strMessage  As String
Dim strName     As String

Const STRRECIPIENTS _
As String = "Your.Employees@WhereverYou.Work"

For Each rngCell In Range("tblX[Name]")

  If rngCell.Offset(0, 3) = "Anniversary" Then

    lngSpace = VBA.InStr(rngCell.Text, " ") - 1
    strName = VBA.Left(rngCell.Text, lngSpace)

    strMessage = "Congratulations to " & strName & _
      ".<br><br>We are so absolutely gobsmacked to have " & _
      "them working with us.<br><br>Happy " & _
      Addth(rngCell.Offset(0, 4).Value) & _
      " Work Anniversary, " & strName & "!"

    Call Email _
      (STRRECIPIENTS, "Let's Celebrate!", strMessage)

    strMessage = ""

  End If

Next rngCell

End Sub

 

[kerm007]

Hello
where i put the mail of employee should i replace it ?