WikiDBA

As we all know how to send mail from SQL server using Database Mail. Here is a very simple trick, where we can set a DO NOT REPLY mail message to recipients, below are the steps,
First create a Mail Profile from Database Mail as VirendraTest

Click on your SQL Server Instance ->Management->DatabaseMail->Right Click and Select Configure Database Mail

It will open Database Mail Configuration Wizard, Click Next

Choose Set Up Database mail by performing the following tasks :, Then Next

If you are configuring database mail first time, it will show as …. Click on Yes.

Give Profile Name ( Here profile name is VirendraTest and click on SMTP’s Account ADD button, It will shows New Database Mail Account

Give here Account Name, Account Description, then set SMTP part,
Email Address – donotreply@abcxyz.com ( Non exist email account at your mail server)
Server Name – Your mail server’s SMPT IP or Name, Port – 25 ( or yours port)
SMTP Authentication – Anonymous
Then Click OK

Now Click on OK

Select your default profile

Click next, next & finish

Select * from msdb..sysmail_profile
Select * from msdb..sysmail_profileaccount

Now your profile is created as VirendraTest.
Now you can send mail using sp_send_dbmail , sample code is below.
declare  @profile_name varchar(50)
  EXEC MSDB.dbo.sp_send_dbmail
       @profile_name = ‘VirendraTest’, — Your Prifile name
       @recipients   = ‘yaduvanshi.v@gmail.com’, — Mail to whom you want to send
       @subject      = ‘Test Mail !’, — Mail Subject
       @body         = ‘Dear Virendra, This is a Test Mail.’, — Mail Body part, 
       @body_format  =‘HTML’; — Mail body format

All done, recipient will get mail from your database mail profile’s account’s configured email id, here its donotreply@abcxyz.com, if recipient will reply this mail, he will got bounce back mail/failure mail notification in his/her mail inbox.

Note : As per my knowing, its some how a bug, we can send a mail from anyone’s mail id or non exist mail id.

Lets enjoy SQL server mailing !