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.