HTML in SQL Mail

  • Hi All,

    I have tried to Send a mail using SQL Mail where I passed a query which will be the body of the mail. The mail was sent successfully but the formatting is very odd. My question is how can I format the text within the mail so that it looks clean. Is it possible to send mail using SQLMail in RichText/HTML format. Please help.

    With warm regards,

    Pradeep

  • I'm not sure what you can do in SQL mail.... we are using SMTP mail (EXEC master..xp_smtp_sendmail) which gives good opportunity to format the output. We use sp_makewebtask procedure to create HTML file from the result of query, and then insert it into the body of a message or (if it exceeds the 64k limit for HTML e-mail) send it as attachment.

  • xp_smtp_sendmail works great on job. Does anyone know of a way to use it if fatal alerts arise in the SQL log?


    Terry

  • I can't find the xp_smpt_sendmail proc.  Any ideas why?

    Error message in Query Analyzer:  Could not find stored procedure 'master.xp_smtp_sendmail'.

  • You are using wrong name.

    It must be master.dbo.xp_smtp_sendmail

    And you need probably visit this site:

    http://www.sqldev.net/xp/xpsmtp.htm

     

    _____________
    Code for TallyGenerator

  • Go to http://www.sqldev.net/xp/xpsmtp.htm It's a great freebie and does everything you could ask for, for the price!


    Terry

  • I am using xp_smtp_sendmail , I am geting error

    Error: sending message

    Server response: 550 5.7.1 Unable to relay for bhushan683@gmail.com

    I have set the relay for smtp server to 127.0.0.1 but it is still giving me error

    EXEC master.dbo.xp_smtp_sendmail

        @server = 'localhost',

        @from = 'bhushan683@gmail.com',

        @to =   'bhushan683@gmail.com',

        @subject = 'HTML Testing...',

        @type = 'text/html',

        @message = N'<HTML><H1>this is some content for the body part object</H1></HTML>'

    Anybody have suggestions on these please reply

     

  • I don't see any reference to the SMTP server. Is what's posted the entire script that you're trying to execute?


    Terry

  • Yeah, that could be it... Bhushan, is the computer on which this SP runs configured as mail server? I guess you are launching that on your SQL Server... which probably isn't mailserver at the same time (also never tried using "localhost" here). Try changing this line to something like:

    @server  = N'COMPANY_MAIL_SERVER_NAME'

  • Try this way

     Set @messageToSend= '<HTML><H1>this is some content for the body part object</H1></HTML>'

    Execute @rc=master.dbo.xp_smtp_sendmail

        @server = 'SERVERNAME',

        @from = 'bhushan683@gmail.com',

        @to =   'bhushan683@gmail.com',

        @subject = 'HTML Testing...',

        @type = 'text/html',

        @message = @messageToSend

  • This example uses link servers to get a space report and email it in HTML format. You can use this as a starting block/template.

    Hope you find it useful. If not drop another post and I'll fish out more examples for you to work with.

    SET NOCOUNT ON

    DECLARE @tableHTML NVARCHAR(MAX) ;

    declare @sub varchar(200)

    declare @table table

    (servername varchar(50),

    drive varchar(20),

    free_space_mb varchar(20),

    total_size_mb varchar(20),

    free_space_percent varchar(20),

    sample_date datetime)

    select @sub = 'SQL Server Physical Space Report'+space(1)+convert(varchar(11), getdate(), 103)+space(1)+convert(varchar(11), getdate(), 108)

    insert into @table select * from OPENQUERY([SV-XXXX],'select * from master .. QTX_FREE_SPACE')

    insert into @table select * from OPENQUERY([SV-XXXX],'select * from master .. QTX_FREE_SPACE')

    insert into @table select * from OPENQUERY([SV-XXXX],'select * from master .. QTX_FREE_SPACE')

    insert into @table select * from OPENQUERY([SV-XXXX],'select * from master .. QTX_FREE_SPACE')

    insert into @table select * from OPENQUERY([SV-XXXX],'select * from master .. QTX_FREE_SPACE')

    insert into @table select * from OPENQUERY([SV-XXXX],'select * from master .. QTX_FREE_SPACE')

    insert into @table select * from OPENQUERY([SV-XXXX],'select * from master .. QTX_FREE_SPACE')

    insert into @table select * from OPENQUERY([SV-XXXX],'select * from master .. QTX_FREE_SPACE')

    insert into @table select * from OPENQUERY([SV-XXXX],'select * from master .. QTX_FREE_SPACE')

    insert into @table select * from OPENQUERY([SV-XXXX],'select * from master .. QTX_FREE_SPACE')

    insert into @table select * from OPENQUERY([SV-XXXX],'select * from master .. QTX_FREE_SPACE')

    insert into @table select * from OPENQUERY([SV-XXXX],'select * from master .. QTX_FREE_SPACE')

    insert into @table select * from OPENQUERY([SV-XXXX],'select * from master .. QTX_FREE_SPACE')

    insert into @table select * from OPENQUERY([SV-XXXX],'select * from master .. QTX_FREE_SPACE')

    insert into @table select * from OPENQUERY([SV-XXXX],'select * from master .. QTX_FREE_SPACE')

    SET @tableHTML =

    N' ' +

    N' ' +

    N' ' +

    N' ' +

    N' '+

    N' ' +

    N' ' +

    N' ' +

    N' ' +

    N' ' +

    cast((

    selecttd = servername, '',

    td = upper(drive)+':\', '',

    td = free_space_mb+space(1)+'MB' , '',

    td = case when total_size_mb = '' then 'Mirror' when total_size_mb is null then 'Mirror' else total_size_mb+space(1)+'MB' end, '',

    td = case when free_space_percent = '' then 'Mirror' when free_space_percent is null then 'Mirror' else free_space_percent+space(1)+'%' end, '',

    td = convert(varchar(11), sample_date, 103)+space(1)+convert(varchar(11), sample_date, 108), ''

    from @table

    where convert(varchar(11), sample_date, 103) = convert(varchar(11), getdate(), 103)

    FOR XML PATH('tr'), TYPE

    ) AS NVARCHAR(MAX) ) +

    N' ' +

    N'

    ' +

    N' ' +

    N'

    ' +

    N' ' ;

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'anyone',

    @recipients = 'anyone@anywhere.com',

    @subject = @sub,

    @body = @tableHTML,

    @body_format = 'HTML' ;

  • davidw,

    I appreciate you posting the code for writing out an email in HTML; however, The beginning of the line of code

    AS NVARCHAR(MAX) ) +

    got interpreted as a "winkie face" emoticon! ... can you please post the code in

    (remove the spaces)

    [ quote ]

    [ / quote ]

    brackets for us?

    Thanks in advance,

    "Key"
    MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply