How to imbed an image into an email sent by dbmail

Question

How to imbed an image into an email sent by dbmail

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

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

Sachin Nandanwar (6/22/2014)
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.

Thanks, Sachin. I appreciate your time.

I gleened what it does from Lowell's post and a couple of dozen posts that I Yabingooglehoo'd but I've found nothing as an actual reference that lists it as an attribute of "src". You would expect such a thing to be included in an HTML reference (it is, after all, an HTML attribute) somewhere but I've not been able to find any such thing, yet.

Heh... I'm reminded of how I felt when I first started SQL and wanted to know how to delete dupes. 😀

--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 2

Thanks, Sachin. I appreciate your time.

My pleasure Jeff and a great honor.

I gleened what it does from Lowell's post and a couple of dozen posts that I Yabingooglehoo'd but I've found nothing as an actual reference that lists it as an attribute of "src". You would expect such a thing to be included in an HTML reference (it is, after all, an HTML attribute) somewhere but I've not been able to find any such thing, yet.
Heh... I'm reminded of how I felt when I first started SQL and wanted to know how to delete dupes. 😀

Though you can use it as a part of HTML tag "cid" does not qualify as an inbuilt HTML tag or a property.It basically is a property of MIME message types which can be embedded in HTML messages sent over http.This is the official definition of "cid" by the W3 consortium

http://www.w3.org/Addressing/URL/4_1_Cid.html#z19

MIME : http://en.wikipedia.org/wiki/MIME

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

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

Excellent, Sachin! Thanks for the great info!

How in the world a newbie would make the connection in his/her mind that you need to find and embed a MIME attribute in HTML to send an image in an email from SQL Server is totally beyond me. Like you said, you do have to have some esoteric knowledge even to be able to do a search.

Thanks for helping out someone who's never programmed in any form of C or Java. I really appreciate it.

Heh... to coin a phrase... "I know what I don't know but I'll be damned if I can find it". 😀

--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)

mister.magoo SSC-Forever Points: 47174 More actions · Answer 4

Jeff Moden (6/22/2014)
How in the world a newbie would make the connection in his/her mind that you need to find and embed a MIME attribute in HTML to send an image in an email from SQL Server is totally beyond me.

To put your mind at rest about "how a newbie would..", the way I found out about this use of "cid:" was this:

1. Hey, can we add images to this email, but in the body, you know - not as an attachment.

2. well, yeah - I've seen emails with pictures in them like that.

3. Open outlook, find an email with a picture embedded in it.

4. right click in the body of that email, "view source"

5. find the image tag "<img..."

6. look at the definition of that image tag and say "hey what's this 'cid' thing"

7. Google cid

😛

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

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

Thanks, Magoo... That's pretty close to what I would have ended up doing had someone not posted the solution but, my word, I don't believe there are a whole lot of newbies that would have figured out to look at the source of such an email. Even fewer would have tried to figure out what it actually was. They would have just ended up using it without knowing just exactly what it was.

Heh... it's like trying to figure out how to quickly convert a table to HTML. That's actually in Books Online but good luck trying to find it on purpose.

I'm begining to think this job is 30% skill, 30% knowledge, and 40% luck. 😀

--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)

mister.magoo SSC-Forever Points: 47174 More actions · Answer 6

Jeff Moden (6/23/2014)
Thanks, Magoo... That's pretty close to what I would have ended up doing had someone not posted the solution but, my word, I don't believe there are a whole lot of newbies that would have figured out to look at the source of such an email. Even fewer would have tried to figure out what it actually was. They would have just ended up using it without knowing just exactly what it was.
Heh... it's like trying to figure out how to quickly convert a table to HTML. That's actually in Books Online but good luck trying to find it on purpose.
I'm begining to think this job is 30% skill, 30% knowledge, and 40% luck. 😀

Hmmm, I think I'd better check but I'm sure my ratio tends more towards 20,20,60

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

kailashIndia SSC Eights! Points: 935 More actions · Answer 7

sir,

