You can use SQL Mail with SQL Server Express edition.
Borrowing liberally from Sending Mail using SQL Server Express Edition
To configure SQL mail we need to follow below steps.
--Create Sysmail Account
--Use sysmail_add_account_sp stored procedure of MSDB database to configure sysmail account.
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'MailTest',
@description = 'Sent Mail using MSDB',
@email_address = 'YourEmail@test.com',
@display_name = 'DisplayName',
--@username='umashankar@queryingsql.com',
--@password='password',
@mailserver_name = 'mail.server.com'
--Creating Database Profile
--Use sysmail_add_profile_sp stored procedure of MSDB database to configure Database Profile.
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'MailTest',
@description = 'Profile used to send mail'
--Add database Mail account to profile
--Use sysmail_add_profileaccount_sp stored procedure of MSDB database to map database mail account to Profile.
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'MailTest',
@account_name = 'MailTest',
@sequence_number = 1
--Grants permission for a database user or role to use a Database Mail profile.
--To Grant permission for a database user or role to use a Database Mail profile use sysmail_add_principalprofile_sp.
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'MailTest',
@principal_name = 'public',
@is_default = 1 ;
--Enable 'Database Mail XPs'.
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO
--Send Mail using Created Profile
exec msdb.dbo.sp_send_dbmail @profile_name = 'MailTest', @recipients = 'receiver@queryingsql.com', @subject = 'Mail Test', @body = 'Mail Sent Successfully', @body_format = 'text'
I will say that SQL Mail is deprecated and may be removed in a future version of SQL Server Express, but it still seems to work in the latest edition.
