Use [msdb]
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO
-- Delete Principle profile if exits
BEGIN TRY
EXECUTE msdb.dbo.sysmail_delete_principalprofile_sp
@principal_id = 0,
@profile_name = '#ProfileName#'
END TRY
BEGIN CATCH
PRINT 'Invalid Pronciple Profile'
END CATCH
GO
-- Delete profile account if exits
BEGIN TRY
EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp
@profile_name = '#ProfileName#',
@account_name = '#AccountName#'
END TRY
BEGIN CATCH
PRINT 'Invalid Profile Account'
END CATCH
GO
-- Delete account if exits
BEGIN TRY
EXECUTE msdb.dbo.sysmail_delete_account_sp
@account_name = '#AccountName#'
END TRY
BEGIN CATCH
PRINT 'Invalid Account'
END CATCH
GO
-- Delete profile if exits
BEGIN TRY
EXECUTE msdb.dbo.sysmail_delete_profile_sp
@profile_name = '#ProfileName#'
END TRY
BEGIN CATCH
PRINT 'Invalid Profile'
END CATCH
GO
-- Create a Database Mail account
DECLARE @emailaddress varchar (100)
,@mailservername varchar(128)
SET @emailaddress = '#EmailId#'
SET @mailservername = '#MailServerName)'
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = '#AccountName#',
@description = 'Mail account for administrative e-mail.',
@email_address = @emailaddress ,
@display_name = 'Automated Mailer',
@mailserver_name = @mailservername ,
@port = 25,
@use_default_credentials = 1
GO
-- Create a Database Mail Profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = '#ProfileName#',
@description = 'Profile used for mail'
GO
-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = '#ProfileName#',
@account_name = '#AccountName#',
@sequence_number = 1
GO
---- Grant access to the profile to the DBMailUsers role
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@principal_id = 0,
@profile_name = '#ProfileName#',
@is_default = 1
GO