Solved

sp_send_dbmail format

Posted on 2011-02-16
6
1,108 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
ID: 34911376
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
ID: 34935836
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
ID: 34954808
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 6

Author Comment

by:anushahanna
ID: 34979810
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
ID: 34981164
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
ID: 34982861
very nice, lcohan - thanks much.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you don't know how to downgrade, my instructions below should be helpful.
MS Outlook is a world-class email client application that is mainly used for e-communication globally.  In this article, we will discuss the basic idea about MS Outlook, its advanced features, and types of MS Outlook File formats.
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…
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…

809 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