Sending Emails and atteching files using SMTP SQL server

--This is a One Time Set UP

--Enable the Database Mail option

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Database Mail XPs', 1; -- 0 to Disable and 1 to enable

GO

RECONFIGURE

GO

 

 

--Create a Database Mail account to send Email

EXECUTE msdb.dbo.sysmail_add_account_sp

     @account_name = 'AS', --Give an Account Name

     @description = 'Test Profile',

     @email_address = 'abkthebond@siemens.com', --Using which SMTP account the mail will be sent

     @display_name = 'Andy',

     @mailserver_name = '134.144.122.244' --Address of the SMTP Server

 

--Create a  Database Mail profile

EXECUTE msdb.dbo.sysmail_add_profile_sp

     @profile_name = 'Anand',

     @description = 'Test Profile'

 

--add the Database Mail account and Database Mail profile created in previous steps

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

     @profile_name = 'Anand',

     @account_name = 'Anand',

     @sequence_number = 1

 

--Grant the Database Mail profile access to the msdb public database role and to make the profile the default Database Mail profile

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

     @profile_name = 'Anand',

     @principal_name = 'public',

     @is_default = 1 ;