May 24, 2010 at 10:44 pm
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
May 24, 2010 at 11:00 pm
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!
May 24, 2010 at 11:10 pm
That got me on the right path.
Thanks and I owe you a warm up on coffee 🙂
May 24, 2010 at 11:17 pm
Wow, it is so nice and warm when people appreciate! Thanks Hoss! 🙂
May 26, 2010 at 11:45 am
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!
May 26, 2010 at 12:25 pm
My first thought is how is the content arriving in the html file? Is it static or dynamic content?
May 26, 2010 at 12:43 pm
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!
May 26, 2010 at 1:18 pm
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...
January 28, 2011 at 10:57 pm
This post help me alot
August 24, 2014 at 2:14 am
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' ;
August 24, 2014 at 2:18 am
I'm using sql2008
August 24, 2014 at 3:24 am
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