this link looks pretty close to what you are looking for using excel. You will need to learn how to adapt the examples you find online
https://www.rondebruin.nl/win/s1/outlook/amail6.htm
suggest you would use the worksheet change event
first check the change is in range and if so and that the target value=Yes then send the email. Something like this - adapted from the code in the link.
Code:
Private Sub Worksheet_Change(ByVal Target as Range)
Dim OutApp As Object
Dim OutMail As Object
if target.count=1 and target.column=8 and target.row>1 then '(8=column H, >1 assumes first row is a header row)
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set OutMail = OutApp.CreateItem(0)
With OutMail
.to = target.Offset(0, -1)'assumes email is in previous column to Y/N column
.Subject = "Testfile"
.Body = "Hi " & target.Offset(0, -2) 'assumes name is in next previous column to Y/N column
.Body=.body & vbcrlf & "Happy New Year"
.Send 'Or use .Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
end if
end sub