Solved

sp_send_dbmail format

Posted on 2011-02-16
6
1,080 Views
Last Modified: 2012-06-21
when sending an email through the above proc in SQL Server to outlook with HTML format, it is jumbled and not proper. what does outlook need for it to receive the format in nice looking way?

thanks
0
Comment
Question by:anushahanna
  • 3
  • 3
6 Comments
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
You need to format the document like in the example below:

DECLARE @title nvarchar(200)
DECLARE @tableHTML nvarchar(MAX)

SET @title = 'Summary Report - Passive and Active List'
SET @tableHTML =
    N'<H1>' + @title + '</H1>' +
    N'<table border="1">' +
    N'<tr>' +
      N'<th>gway</th>' +
      N'<th>valid_count</th>' +
      N'<th>active</th>' +
      N'<th>excluded</th>' +
   CAST(
            (SELECT
                  td = gway, '',
                  td = flag, '',
                  td = case when active = 1 then 'YES' else 'NO' END ,'',
                  td = case when gel = 1 then 'YES' else 'NO' END ,''
            FROM  servername.dbo.tablename
            FOR XML PATH('tr'), TYPE)
      AS NVARCHAR(MAX) ) + N'</table>' ;

IF @tableHTML is not null
BEGIN
    EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'your_profile',
            @recipients='anushahanna@mail.com',
            @subject = @title,
            @body = @tableHTML ,
            @body_format = 'HTML' ;
END
0
 
LVL 6

Author Comment

by:anushahanna
Comment Utility
thanks lcohan- does this make sure any recipient mails, will receive it ok, in proper format (as long the recipient mail system supports HTML)?
0
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
Comment Utility
That is correct including even blackberry users on both 2005 and 2008 so far.
Please test is and adjust it to your table structure - other nice thing is that if table is empty like you have nothing to send that particular time they won't get an email with nothing attached.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 6

Author Comment

by:anushahanna
Comment Utility
lcohan- thanks.

can you please add some dummy values to the below code, just to prove the point of how pretty this can be made. can we add 2 rows of data for the 4 columns and put some colors?
DECLARE @title nvarchar(200)
DECLARE @tableHTML nvarchar(MAX)

SET @title = 'Summary Report - FunctionCode'
SET @tableHTML =
    N'<H1>' + @title + '</H1>' +
    N'<table border="1">' +
    N'<tr>' +
      N'<th>Column1</th>' +
      N'<th>Column2</th>' +
      N'<th>Column3</th>' +
      N'<th>Column4</th>'  + N'</table>' ;

IF @tableHTML is not null
BEGIN
    EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'ProfileName',
            @recipients='anushahanna@mail.com',
            @subject = @title,
            @body = @tableHTML ,
            @body_format = 'HTML' ;
END

Open in new window

0
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 500 total points
Comment Utility
I'm pretty sure ther must be a way to color it and you should be able to find that in SQL BOL however to get the example you wanted just replace your mail profile and actual email in query below:

create table #test (column1 int, column2 text, column3 text, column4 text)
insert into #test SELECT 1 ,'text2','text3','text4'
insert into #test SELECT 2 ,'code2','code3','code4'


DECLARE @title nvarchar(200)
DECLARE @tableHTML nvarchar(MAX)

SET @title = 'Summary Report - FunctionCode'
SET @tableHTML =
    N'<H1>' + @title + '</H1>' +
    N'<table border="1">' +
    N'<tr>' +
      N'<th>Column1</th>' +
      N'<th>Column2</th>' +
      N'<th>Column3</th>' +
      N'<th>Column4</th>' + 
     CAST(
            (SELECT
                  td = Column1 ,'',
                  td = Column2,'',
                  td = Column3,'',
                  td = Column4,''
            FROM  #test WHERE column1 in (1,2)
            FOR XML PATH('tr'), TYPE) 
	 AS NVARCHAR(MAX) ) 
	 + N'</table>' ;

IF @tableHTML is not null
BEGIN
    EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'mail_profile',
            @recipients='user@mail.com',
            @subject = @title,
            @body = @tableHTML ,
            @body_format = 'HTML' ;
END

drop table #test

Open in new window

0
 
LVL 6

Author Comment

by:anushahanna
Comment Utility
very nice, lcohan - thanks much.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Granting full access permission allows users to access mailboxes present in their database. By giving full access permission one can open and read the content of any mailbox but cannot send emails from that mailbox.
If you don't know how to downgrade, my instructions below should be helpful.
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now