SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 How to send email using HTML style with picture?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hornet
Yak Posting Veteran

96 Posts

Posted - 06/05/2008 :  00:53:59  Show Profile  Reply with Quote
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

USA
938 Posts

Posted - 06/05/2008 :  01:21:14  Show Profile  Reply with Quote
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

Edited by - nathans on 06/05/2008 01:21:31
Go to Top of Page

Vinnie881
Flowing Fount of Yak Knowledge

USA
1222 Posts

Posted - 06/05/2008 :  01:22:50  Show Profile  Reply with Quote
This is a good article on Email and sql

http://classicasp.aspfaq.com/email/how-do-i-send-e-mail-from-sql-server.html

Edited by - Vinnie881 on 06/05/2008 01:25:36
Go to Top of Page

hornet
Yak Posting Veteran

96 Posts

Posted - 06/05/2008 :  01:39:20  Show Profile  Reply with Quote
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

India
38 Posts

Posted - 06/05/2008 :  01:50:21  Show Profile  Reply with Quote
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

USA
938 Posts

Posted - 06/05/2008 :  01:58:01  Show Profile  Reply with Quote
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

Edited by - nathans on 06/05/2008 02:01:40
Go to Top of Page

hornet
Yak Posting Veteran

96 Posts

Posted - 06/05/2008 :  06:34:07  Show Profile  Reply with Quote
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

USA
938 Posts

Posted - 06/05/2008 :  10:58:42  Show Profile  Reply with Quote
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

Edited by - nathans on 06/05/2008 11:04:08
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30218 Posts

Posted - 06/05/2008 :  11:09:04  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 06/11/2008 :  03:58:04  Show Profile  Reply with Quote
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 - 06/11/2008 :  04:51:32  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000