Send emails from MS SQL server

1st_sample_sendmail_in_sqlserver.sql

EXEC xp_ConfigureMailServer

DECLARE @Content VARCHAR(max),
		@ContentOut VARCHAR(max)

EXEC xp_GetFileContent 'C:\Users\pmcfe\Desktop\1.txt', @Content OUTPUT

DECLARE @vars AS TemplateVars
INSERT INTO @vars ([Name], [Value]) VALUES ('Name', 'Michael Franq')
INSERT INTO @vars ([Name], [Value]) VALUES ('Position', 'Head of Development')
INSERT INTO @vars ([Name], [Value]) VALUES ('Email', '[email protected]')

EXEC xp_ReplaceTemplateVars @Content, @vars, @ContentOut OUTPUT

EXEC xp_SendMail @recipients = '[email protected]',
	@subject = 'Newsletters sending test',
	@body = @ContentOut,
	@is_html = 1

all_scripts.sql

CREATE TYPE [dbo].[TemplateVars] AS TABLE(
	[Name] [varchar](50) NULL,
	[Value] [varchar](max) NULL
)
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MailServer](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NULL,
	[Server] [varchar](50) NULL,
	[Port] [int] NOT NULL,
	[Username] [varchar](120) NULL,
	[Password] [varchar](120) NULL,
	[Sender] [varchar](120) NULL,
	[IsDefault] [bit] NOT NULL,
	[Sending] [bit] NOT NULL,
	[Receiving] [bit] NOT NULL,
	[tenant] [varchar](120) NULL,
	[client_id] [varchar](120) NULL,
	[client_secret] [varchar](120) NULL,
	[M_IsDeleted] [bit] NOT NULL,
 CONSTRAINT [PK_Mail] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MailServer] ADD  CONSTRAINT [DF_Mail_Port]  DEFAULT ((25)) FOR [Port]
GO
ALTER TABLE [dbo].[MailServer] ADD  CONSTRAINT [DF_Mail_IsDefault]  DEFAULT ((0)) FOR [IsDefault]
GO
ALTER TABLE [dbo].[MailServer] ADD  CONSTRAINT [DF_Mail_Sending]  DEFAULT ((0)) FOR [Sending]
GO
ALTER TABLE [dbo].[MailServer] ADD  CONSTRAINT [DF_Mail_Receiving]  DEFAULT ((0)) FOR [Receiving]
GO
ALTER TABLE [dbo].[MailServer] ADD  CONSTRAINT [DF_Mail_M_IsDeleted]  DEFAULT ((0)) FOR [M_IsDeleted]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Pedro Fernandes
-- Create date: <Create Date,,>
-- Description:	Configure sql server with data in MailServer table
-- =============================================
CREATE PROCEDURE [dbo].[xp_ConfigureMailServer] 
	-- Add the parameters for the stored procedure here
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- Insert statements for procedure here
	DECLARE @Name VARCHAR(50), 
			@Server VARCHAR(50), 
			@Port INT, 
			@Username VARCHAR(120),
			@Password VARCHAR(120), 
			@Sender VARCHAR(120)

	DECLARE c CURSOR FOR 
	SELECT [Name], LTRIM(RTRIM([Server])) AS [Server], [Port], [Username], [Password], [Sender]
	FROM MailServer
	WHERE Sending = 1 AND M_IsDeleted = 0

	OPEN c  
	FETCH NEXT FROM c INTO @Name, @Server, @Port, @Username, @Password, @Sender  

	WHILE @@FETCH_STATUS = 0  
	BEGIN  
		DECLARE @UseSSL BIT
		SET @UseSSL = (CASE WHEN @Port = 25 THEN 0 ELSE 1 END)
		
		EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp @profile_name = @Name
		EXECUTE msdb.dbo.sysmail_delete_principalprofile_sp @profile_name = @Name
		EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = @Name
		EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = @Name
		
		EXECUTE msdb.dbo.sysmail_add_profile_sp  
			@profile_name = @Name,  
			@description = 'Profile used for sending outgoing notifications using Gmail.' ;  
	
		EXECUTE msdb.dbo.sysmail_add_principalprofile_sp  
			@profile_name = @Name,  
			@principal_name = 'public',  
			@is_default = 1;
	
		-- Create a Database Mail account  
		EXECUTE msdb.dbo.sysmail_add_account_sp  
			@account_name = @Name,  
			@description = 'Mail account for sending outgoing notifications.',  
			@email_address = @Sender,  
			@display_name = @Sender,  
			@mailserver_name = @Server,
			@port = @Port,
			@enable_ssl = @UseSSL,
			@username = @Username,
			@password = @Password;  
	
		EXECUTE msdb.dbo.sysmail_add_profileaccount_sp  
			@profile_name = @Name,  
			@account_name = @Name,  
			@sequence_number = 1;  

	FETCH NEXT FROM c INTO @Name, @Server, @Port, @Username, @Password, @Sender 
	END 

	CLOSE c  
	DEALLOCATE c

