-- 1) Enable Database Mail feature (disabled by default)
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Database Mail XPs', 1;
RECONFIGURE;
GO

-- 2) Create a Database Mail profile and the SMTP account behind it
EXEC msdb.dbo.sysmail_add_profile_sp
	@profile_name = 'SchedulerMailProfile',
	@description = 'Profile used to send scheduler notifications';
GO

EXEC msdb.dbo.sysmail_add_account_sp
	@account_name = 'SchedulerMailAccount',
	@email_address = 'notifier@live-tech.com',
	@display_name = 'SQL Server Scheduler',
--	@replyto_address nvarchar(128) = NULL,
--	@description nvarchar(256) = NULL,  
	@mailserver_name = 'mail001.livetech.local' -- your SMTP server
--	@mailserver_type sysname = N'SMTP',
--	@port = 25, -- INT
--	@username = NULL, -- NVARCHAR(128)
--	@password = NULL, -- NVARCHAR(128)
--	@use_default_credentials = 0, -- BIT
--	@enable_ssl = 0, -- BIT
--	@account_id = NULL OUTPUT -- INT
GO

-- 3) Link account to profile, and make it the default for the msdb database
EXEC msdb.dbo.sysmail_add_profileaccount_sp
	@profile_name = 'SchedulerMailProfile',
	@account_name = 'SchedulerMailAccount',
	@sequence_number = 1;
GO

EXEC msdb.dbo.sysmail_add_principalprofile_sp
	@profile_name = 'SchedulerMailProfile',
	@principal_name = 'public',
	@is_default = 1;
GO

EXEC msdb.dbo.sp_send_dbmail
	@profile_name = 'SchedulerMailProfile',
	@recipients = 'mauro.luigi.cortese@live-tech.com',
--	@copy_recipients = 'mauro.luigi.cortese@live-tech.com',
--	@blind_copy_recipients = 'mauro.luigi.cortese@live-tech.com',
	@subject = 'TEST',
	@body = 'EMAIL DI PROVA SQL',
	@body_format = 'HTML';

SELECT * FROM [msdb].[dbo].[sysmail_mailitems]
SELECT * FROM [msdb].[dbo].[sysmail_event_log]


DECLARE @Date DATETIME = GETDATE()
EXEC msdb.dbo.sysmail_delete_mailitems_sp 
	@sent_before = @Date; 
--	@sent_status= '' -- (unsent, sent, failed, retrying).

EXEC msdb.dbo.sysmail_delete_log_sp

SELECT * FROM [msdb].[dbo].[sysmail_mailitems]
SELECT * FROM [msdb].[dbo].[sysmail_event_log]