Send Email as HTML Table from SQL Server

Question

I am trying to send an email from my SQL Server using SQLmail, but I cannot get the output I desire. Below is an example of what I want:

email body:-

Hello:

Following program has been submitted by @username at getdate() for your review. Please review the program and take further action.

Regards,

@username

---

---

Answer 1

Before attempting to answer your question, I would like to request you to make your questions more clear, like posting any script you already written for this or any error you are facing etc, so that we can easily write the query for you.

The below is the SQL script that will help you to meet your requirement. The script is tested working.

declare @EmailBody  NVARCHAR(MAX);

declare @username VARCHAR(50) = 'Sean';

SET @EmailBody = N'<p style="font-family:arial; font-size:13px;">'+
                               'Hello:<br/><br/>'+
                               'Following program has been submitted by '+ @username +' at '+convert(varchar(50),getdate(),103)+ ' for your review.'+
                               'Please review the program and take further action.<br/></p>'+
                               '<table border="1" cellspacing="0" cellpadding="4" style="font-family: Arial; font-size: 11px;">' +
                               '<tr>
                                    <td>Program No:</td>
                                    <td>xxxxxxxxxxx</td>
                                </tr>

                                <tr>
                                    <td>Description:</td>
                                    <td>xxxxxxxxxxx</td>
                                </tr>

                                <tr>
                                    <td>BUnit:</td>
                                    <td>xxxxxxxxxxx</td>
                                </tr>

                                <tr>
                                    <td>Program Type:</td>
                                    <td>xxxxxxxxxxx</td>
                                </tr>

                                <tr>
                                    <td>Product Line:</td>
                                    <td>xxxxxxxxxxx</td>
                                </tr>';

--select @EmailBody

EXEC msdb.dbo.SP_SEND_DBMAIL
                        @recipients='add recepients here seperated by ; eg :  abc@xyz.com;pqr@xyz.com',
                        @subject = 'Write email subject here',
                        @body = @EmailBody,
                        @body_format = 'HTML';