How can we send email by sql script ?

To enable Database Mail XP

 1
 2USE msdb;
 3GO
 4EXEC sp_configure 'Database Mail XPs'
 5GO
 6RECONFIGURE
 7GO
 8EXEC sp_configure 'Database Mail XPs', 1
 9GO
10RECONFIGURE
11GO
12EXEC sp_configure 'Database Mail XPs'
13GO

To configure database mail

 1
 2USE msdb
 3GO
 4
 5DECLARE @ProfileName VARCHAR(255)
 6DECLARE @AccountName VARCHAR(255)
 7DECLARE @SMTPAddress VARCHAR(255)
 8DECLARE @EmailAddress VARCHAR(128)
 9DECLARE @DisplayUser VARCHAR(128)
10
11SET @ProfileName = 'DBMailProfile';
12SET @AccountName = 'DBMailAccount';
13SET @SMTPAddress = 'smtp.server.com';
14SET @EmailAddress = 'myemail@email.com';
15SET @DisplayUser = 'Myname Noname';
16
17EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = @AccountName, @email_address = @EmailAddress, @display_name = @DisplayUser, @mailserver_name = @SMTPAddress
18
19EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = @ProfileName
20
21EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = @ProfileName, @account_name = @AccountName, @sequence_number = 1;
22
23--Now that everything is setup, I will send a test e-mail.
24EXEC msdb.dbo.sp_send_dbmail @recipients = N'myemail@email.com', @body = 'Test Email Body', @subject = 'Test Email Subject from SQL', @profile_name = 'DBMailProfile'