How to imbed an image into an email sent by dbmail

Question

Post reply

How to imbed an image into an email sent by dbmail

gbankos

Ten Centuries

Points: 1122
More actions
June 22, 2010 at 1:30 pm

#223022

Hello all and thank you in advance for your time and expertise.
I am sending out customer statements in email using dbmail on sql 2008. I'd like to put our corporate logo on top of the email, but I'm having difficulty doing it. I found an article on the web that showed me how to put html tags into documents (I'm not a programmer, in case you can't tell :-)) and I have this line in my sql statement:
<img src="\\waccounting\images\logo.gif" width="400" height="100" border="0"/>
When the email goes out, the image is blank on the email. Has anyone been able to put an image into their emails that get sent out by dbmail? If so, how?

Viewing 15 posts - 1 through 15 (of 40 total)

You must be logged in to reply to this topic. Login to reply

Jim Foster 80123 SSChasing Mays Points: 655 More actions · Answer 1

I don't have an answer as I've never done this, but you're referring to a local server as the image storage location.

Is this email only going to an intranet where all recipients would have access to this server & file?

Could it be an issue of needing to actually attach the image to the email?

Jim

R Michael SSCarpal Tunnel Points: 4211 More actions · Answer 2

Keep in mind that most email clients do not automatically load images in emails for privacy reasons. I expect that isn't the issue, but I wanted to mention it.

The problem is probably your file URI, which is incorrect.

Try this:

<img src="file://waccounting/images/logo.gif" width="400" height="100" border="0"/>

SQL guy and Houston Magician

gbankos Ten Centuries Points: 1122 More actions · Answer 3

This is going externally - it's going out to our customers. I want to put our logo at the top of the email. Right now, it's blank.

gbankos Ten Centuries Points: 1122 More actions · Answer 4

Robert Cary (6/22/2010)
Keep in mind that most email clients do not automatically load images in emails for privacy reasons. I expect that isn't the issue, but I wanted to mention it.
The problem is probably your file URI, which is incorrect.
Try this:
<img src="file://waccounting/images/logo.gif" width="400" height="100" border="0"/>

Robert,

Thanks for the input, but it still didn't work. You are right - outlook did block the image. But when I allowed the image to come through, it still showed blank.

Lowell SSC Guru Points: 323518 More actions · Answer 5

the secret is to use cid:attachedimage.gif for the source file.

here's a setup and working example:

I'm attaching the results of a query, and i copied the SQl server logo to my C:\ drive of my sql server.

declare @body1 varchar(4000)

set @body1 = '<head>

<title> Embedded Logo Example</title>

<meta name="Generator" content="EditPlus">

<meta name="Author" content="">

<meta name="Keywords" content="">

<meta name="Description" content="">

</head>

<body>

<table><tr><td valign="top" align="left">MyHeader</td></tr>

<tr><td valign="top" align="left"><img src="cid:sqlservercentral_logo.gif" width="235" height="70" border="0" alt=""></td></tr>

</table>

</body>'

EXEC msdb.dbo.sp_send_dbmail

@profile_name='MyDefault EmailProfile',

@recipients='lowell@somedomain.net',

@subject = 'SQl 2008 email test',

@body = @body1,

@body_format = 'HTML',

@query = 'SELECT top 3 * from sysobjects where xtype=''U''',

@query_result_header = 0,

@exclude_query_output = 1,

@append_query_error = 1,

@attach_query_result_as_file = 1,

@query_attachment_filename = 'results.txt',

@query_result_no_padding = 1,

@file_attachments = 'C:\sqlservercentral_logo.gif'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

gbankos Ten Centuries Points: 1122 More actions · Answer 6

Lowell (6/23/2010)
the secret is to use cid:attachedimage.gif for the source file.
here's a setup and working example:
I'm attaching the results of a query, and i copied the SQl server logo to my C:\ drive of my sql server.
declare @body1 varchar(4000)
set @body1 = '<head>
<title> Embedded Logo Example</title>
<meta name="Generator" content="EditPlus">
<meta name="Author" content="">
<meta name="Keywords" content="">
<meta name="Description" content="">
</head>
<body>
<table><tr><td valign="top" align="left">MyHeader</td></tr>
<tr><td valign="top" align="left"><img src="cid:sqlservercentral_logo.gif" width="235" height="70" border="0" alt=""></td></tr>
</table>
</body>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name='MyDefault EmailProfile',
@recipients='lowell@somedomain.net',
@subject = 'SQl 2008 email test',
@body = @body1,
@body_format = 'HTML',
@query = 'SELECT top 3 * from sysobjects where xtype=''U''',
@query_result_header = 0,
@exclude_query_output = 1,
@append_query_error = 1,
@attach_query_result_as_file = 1,
@query_attachment_filename = 'results.txt',
@query_result_no_padding = 1,
@file_attachments = 'C:\sqlservercentral_logo.gif'

