This is possibly not the best solution, but it will work:

1. Get the necessary information (Name, email address) of the people who have birthday that day.

2. Loop through and send using xp_sendmail or even better go get xp_smtp_sendmail and use that.

As part of step 2 you may need to do some replacing in the body of the email if you want to insert the person's name into the email.