embedded image not auto open in blackberry or i-phone like : cid:attachedimage.gif working fine in gmail and outlook.

Please suggest.

kailashIndia SSC Eights! Points: 935 More actions · Answer 8

sir,

embedded image not open in blackberry or i-phone like : cid:attachedimage.gif working fine in gmail and outlook.

Please suggest.

Lowell SSC Guru Points: 323518 More actions · Answer 9

a feature of almost all email programs is to not show images without extra user interaction, or not show from untrusted sources. check your application, and see if you can trust the sender email address.

the show images is not limited ot external images, it applies to all images referenced int eh email, to avoid spammers including a link to their unique-to-you images so they validate your meail or viewing of what they sent.

you cannot control whether it displays or not, only include the images.

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!

rperetz SSC Journeyman Points: 79 More actions · Answer 10

rperetz

SSC Journeyman

Points: 79

October 5, 2016 at 8:33 am

#1904926

This does not work for Gmail.

Lowell SSC Guru Points: 323518 More actions · Answer 11

rperetz (10/5/2016)
This does not work for Gmail.

Gmail, like many other client applications, have an option to prevent images from showing unless the sender is trusted. I had to do that for Thunderbird.

The html specification is what it is. Gmail can show the images, but you have to change your settings for that source domain.

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!

ExhibitA Ten Centuries Points: 1074 More actions · Answer 12

Hello Guys,

Thanks for providing the code to include a image withing the emails.

I am fairly new to SQL and how to use a email functions within SQL server, not to mention using HTML in conjunction with sql server syntax.

With that said, I am working on a project where I need to send a client an email based on entries that are made in to a sql table. However I need to include the company header on the top of the email and some logos at the bottom.

I understand the code provided earlier addresses this, but I'm struggling to also include the text I wish to include along with the images. I have tried using the code provided and an earlier coded to include the text as well as the images but I haven't found a solution. Can someone break it down for me please?

Thanks in advance

Lowell SSC Guru Points: 323518 More actions · Answer 13

Reconsider the tool to use for your project;

This post started out with "how to include an image in sp_send_dbmail".

certainly possible, but is the right tool for your specific question the same ?:

it seems like you need to do customized email to customer....most likely with data from the database, right?

SSRS is probably a better tool for that...it allows you to visualize the report, customize the reports look and feel, dynamically create the data, and even schedule a subscription to send emails to your customers. custom images? copy and paste. it cannot get simpler. custom text? create textbox for it, maybe in the group header. done.

doing the same work in TSQL is absolutely possible, but the solution is all scripting and doing a lot of neat techniques to generate HTML with images descriptions and such.

it takes a lot more effort to make something look like the image below in tSQL than it would be in SSRS:

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!

ExhibitA Ten Centuries Points: 1074 More actions · Answer 14

Thanks for the comprehensive reply.

After much deliberation, it has been brought to my attention that the organisation I work for will not allow access to SSRS so I am stuck with SQL server MS or any other solution anyone can come up with.

In case I didn't make it clear enough in my earlier post, what I am trying to do is:

Send a customer an email with payment confirmation details that includes the company header at the top of the email(image) and logos at the bottom. There will be text confirming the payment and the personalized details will hopefully pull from an internal form that is filled out (info path) and submitted.

I am assuming I will have to build a new stored procedure to send these emails and the current sp_send_dbmail doesn't allow for images. I have, in the past, send basic internal emails once stored procedures have run, with the emails serving as a notification. I was hoping to build on this, and create a trigger to send this personalized email upon this form being submitted.

Again thanks in advance and had it not been for my workplace not allowing me to use SSRS the earlier solution would have worked

mister.magoo SSC-Forever Points: 47174 More actions · Answer 15

ExhibitA (12/20/2016)
...
Send a customer an email with payment confirmation details that includes the company header at the top of the email(image) and logos at the bottom. There will be text confirming the payment and the personalized details will hopefully pull from an internal form that is filled out (info path) and submitted.
...

What's not clear is what you have tried that is not working.

It's really quite simple to break down...you need: