body_format not working when specified in cursor

• zach 26717

  SSC Enthusiast

  Points: 153

  More actions

  July 23, 2015 at 7:17 am

  #319953

  I am using the following code to grab values from an "email_address" field, and sending an email message to those recipients found in the table. I previously had emails sending in html format with the sp_send_mail, but now in this new script, the email sends to the right people, but it is just showing up as plain text.

  DECLARE

  @email VARCHAR(64),

  @format VARCHAR(20),

  @emailSubject varchar(128),

  @emailBody varchar(128)

  DECLARE c1 CURSOR FOR

  SELECT

  email_address

  FROM customeremailtest

  WHERE id > 0

  OPEN c1

  FETCH NEXT FROM c1 INTO @email

  WHILE @@FETCH_STATUS <> -1

  BEGIN

  SELECT

  @format = 'HTML',

  @emailSubject = 'Please send Feedback for Order#',

  @emailBody = '<img src="http://static.wixstatic.com/media/cae1a3_321383f47b764679b6a325f76ebcc942.jpg_srz_p_308_97_75_22_0.50_1.20_0.00_jpg_srz" />'

  --you cannot concatenate strings as the parameters for a query, they must be eitehr a static string or an already built variable.

  EXEC msdb.dbo.sp_send_dbmail

  @profile_Name ='P21 Alert',

  @recipients= @email ,

  @subject = @emailSubject,

  @body = @emailBody,

  @body_format = @format

  FETCH NEXT FROM c1 INTO @email

  END

  CLOSE c1

  DEALLOCATE c1