How to add a variable to @subject in sp_send_dbmail

  • I've been using sp_send_dbmail and now I need to include a varchar(20) variable to the @subject

    EXEC msdb.dbo.sp_send_dbmail

    @recipients = @emailaddys,

    @blind_copy_recipients = 'myemail@email.com',

    @subject = 'Subject line goes here' + @variable ,

    @body = @tableHTML,

    @body_format = 'HTML' ;

    I keep getting "Incorrect syntax near '+'

    I've tried passing it as a string but I tend to have issues with all the '''' and ''''' :unsure:

    Thanks

  • Hi there, try something like this:

    -- The message that will be dispayed in the <BODY> element of the Mail

    -- Please note taht explicit typing of <BODY> is not required as sp_send_dbmail will take care of it

    SET @HTMLBody = ''

    SET @HTMLBody =

    N''+@UserName+'Thanks for using SSC.com'

    --Subject of the Mail

    SET @MailSubject = ''

    SET @MailSubject = 'Learn and teach'

    --Trigger the mail

    EXEC @RetCode = msdb.dbo.sp_send_dbmail

    @profile_name = 'SSC Notification Mailer',

    @recipients = @EMailAddress,

    @subject = @MailSubject,

    @body = @HTMLBody,

    @body_format = 'HTML',

    @mailitem_id = @MailItemID OUTPUT

    --Update after successfully sending Mail Notification

    IF @RetCode = 0

    BEGIN

    SET @Msg = 'Success'

    RAISERROR(@Msg,0,1) WITH NOWAIT

    END

    ELSE

    BEGIN

    SET @Msg = 'Mail to User: '+@UserName+' FAILED'

    RAISERROR(@Msg,0,1) WITH NOWAIT

    END

    Hope this helps you!

  • That got me on the right path.

    Thanks and I owe you a warm up on coffee 🙂

  • Wow, it is so nice and warm when people appreciate! Thanks Hoss! 🙂

  • What if you wanted to insert the contents of an .html file into the BODY of that email? Still searching posts before creating a new topic, and this one was similar and recent. Thanks!

  • My first thought is how is the content arriving in the html file? Is it static or dynamic content?

  • Well, funny you should ask...

    My intent is to have a parameterized SSRS report automatically run when certain data is entered (or scheduled), it's results "Saved As..." a .mhtml (web archive) file.

    It's the contents of this file that I'd like to comprise the body of the email that's sent. So, to answer your question... I *think* it's static html.

    (BTW, if you've got any suggestions on how to get an SSRS report to run immediately and save a certain way, preferably from within SSIS, I'm all ears.)

    THANK YOU!

  • I haven't played with SSRS but in SSIS there is a Send Mail option but I found it a little too structured for some of my projects.

    Something I might explore is building an SSIS package to import the html file and then use the send mail function for emailing. Once the package is installed, I'm assuming it could be launched via a trigger when a change is made.

    Hope this helps in some way...

  • This post help me alot

  • I'm also having similar issuer. I have below codes but i'm not sure how to define variable emails from same table

    use

    db

    go

    DECLARE @tableHTML NVARCHAR(MAX) ;

    SET @tableHTML =

    N'<H2>Dear User(USD)</H2>' +

    N'<table border="1">' +

    N'<tr><th>Column1</th><th>Column2</th>' +

    CAST ( ( SELECT td = [Column1],'',

    td = [Column2], ''

    FROM dbo.table1

    FOR XML PATH('tr'), TYPE

    ) AS NVARCHAR(MAX) ) +

    N'</table>'

    +N'

    Regards,</br>' +

    + N'XX'

    EXEC msdb.dbo.sp_send_dbmail @recipients='user@mail.com',

    @subject = 'Subject Line',

    @profile_name = 'MyProfile',

    @body = @tableHTML,

    @body_format = 'HTML' ;

  • I'm using sql2008

  • It worked for me. I sorted out by myself.

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

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