How to Send Email Automatically in Approve Leave Condition

Question

i want to ask about how i can send notification e-mail message when Approve Leave in sql server 2005 to know this Hi Dear (EmployeeName) Your Leave Approved.. .

Pradip Pandey · Answer

Hi To send Email automatically first you have to configure Database Mail in SQL Server. For this refer this link http://www.c-sharpcorner.com/UploadFile/raj1979/how-to-configuresend-sql-server-database-mail/ Create a table called EmailParameters: CREATE TABLE [dbo].[EmailParameters]( [ProfileName] [varchar](50) NULL [Recipients_CC] [varchar](200) NULL [Recipients_BCC] [varchar](200) NULL [Priority] [int] NULL ) ON [PRIMARY] SET ANSI_PADDING ON ALTER TABLE [dbo].[EmailParameters] ADD [AttachmentPath] [varchar](max) NULL GO Insert a value into EmailParameters table like this ProfileName Recipients_CC Recipients_BCC Priority AttachmentPath MailProfile test@test.com test@test1.com 1 A fter configuring Database Email use this code in your stored procedure when you are using in the functionality of Leave Approval. EXEC @OUTPUT= P_SendEmail @REQID=@RequestID @RECEIPIENT= @RecipientEmail @SUBJECT=@SUBJECT @BODY=@BODY @OUTPUT=@OUTPUT Procedure of P_SendMail: CREATE Procedure [dbo].[P_SendEmail] ( @RECEIPIENT VARCHAR(100) @SUBJECT VARCHAR(500) @BODY VARCHAR(max) @Attachment_File VARCHAR(MAX)=NULL @OUTPUT bigint OUTPUT ) as BEGIN DECLARE @PROFILE VARCHAR(50) DECLARE @Recipients_CC varchar(200) DECLARE @Recipients_BCC varchar(200) SELECT @PROFILE=ProfileName @Recipients_CC=Recipients_CC @Recipients_BCC=Recipients_BCC FROM EmailParameters WHERE PRIORITY=1 EXEC msdb.dbo.sp_send_dbmail @profile_name = @PROFILE @recipients = @RECEIPIENT @copy_recipients = @Recipients_CC @blind_copy_recipients=@Recipients_BCC @subject = @SUBJECT @importance ='HIGH' @body =@BODY @file_attachments ='' @BODY_FORMAT='HTML' IF @@Error 0 SET @OUTPUT = 0 ELSE SET @OUTPUT = 1 RETURN @OUTPUT END Hope it will help.