How to embed image in html and send html as email by msdb.dbo.sp_send_dbmail?

Question

I can use msdb.dbo.sp_send_dbmail to send out email in html format. It is very nice for text only in terms of format. For example:

EXEC msdb.dbo.sp_send_dbmail
  @recipients = @p_recipients,
  @subject = @v_subject,
  @body=@emailHTML, 
  @body_format = 'HTML';

However, if I want to include images such as trend generated from data on my SQL server, and to embed them in the html (@emailHTML), what html tag should I use?

If I use [img] tag, then I need to set the src attribute. The image generated are saved in my local SQL server's hard disk. I could place them in IS server web page area. But all those web servers are intranet accessible but not outside of my work.

Is there any way to embed image in the email? How I can set html to embed images?

I am using Microsoft SQL server 2005. I prefer to msdb.dbo.sp_send_dbmail to send out reports out as email. I have much control of html format. If there is no way to that, I may have to send images as attachment files.

Answer 1

I think I got the answer:

EXEC msdb.dbo.sp_send_dbmail
   @recipients = '[email protected]',
   @subject = 'test',
   @file_attachments = 'C:\MyFolder\Test\Google.gif;C:\MyFolder\Test\Yahoo.gif',
   @body=N'<p>Image Test</p><img src="Google.gif" /><p>See image there?</p>
        <img src="Yaoo.gif" /><p>Yahoo!</p>', 
   @body_format = 'HTML';

basically, add the image as attachment, and the src attribute contains just the image file name, no any path is needed. If more than one image files are needed, just use ";" to separate them.

I send email to my outlook email and it works. Try it to my yahoo email....