That was it. My other problem was the @file_attachments parameter - I didn't have that. Thank you very much!

WayneS SSC Guru Points: 95460 More actions · Answer 7

Cool, I was hoping that there was a solution for this, it just sounded like a reasonable thing to do.

Good job Lowell!

(Now if only one could bookmark a forum post on this site...)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

AppSup_dba SSCrazy Points: 2215 More actions · Answer 8

Thanks to forum (again) , i have something new to offer to my client...

Cheers

SQLWannabe SSCrazy Points: 2842 More actions · Answer 9

I will have an upcoming use of printing a logo in my emails out to clients. I stumbled onto this forum looking for the syntax to use sp_send_dbmail without setting up the Database Mail client (like what credentials do I use to pass to the SMTP server?). This is the cart before the horse, but you guys are great and I won't have any problem with how my email will appear. Another satisfied customer of SSC!!;-)

msalman 42552 SSC Enthusiast Points: 144 More actions · Answer 10

hey, attachments work fine but when I see email in web based client like gmail attach picture not showing in right place they way it's show in outlook. i've multi images in html email.

Lowell SSC Guru Points: 323518 More actions · Answer 11

right click on the email message in gmail and view source;

gmail has a lot of anti spam stuff amd mail filtering going on, so i would not be surprised if you have to click something to "show images", or place the sender on a safe list or something, as they are probably modifying the html to avoid spam and stuff.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

msalman 42552 SSC Enthusiast Points: 144 More actions · Answer 12

thanks for quick response.

in outlook logo show just after 5 row then remaning row but in gmail it show 5 row (MyHeader) then box (missing image) then remanning row at the bottom i see the logo.

i just want to show logo just after 5th row, email drop right in my inbox and I don't have to click to download images.

<title> Embedded Logo Example</title>

</head>

<body>

<table><tr><td valign="top" align="left">MyHeader1</td></tr>

<table><tr><td valign="top" align="left">MyHeader2</td></tr>

<table><tr><td valign="top" align="left">MyHeader3</td></tr>

<table><tr><td valign="top" align="left">MyHeader4</td></tr>

<table><tr><td valign="top" align="left">MyHeader5</td></tr>

<table>

</table>

<table><tr><td valign="top" align="left">MyHeader</td></tr>

</table>

</body>'

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 13

Lowell (6/23/2010)
the secret is to use cid:attachedimage.gif for the source file.
here's a setup and working example:
I'm attaching the results of a query, and i copied the SQl server logo to my C:\ drive of my sql server.
declare @body1 varchar(4000)
set @body1 = '<head>
<title> Embedded Logo Example</title>
<meta name="Generator" content="EditPlus">
<meta name="Author" content="">
<meta name="Keywords" content="">
<meta name="Description" content="">
</head>
<body>
<table><tr><td valign="top" align="left">MyHeader</td></tr>
<tr><td valign="top" align="left"><img src="cid:sqlservercentral_logo.gif" width="235" height="70" border="0" alt=""></td></tr>
</table>
</body>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name='MyDefault EmailProfile',
@recipients='lowell@somedomain.net',
@subject = 'SQl 2008 email test',
@body = @body1,
@body_format = 'HTML',
@query = 'SELECT top 3 * from sysobjects where xtype=''U''',
@query_result_header = 0,
@exclude_query_output = 1,
@append_query_error = 1,
@attach_query_result_as_file = 1,
@query_attachment_filename = 'results.txt',
@query_result_no_padding = 1,
@file_attachments = 'C:\sqlservercentral_logo.gif'

Hi Lowell,

I know this is a 4 year old post but I have to ask...

I absolutely understand the need for the image to be attached to the email. It just makes sense. How else would the email know where the image was? So, no problem there.

My questions are, how did you figure out that "<img src=" needed the "cid:" annotation and what does "cid" stand for? That was a really cool bit of research on your part and, for the sake of future learning/research, I wanted to know how you figured that out. Even W3Schools.com doesn't even mention it.

Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 14

Having good background with C# maybe I will try to clear that.

Cid is called ContentIdentifier.So when an image needs to be referenced in a mail basically it can be done in two ways.Either directly link the src of the image in the message HTML<IMG SRC=http://www.sqlservercentral.com/Resources/Images/sqlservercentral_logo.gif/> or directly embed it into the mail which becomes available in the email header and becomes part of the message itself.

So tomorrow if the image is unavailable on location http://www.sqlservercentral.com/Resources/Images/sqlservercentral_logo.gif but you have embedded it as a cid the mail would render(display) the image as it is already embedded in the email header and is part of the message.But if its just a simple url reference to the image location through a src tag the image wont be rendered in the mail if the image becomes unavailable on the url.

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server