Recently one of my projects had a requirement to send email from SQL Server to certain recipients. A developer asked if they could use sp_send_dbmail and I said yes, I use it all the time and didn’t think too much about it until I got an email from her mentioning an issue they were having. Researching the problem more I found a solution I was not aware of and wanted to share it.
The problem they were having was with the way the “From” address was resolving. They wanted it to read “Do Not Reply” but what was happening was the email header resolved to the Server name it was sent from.
Researching the sp_send_dbmail did not provide much insight so I started experimenting a little. One option I provided to them was the use of Powershell. I use that on one of my servers and the “From” email resolves to what I name it in the script. I provided the simple 7 lines of code as an example and suggested I could help them work it since she was not familiar or comfortable with using Powershell.
This is the Powershell code I use for sending email.
I suggested she send me the stored procedure she was planning on using so I could see what she was doing and potentially convert to Powershell. If not the entire stored procedure I envisioned at the very least the sending of the email function.
While I was waiting on her reply I continued to experiment with the sp_send_dbmail and discovered this little trick.
Almost every problem, issue or requirement will have multiple ways to solve it. What the best solution for you may not work for someone else. It may not work each time you need to use it where you work. Discovering different uses or ways of doing the same thing is one of the reasons I love what I do. Sharing what I have learned is one way I try to pay it forward.