Jump to content

Procedures to send email with multiple data


FooKelvin

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.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • 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.