How to Send Email Automatically in Approve Leave Condition

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:

After 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

)

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.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Pradip Pandey14y ago
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
ProfileNameRecipients_CCRecipients_BCCPriorityAttachmentPath
MailProfile[email protected][email protected]1

After 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.