Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 How to send email using HTML style with picture?

Author  Topic 

hornet
Yak Posting Veteran

96 Posts
Posted - 2008-06-05 : 00:53:59
Hi all!

I have a proc which send email as a text. But now I have to rewrite her to send email as html and embed a picture there.

I use DatabaseMail with sp_send_dbmail.
Does anybody know how to sort out it?

If somebody another way to send email as html with picture don't hesitate, say me.

Thanks in advance!

nathans
Aged Yak Warrior

938 Posts
Posted - 2008-06-05 : 01:21:14
Do you need to embed the image in the email or reference an online image within the email?

If the latter, then sending an HTML email with an <IMG> tag should do the trick. If not, how about linking the image as an attachment?



Nathan Skerl
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2008-06-05 : 01:22:50
This is a good article on Email and sql

http://classicasp.aspfaq.com/email/how-do-i-send-e-mail-from-sql-server.html
Go to Top of Page

hornet
Yak Posting Veteran

96 Posts
Posted - 2008-06-05 : 01:39:20
quote:
Originally posted by nathans

Do you need to embed the image in the email or reference an online image within the email?

If the latter, then sending an HTML email with an <IMG> tag should do the trick. If not, how about linking the image as an attachment?



Nathan Skerl


Nathan, I have to send email like html page with background color and where embed an image of phone depence on user choice and also i have to embed in letter hyperlink.
Don't u know is it possible? And also i'd like to ask ho to change link
http://... on word something like "reference"?

Thanks!
Go to Top of Page

cruxmagi
Starting Member

38 Posts
Posted - 2008-06-05 : 01:50:21
Declare @Body varchar(300)
SET @Body = '<table width="700" border="0" cellspacing="0" cellpadding="0"><tr><td><a href="' + @Link + '"><img src="' + @ImgName + '" border="0" width="270" height="146" /></a></td></tr><tr><td bgcolor="#00adef" height="5"></td>
</tr><tr><td><font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#333333"><p><br /><br />Dear <b><i> ' + @UserId +
',</i></b></p><br/><p>Some sort of message. <br /></p></font>' +
'write wt ever u want.<br /><br />sample text <a href="'+@Link+'" target="_blank">click here</a><br/><br/>' +
'<p><font color="#333333" size="2" face="Verdana, Arial, Helvetica, sans-serif"><b><u> DEsc sample</u></b> </font><br/><br/>' + @Description +'<br/></p><p><br/><a href="' + @Link + '"><img src="' + @ImageSrc + '" /><a></p>' +
'<font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#333333"><p><br />Thanks,<br />YourName</p><p>  </p><p> </p></font></td></tr><tr>
<td height="20" bgcolor="#ebebeb"><font face="Verdana, Arial, Helvetica, sans-serif" size="1"> All Rights Reserved</font></td></tr></table>'


EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SiteName',
@recipients = @EmailId,
@body_format ='HTML',
@body = @Body,
@subject = 'SqlServer HTML mail';
Go to Top of Page

nathans
Aged Yak Warrior

938 Posts
Posted - 2008-06-05 : 01:58:01
You can easily send an HTML email via DB Mail that references an online image:

exec msdb.dbo.sp_send_dbmail
@profile_name		= 'YourProfile',
@recipients			= 'you@yourDomain',
@importance			= 'Normal',
@subject			= 'test subj',
@body				= '<img src="http://www.sqlteam.com/images2/SqlTeamHDR2.jpg" />',
@body_format		= 'HTML',
@file_attachments	= null


I think this is what you are after. For background colors and link tags you can look up HTML reference here:

http://www.w3schools.com/HTML/html_links.asp
http://www.w3schools.com/Html/html_backgrounds.asp



Nathan Skerl
Go to Top of Page

hornet
Yak Posting Veteran

96 Posts
Posted - 2008-06-05 : 06:34:07
quote:
Originally posted by cruxmagi

Declare @Body varchar(300)
SET @Body = '<table width="700" border="0" cellspacing="0" cellpadding="0"><tr><td><a href="' + @Link + '"><img src="' + @ImgName + '" border="0" width="270" height="146" /></a></td></tr><tr><td bgcolor="#00adef" height="5"></td>
</tr><tr><td><font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#333333"><p><br /><br />Dear <b><i> ' + @UserId +
',</i></b></p><br/><p>Some sort of message. <br /></p></font>' +
'write wt ever u want.<br /><br />sample text <a href="'+@Link+'" target="_blank">click here</a><br/><br/>' +
'<p><font color="#333333" size="2" face="Verdana, Arial, Helvetica, sans-serif"><b><u> DEsc sample</u></b> </font><br/><br/>' + @Description +'<br/></p><p><br/><a href="' + @Link + '"><img src="' + @ImageSrc + '" /><a></p>' +
'<font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#333333"><p><br />Thanks,<br />YourName</p><p>  </p><p> </p></font></td></tr><tr>
<td height="20" bgcolor="#ebebeb"><font face="Verdana, Arial, Helvetica, sans-serif" size="1"> All Rights Reserved</font></td></tr></table>'


EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SiteName',
@recipients = @EmailId,
@body_format ='HTML',
@body = @Body,
@subject = 'SqlServer HTML mail';


it's what i need, but email doesn't delivered.
Can u look up at my body:

set @ImgName = N'fl2'
Set @imageSrc = N'C:\Inetpub\wwwroot\Pils\Res\e-mails\fl2.jpg'
set @description = N'amazing'
set @userID = N'777'
set @link = N'http://192.46.35.2/Answers.aspx?Link=59661DEE-2DA4-4061-BC80-9F44D2CB4F61'

Declare @Body nvarchar(4000)
SET @Body = N'<table width="700" border="0" cellspacing="0" cellpadding="0"><tr><td><a href="' + 
@Link + N'"><img src="' + 
@ImgName + N'" border="0" width="270" height="146" /></a></td></tr><tr><td bgcolor="#00adef" height="5"></td>
</tr><tr><td><font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#333333"><p><br /><br />Dear <b><i> ' 
+ @UserId + 
N',</i></b></p><br/><p>Some sort of message. <br /></p></font>' +
N'write wt ever u want.<br /><br />sample text <a href="'+
@Link + N'" target="_blank">click here</a><br/><br/>' +
N'<p><font color="#333333" size="2" face="Verdana, Arial, Helvetica, sans-serif"><b><u> DEsc sample</u></b> </font><br/><br/>'
+ @Description + N'<br/></p><p><br/><a href="' 
+ @Link + N'"><img src="' 
+ @ImageSrc + N'" /><a></p>' +
N'<font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#333333"><p><br />Thanks,<br />YourName</p><p>  </p><p> </p></font></td></tr><tr>
<td height="20" bgcolor="#ebebeb"><font face="Verdana, Arial, Helvetica, sans-serif" size="1"> All Rights Reserved</font></td></tr></table>'


BTW body without image is delivered.

Thanks in advance!
Go to Top of Page

nathans
Aged Yak Warrior

938 Posts
Posted - 2008-06-05 : 10:58:42
because the recipient of the email does not have f12.jpg on their local c: drive. This is what i meant by embedding vs. referencing an image path on the net (as in my example I refer to a image hosted on sqlteam).

to embed you could try to encode the image as base64 and reference it directly in the img path... but i think most email clients correlate this technique with spam. i think youre better off referencing hosted images or simply attaching it to the email.

You may also be able to attach the image and then reference the attachment (by its cid) in the img path... search google for examples of both of these options.



Nathan Skerl
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-06-05 : 11:09:04
MIME64 encoder here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67831&SearchTerms=mime64


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

hornet
Yak Posting Veteran

96 Posts
Posted - 2008-06-11 : 03:58:04
quote:
Originally posted by nathans

because the recipient of the email does not have f12.jpg on their local c: drive. This is what i meant by embedding vs. referencing an image path on the net (as in my example I refer to a image hosted on sqlteam).

to embed you could try to encode the image as base64 and reference it directly in the img path... but i think most email clients correlate this technique with spam. i think youre better off referencing hosted images or simply attaching it to the email.

You may also be able to attach the image and then reference the attachment (by its cid) in the img path... search google for examples of both of these options.



Nathan Skerl


Nathan,

u mean if I need send HtmlEmail with pucture I ought to refer on picture?
So if I need to send email. I should next:

Set @imageSrc = N'http://localhost//News/Res/test.jpg'

declare @strMailBody NVARCHAR(4000)

SET @strMailBody = N'<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.01 Transitional//EN"" ""http://www.w3.org/TR/html4/loose.dtd""
<html
<head
<meta http-equiv=""Content-Type"" content=""text/html; charset=windows-1251""
<title></title
</head
<body style=""margin: 0;""
<table width=""600"" cellpadding=""0"" cellspacing=""0"" border=""0""
<tr
<td bgcolor=""#ffffff""><img src="' + @imageSrc + '"
..............................
</table
</body
</html'

EXEC @ResultId = msdb.dbo.sp_send_dbmail
		@body = @strMailBody,
                                 ...........................


Correct me please I wrong.

Thanks in advance!
Go to Top of Page

hornet
Yak Posting Veteran

96 Posts
Posted - 2008-06-11 : 04:51:32
Else I have 2 questions))

1. when I've opened an email where are pictures:
on web-server or on my Pc?
2. How to set universal format like UTF-8? On 1 inbox I've got normal text but on another not read symbols.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site