Jump to content
Sign in to follow this  
FooKelvin

Procedures to send email with multiple data

Recommended Posts

Hi,

I have a Procedures ready to auto send email to employee when the reminder date equal today.

Let me show you what is inside my employee database.

 

post-179514-0-19494000-1449793020_thumb.png

 

As you can see, there is two records for employee 1001.

This is because this employee short notice for 2 months. [sdate]

There is also a shortNotice which is in December is 4 day, in January is 18 days.

So, the consist days is 18+4=22 Days

 

This is my procedure code, it's working, this procedure will trigger out email if meet the criteria.

***REMEMBER TO CHANGE THE SERVER NAME & PORT NUMBER***

USE [RSA]
GO
/****** Object:  StoredProcedure [dbo].[sp_send_cdosysmail]    Script Date: 12/10/2015 3:55:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_send_cdosysmail] 
@From varchar(100)="notification@mail.com",
@Subject varchar(100)="A Reminder for you",
@body varchar(4000)=" ",
@attachments varchar(4000)=NULL


AS
DECLARE
@out_desc VARCHAR(1000),
@out_mesg VARCHAR(10)
 
DECLARE 
@empid nvarchar(255),
@Name nvarchar(255),
@Email nvarchar(255),
@reminderDate datetime,
@lastDay datetime,
@shortNotice int,
@consistsDays int,
@sdate datetime
 
Declare C1 CURSOR READ_ONLY
FOR
SELECT DISTINCT [empid],[Name],[Email],[reminderDate],[lastDay],[shortNotice],[consistsDays],[sdate]
FROM [resigntblView]

Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
Declare @files table(fileid int identity(1,1),[file] varchar(255))


OPEN C1
FETCH NEXT FROM C1 INTO
@empid,@Name, @Email, @reminderDate, @lastDay,@shortNotice,@consistsDays,@sdate
WHILE @@FETCH_STATUS = 0
BEGIN

IF DATEPART(DAY,@reminderDate) = DATEPART(DAY,GETDATE())
      AND DATEPART(MONTH,@reminderDate) = DATEPART(MONTH,GETDATE())
		 AND DATEPART(YEAR,@reminderDate) = DATEPART(YEAR,GETDATE())
      BEGIN
	  SET @body = '<b>Hi ' + @Name +
            '</b><br />This is a reminder'
            + '<br /><br />Thank You'
--************* Create the CDO.Message Object ************************ 
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT 
--***************Configuring the Message Object ****************** 
-- This is to configure a remote SMTP server. 
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp 
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields ("http://schemas.microsoft.com/cdo/configuration/sendusing"). Value','2' 
-- This is to configure the Server Name or IP address. 
-- Replace MailServerName by the name or IP of your SMTP Server. 
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields ("http://schemas.microsoft.com/cdo/configuration/smtpserver"). Value', '#######' --PUT YOUR SERVER HERE
-- Save the configurations to the message object. 
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null 
-- Set the e-mail parameters. 
EXEC @hr = sp_OASetProperty @iMsg, 'To', @Email 
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From 
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject 
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'. 
EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @body
EXEC @hr = sp_OAMethod @iMsg, 'Send' 
-- Do some error handling after each step if you need to. 
-- Clean up the objects created. 
EXEC @hr = sp_OADestroy @iMsg 
END
      FETCH NEXT FROM C1 INTO
      @empid,@Name, @Email, @reminderDate,@lastDay,@shortNotice,@consistsDays,@sdate
	  END
CLOSE C1
DEALLOCATE C1


Once the Procedures  is execute, this is the OUTPUT that i received in my inbox:

Hi Foo, Kelvin
This is a reminder

Thank You

This Email will Trigger two times since there is a two column of data. My question is, how to merge some of the data in one, and send the email only once.

 

Expected Output:

Hi Foo, Kelvin,

You are required to compensate USD 100 to the Company in view of your 22 days of short notice.
The 1 month of your notice period consist of 22 working days, (01-12-2015 - 31-12-2015), 4 Days
The 2 month of your notice period consist of 22 working days, (01-01-2016 - 31-01-2016), 18 Days


Thank You. 

Thanks for your big help.

Edited by FooKelvin

Share this post


Link to post
Share on other sites

Why the heck are you sending emails from within the database server?

Share this post


Link to post
Share on other sites

Why the heck are you sending emails from within the database server?

 

This is because i'm using SQL Agent to help me schedule the email trigger. I need the email to be send everyday automatically.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.