An application without mail, it exists? Never seen !  😆

Several solutions to manage the mails are available:

• Directly in the configuration of the mail event?
  It’s possible, it works, but maintenance level is complex and not flexible at all, we forget!

• The second solution is to deport the mails in database, and to use a stored procedure and a series of tags to make the mail generic. A little more details below:

Create the table that will contain the email

First, create a table that will allow to manage the contents of the different mails, we will need a code of the mail, to easily identify process side of the subject of the email and its body:

CREATE TABLE [dbo].[Blog_Mail](
	[MA_ID] [int] IDENTITY(1,1) NOT NULL,
	[MA_Code] [nvarchar](120) NULL,
	[MA_Subject] [nvarchar](max) NULL,
	[MA_Body] [nvarchar](max) NULL,
 CONSTRAINT [PK_Blog_Mail] PRIMARY KEY CLUSTERED 
(
	[MA_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Example of mail (simple):

• Code : NEWTASK
• Object : [Blog] – Validation of the request %ID% %Title%
• Body : Hello %UserDisplayName%, <br/><br/>You must validate the request %ID% (Title : %Title%).<br /><br /><a href=’%LinkToTask%’>Click here to validate</a>

💡 Tip: As you can see, we can put HTML in the body and the object of the mall, you can make nice emails with lots of colors super easily  😎

Create the stored procedure that will restore the mail

Our process will not be able to directly look for the contents of the email in the table, because the mails have dynamic contents (name of the person who receives the mail, link to the task, information on the request being validated, .. .). A stored procedure will have to be used to generate the body of the mail with the good content.

If you want more details about creating stored procedures and their use in K2, Thomas explains everything in his article on enK2next.

The mail templates contained in the table created above must have tags, which will be replaced by the stored procedure by the real values. In my example, the tags have the form %NomDuTag%, this tag format can be modified according to your needs.

The stored procedure will take at least a parameter:

• The code of the mail that one seeks to return
• The business ID of the request (depending on our application, it may vary)
• The link to the task (must be present in an optional way)

The stored procedure will:

1. Retrieve the model of the mail according to the code passed in parameter
2. Retrieve information from the business table (business data in the example)
3. Replace mail tags with information retrieved from the business table or passed as a parameter
4. Resend the mail

CREATE PROCEDURE [dbo].[PRC_Blog_GetMail]
	@P_MailCode			nvarchar(120),			-- Code du mail a retourner
	@P_ID				int = null,				-- ID de notre demande
	@P_LinkToTask		nvarchar(max) = null,	-- Lien vers la tache
	@P_UserLogin		nvarchar(120) = null,	-- Login de la personne
	@P_UserDisplayName	nvarchar(120) = null	-- Destinataire du mail
AS
BEGIN

	DECLARE @MA_Subject nvarchar(max) = '',
			@MA_Body nvarchar(max) = '',
			@BD_Title nvarchar(120) = '',
			@BD_Originator nvarchar(120) = '';
			
	-- Récupération du template de mail
	SELECT @MA_Subject = MA_Subject, @MA_Body = MA_Body
	FROM Blog_Mail 
	WHERE MA_Code = @P_MailCode 
	
	-- Récupération des données métier
	SELECT @BD_Title = BD_Title, @BD_Originator = BD_Originator
	FROM Blog_BusinessData
	WHERE BD_ID = @P_ID
	
	-- Génération du subject
	SET @MA_Subject = REPLACE(@MA_Subject,'%UserDisplayName%',ISNULL(@P_UserDisplayName,''))
	SET @MA_Subject = REPLACE(@MA_Subject,'%UserLogin%',ISNULL(@P_UserLogin,''))
	SET @MA_Subject = REPLACE(@MA_Subject,'%ID%',ISNULL(@P_ID,''))
	SET @MA_Subject = REPLACE(@MA_Subject,'%Title%',ISNULL(@BD_Title,''))
	SET @MA_Subject = REPLACE(@MA_Subject,'%Originator%',ISNULL(@BD_Originator,''))

	-- Génération du corps du mail
	SET @MA_Body = REPLACE(@MA_Body, '%UserDisplayName%', ISNULL(@P_UserDisplayName,''))
	SET @MA_Body = REPLACE(@MA_Body, '%UserLogin%', ISNULL(@P_UserLogin,''))
	SET @MA_Body = REPLACE(@MA_Body, '%LinkToTask%', ISNULL(@P_LinkToTask,''))
	SET @MA_Body = REPLACE(@MA_Body,'%ID%',ISNULL(@P_ID,''))
	SET @MA_Body = REPLACE(@MA_Body,'%Title%',ISNULL(@BD_Title,''))
	SET @MA_Body = REPLACE(@MA_Body,'%Originator%',ISNULL(@BD_Originator,''))

	
	SELECT @MA_Subject AS 'MA_Subject', @MA_Body AS 'MA_Body'
	
END

Process side

Process side, nothing more simple, in our emails event, we will call our stored procedure and inform him the input information as below:

It’s good, we can test now, and here is the result:

It’s your turn  😉