Setup Database Mail in SQL Server 2012 Express

SQL Server 2012 Express still supports Database Mail (DB Mail), but it’s well hidden.
Step 1
One should enable Database mail on the server, before setting up the database mail profile and accounts, Either can be done by using Transact SQL to enable Database Mail. Run the following statement in the SQl Server Management Studio.

use master
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Database Mail XPs',1
--go
--sp_configure 'SQL Mail XPs',0
go
reconfigure
go

Step 2
One can enable the Configuration Component Database account by using the sysmail_add_account procedure.
You’d execute the below query.

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'TestMailAccount',
@description = 'Mail account for Database Mail',
@email_address = 'tanmaya@mydomain.com',
@display_name = 'MyAccount',
@username='tanmaya@mydomain.com',
@password='1qwe432',
@mailserver_name = 'mail.mydomain.com'

Step 3
Now one should create a Mail profile.
You’d execute the below query.

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'TestMailProfile',
@description = 'Profile needed for database mail'

Step 4
Next will be the sysmail_add_profileaccount procedure, to include the Database Mail account which is created in step 2, along with the Database Mail profile in step 3.
You’d execute the below query.

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'TestMailProfile',
@account_name = 'TestMailAccount',
@sequence_number = 1

Step 5
You’d execute the below query.

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

UPDATE msdb.dbo.sysmail_server SET enable_ssl=1 

Step 6
After all these settings done, try to send a test mail from MSSQL Server.
You’d execute the below query.

declare @body1 varchar(100)
set @body1 = 'Server :'+@@servername+ ' Test DB Email '
EXEC msdb.dbo.sp_send_dbmail @recipients='tanmaya@mydomain.com',
@subject = 'Test',
@body = @body1,
@body_format = 'HTML' ;

Step 7

You can review the logs linked to Database Mail.

You’d execute the below query.

SELECT * FROM msdb.dbo.sysmail_event_log