END
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Pedro Fernandes
-- Create date: <Create Date,,>
-- Description:	Get Mail credentials for sending emails
-- =============================================
CREATE PROCEDURE [dbo].[xp_GetMailCredentials]
	-- Add the parameters for the stored procedure here
	@Name VARCHAR(50) = NULL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	IF ISNULL(@Name, '') = ''
	BEGIN
		SELECT Id, [Name], [Server], [Port], [Username], [Password], [Sender]
		FROM MailServer
		WHERE Sending = 1 AND IsDefault = 1 AND M_IsDeleted = 0
	END
	ELSE
	BEGIN
		SELECT Id, [Name], [Server], [Port], [Username], [Password], [Sender]
		FROM MailServer
		WHERE Sending = 1 AND [Name] = @Name
	END
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Pedro Fernandes
-- Create date: <Create Date,,>
-- Description:	Get content from file
-- =============================================
CREATE PROCEDURE [dbo].[xp_GetFileContent]
	-- Add the parameters for the stored procedure here
	@Path VARCHAR(max),
	@Content VARCHAR(max) OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @sql NVARCHAR(max);

	SET @sql = N'SELECT f.BulkColumn 
	FROM OPENROWSET
	(
		BULK ''' + @Path + ''',
		SINGLE_CLOB 
	) f'

	DECLARE @ReturnTable TABLE (x VARCHAR(max))
	INSERT INTO @ReturnTable (x)
		EXEC (@sql);

	SELECT @Content = x
	FROM @ReturnTable
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Pedro Fernandes
-- Create date: <Create Date,,>
-- Description:	Replace variables
-- =============================================
CREATE PROCEDURE [dbo].[xp_ReplaceTemplateVars]
	-- Add the parameters for the stored procedure here
	@Content VARCHAR(max),
	@VarsTable dbo.TemplateVars READONLY,
	@ContentOut VARCHAR(max) OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @Name VARCHAR(50), @Value VARCHAR(max)
	SET @ContentOut = @Content

	DECLARE c CURSOR FOR 
	SELECT t.[Name], t.[Value]
	FROM @VarsTable t

	OPEN c  
	FETCH NEXT FROM c INTO @Name, @Value

	WHILE @@FETCH_STATUS = 0  
	BEGIN  
		SET @ContentOut = REPLACE(@ContentOut, '<%=' + @Name + '%>', @Value)
		FETCH NEXT FROM c INTO @Name, @Value
	END

	CLOSE c  
	DEALLOCATE c
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Pedro Fernandes
-- Create date: <Create Date,,>
-- Description:	Send emails
-- =============================================
CREATE PROCEDURE [dbo].[xp_SendMail]
	-- Add the parameters for the stored procedure here
	@profile_name VARCHAR(50) = NULL,
	@recipients VARCHAR(max),
	@reply_to VARCHAR(max) = NULL,
	@subject VARCHAR(1024),
	@body VARCHAR(max),
	@is_html BIT = 1,
	@file_attachments VARCHAR(max) = NULL,
	@query VARCHAR(max) = NULL,
	@attach_query_result_as_file BIT = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @body_format VARCHAR(20), @mailitem_id INT
	SET @body_format = (CASE WHEN @is_html = 1 THEN 'HTML' ELSE 'TEXT' END)

    -- Insert statements for procedure here
	IF @profile_name IS NULL
	BEGIN
		SELECT @profile_name = [Name]
		FROM MailServer
		WHERE Sending = 1 AND IsDefault = 1
	END

	EXEC msdb.dbo.sp_send_dbmail
		@profile_name = @profile_name,
		@recipients = @recipients,
		@subject = @subject,
		@body = @body,
		@reply_to = @reply_to,
		@body_format = @body_format,
		@file_attachments = @file_attachments,
		@query = @query,
		@attach_query_result_as_file = @attach_query_result_as_file,
		@mailitem_id = @mailitem_id OUTPUT

	SELECT @mailitem_id as mailitem_id
END
GO

sendmail.sql

USE xxx

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
 
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

USE msdb
GO

EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp @profile_name = 'Notifications'
EXECUTE msdb.dbo.sysmail_delete_principalprofile_sp @profile_name = 'Notifications'
EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = 'Office365'
EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = 'Notifications'

EXECUTE msdb.dbo.sysmail_add_profile_sp  
    @profile_name = 'Notifications',  
    @description = 'Profile used for sending outgoing notifications using Gmail.' ;  
GO

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp  
    @profile_name = 'Notifications',  
    @principal_name = 'public',  
    @is_default = 1 ;
GO


-- Create a Database Mail account  
EXECUTE msdb.dbo.sysmail_add_account_sp  
    @account_name = 'Office365',  
    @description = 'Mail account for sending outgoing notifications.',  
    @email_address = '[email protected]',  
    @display_name = 'Automated Mailer',  
    @mailserver_name = 'smtp.office365.com',
    @port = 587,
    @enable_ssl = 1,
    @username = '[email protected]',
    @password = '' ;  
GO


EXECUTE msdb.dbo.sysmail_add_profileaccount_sp  
    @profile_name = 'Notifications',  
    @account_name = 'Office365',  
    @sequence_number =1 ;  
GO

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Notifications',
	@recipients='[email protected]',
	@subject='Test message',
	@body='This is the body of the test message.
	Congrates Database Mail Received By you Successfully.'