mister.magoo (10/8/2013)
It's hard to help because we can't see the whole process as you can...Can you try this to see if it works?(replace the profile and email address and fix the BR tags)
exec msdb.dbo.sp_send_dbmail@profile_name='YOUR PROFILE',
@recipients='someone@email.com',
@subject='test html',
@body='test1.pdf< br/ >test2.pdf< br/ >',
@body_format='HTML'
Fixed it!! might not be the right way but done it.
In my variable @PDFFileName, i pass through '< br/ >' as normal.
DECLARE @PDFFileName VARCHAR(8000) = (SELECT STUFF(( SELECT ' ' + PDF_FileName + '.pdf' + '<br/>' FROM @PDFResult ORDER BY 1 ASC FOR XML PATH('')),1,1,''))
As the data gets translated from BR to the & l t ; b r / & g t ; etc, I do an additional replace to remove those characters with the BR i expect.
SET @BodyHTML =
(
SELECT
[HTML] = REPLACE(REPLACE(REPLACE(HTML,'[[FileName]]',@ProcessingFile),'[[PDF_FileName]]',@PDFFileName),' & l t ; b r / & g t ; ','<br/>') -- sorry for spaces but its changing
FROM
@temptable
)
Returned the results as i hoped for
1.pdf
2.pdf
3.pdf
...