How To Send Employees Birthday EMail From SQL Server

/*
============================================= 
Author:  xxxxxxx
 Create date: xxxx-xx-xx 
 Description: This procedure is used for EMPLOYEE BIRTHDAY. 
 ============================================= 
*/

ALTERPROCEDURE [dbo].[Get_birthday_emp] 
AS 
BEGIN 
DECLARE @DATE varchar(20) 
SET @DATE =   right(cast(GETDATE()as DATE),5)
/*
CREATE TABLE EmplyeeDetail
(
       [Name] nvarchar(50) ,-- name xxxxx
      [EmailId] nvarchar(100) ,-- emailid   [email protected]
      [DOB] date --  date format  yyyy-mm-dd
      )
*/   

CREATE TABLE #SS 
( 
      CON               INT IDENTITY(1,1), 
      NAME        VARCHAR(50), 
      EmailId           VARCHAR(70), 
      DOB               varchar(20) 
) 
INSERT INTO #SS
(NAME,EmailId,DOB) 
(SELECT NAME,EmailId,right(cast(DOB as DATE),5) FROM EmplyeeDetail 
WHERE right(cast(DOB as DATE),5) =@DATE) 
  select * from #SS
DECLARE @ADMIN VARCHAR(100) 
            SET @ADMIN [email protected]'           
DECLARE @NAME VARCHAR(100)    
DECLARE @EMAILID VARCHAR(100) 
DECLARE @I INT 
            SET @I = 1                
DECLARE @COUNT  INT 
      SELECT @COUNT = COUNT(CON) FROM #SS 
      WHILE (@I <= @COUNT) 
      BEGIN   
 if (@COUNT>=1) 
  BEGIN
            SELECT @NAME=NAME, @EMAILID=EmailId FROM #SS WHERE CON = @I 
            SELECT @NAME,@EMAILID     
  /* HTML table for birthday person  */
  DECLARE @birthdaytableHTML  NVARCHAR(MAX);
SET @birthdaytableHTML =
'<table width="100%" cellpadding="15" cellspacing="15" bgcolor="#dcdcdc"> 
 <tr> 
     <td width="600px" align="center">           
         <table width="600px" border="0" cellspacing="0" cellpadding="0" bgcolor="#ffffff" align="center">            
  <tr> 
    <td> 
    <table width="600" border="0" cellspacing="0" cellpadding="0">     
        <tr>              
          <td align="left" valign="top"><table width="100%" border="0" cellspacing="0" cellpadding="0"> 
               <tr> 
                <td height="11" style="font-size:0; line-height:0;"> </td> 
               </tr> 
               <tr> 
                <td  height="45" style="font-size:22px; color:#0270bf;text-align:center; text-transform:uppercase; font-family:Arial, Helvetica, sans-serif; padding:0 0 0 10px;">Wishing you a very Happy Birthday '+ @NAME+' </td> 
               </tr> 
              <tr> 
                <td height="10"> </td> 
               </tr> 
              <tr> 
               <td> 
    <img src="http://sms.latestsms.in/wp-content/uploads/birthday-scraps3.jpg"" />
                </td> 
              </tr> 
               <tr><td height="20"></td></tr> 
             </table></td> 
        </tr> 
       </table> 
      </td> 
  </tr> 
  <tr> 
    <td height="40"><table width="600" border="0" cellspacing="0" cellpadding="5">            
        <tr> 
          <td style="font-size:11px; color:#0270bf; font-family:Arial, Helvetica, sans-serif; text-align:center; font-size:22px">By MCN family</td> 
        </tr> 
       </table></td> 
  </tr> 
</table>   
         </td> 
    </tr>   
</table>'

  /* HTML table for reminder HR person  */

  DECLARE @RemindertableHTML  NVARCHAR(MAX);
SET @RemindertableHTML =
'<table width="100%" cellpadding="15" cellspacing="15" bgcolor="#dcdcdc"> 
 <tr> 
     <td width="600px" align="center"> 
         
         <table width="600px" border="0" cellspacing="0" cellpadding="0" bgcolor="#ffffff" align="center"> 
  <tr> 
    <td> 
    <table width="600" border="0" cellspacing="0" cellpadding="0"> 
        <tr> 
          <td align="left" valign="top"><table width="100%" border="0" cellspacing="0" cellpadding="0"> 
               <tr> 
                <td height="11" style="font-size:0; line-height:0;"> </td> 
               </tr> 
               <tr> 
                <td  height="45" style="font-size:22px; color:#0270bf;text-align:center; text-transform:uppercase; font-family:Arial, Helvetica, sans-serif; padding:0 0 0
10px;">Today is '+ @NAME+'  Birthday </td> 
               </tr> 
               <tr> 
                <td height="10"> </td> 
               </tr> 
              <tr> 
               <td> 
       </table></td> 
  </tr> 
</table> 
         </td> 
    </tr> 
</table>'

  EXEC msdb.dbo.Sp_send_dbmail @profile_name='HappyBirthday',  
@recipients=@ADMIN,  
@subject='Reminder Mail' ,  
@body = @RemindertableHTML,
@body_format= 'HTML'
exec msdb.dbo.sp_send_dbmail
@profile_name = 'HappyBirthday',
@recipients = @EMAILID,
@subject = '  Happy Birthday. ',
@body = @birthdaytableHTML,
@body_format= 'HTML'
    SELECT @I = @I + 1 
END 
END 
END 

Start Birthday Job

Step 1. Ensure that SQL Server Agent is up and running. You can see it below.

Click the YES button.

Step 2. Right-click on SQL Server Agent. You will see the option for "New" there. Add the Job as in the following.:

Step 3. The New Job popup will appear. Specify the name of the job.

Step 4. Click next on the "Steps" in the left menu. A SQL job can contain one or more steps. A step might be simply a SQL statement or a stored procedure call. Add your step here.

Job added

Step 5. Click next on the "Schedules" in the left menu. A SQL job can contain one or more schedules. A schedule is the timejob will run itself. You can specify recurring schedules also.

Job schedule added

Job successfully added.

Summary

In this post, we learned how to schedule a job to automatically send emails to some email accounts in a database. Similarly, you can use this approach to send emails from a SQL Server database without writing a single line of code.

If you want to send emails from code, here is an article: Sending Emails In C# Application