-- =============================================
-- Author: Raviraj.M
-- Create date: 5th Dec 2013
-- Description: Automatic Email Serves
-- =============================================
ALTER PROCEDURE [dbo].[AutomatcEmailServer]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @DATE varchar(20)
SET @DATE = right(cast(GETDATE()as DATE),5)
DECLARE @MinId INT
SET @MinId = (select MIN(Id) as id FROM StudentInfo
WHERE right(cast(DateOfBirth as DATE),5) =@DATE)
DECLARE @MaxId INT
SET @MaxId=(SELECT MAX(Id) as id FROM StudentInfo
WHERE right(cast(DateOfBirth as DATE),5) =@DATE)
DECLARE @ADMIN VARCHAR(100)
DECLARE @NAME VARCHAR(100)
DECLARE @EMAILID VARCHAR(100)
WHILE (@MinId<=@MaxId)
BEGIN
DECLARE @IsNull NVARCHAR(50)
SELECT @IsNull=Id FROM StudentInfo WHERE Id=@MinId and right(cast(DateOfBirth as DATE),5)=@DATE
IF (@IsNull!='')
BEGIN
SELECT @NAME=StudentName,@EMAILID=EmailId FROM StudentInfo WHERE Id=@MinId
/* 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 @MinId = @MinId + 1
END
ELSE
BEGIN
SELECT @MinId = @MinId + 1
END
END
END