How to imbed an image into an email sent by dbmail
-
Apologies,
So currently there is an infopath which is submitted to a sql table.
I have built a trigger to send an email based on certain conditions being met.
The email needs to be sent to the address filled out in the infopath. I get the error message in infopath when testing that says "Procedure or function has too many arguments specified".
Additionally as I am not well versed on how to code HTML, what I was looking for is a solution to have images embedded in an email, as well as the payment confirmation details-specific to the customer.
I know I may be asking for the moon but I am at a wits end trying to find a solution.
Thanks in advance again
-
you should start a new thread to get a bit better help;
however, here's a decent framework to start with.
the email portion is the easiest part, really.
the only thing you need to do right now is the create html, complete with data and images. use external links (http://yourcompanysite.com/logo.png) until you have everything working; then you chan change them to cid:
I've added a couple of fully qualified images in this example, so you can see it's results, and obfuscated the server names. this will work without any modifications on any server, and produce valid html.
look this example over; it's broken up into sections that have to be customized: The @TableHeader has to be the column definitions that match your data columns.
since it's html, you should know
Declare @HTMLBody varchar(max),
@PageHead varchar(max),
@TableHead varchar(max),
@TableTail varchar(max)
Set NoCount On;
Set @TableTail = '</table><div><img src="http://www.sqlservercentral.com/Resources/Images/sqlservercentral_logo.gif" alt="sql server central" /></div></body></html>';
Set @PageHead = '<html>'
+ '<head>'
+ '<title> Query Results </title>'
+ '<meta name="Generator" content="Mud Table and a sharp stick.">'
+ ' <style TYPE="text/css"> '
+ ' <!-- '
+ ' BODY { background-color: #FFFFFF;font-family: "Verdana","Helvetica","MS Sans Serif",verdana; font-size: x-small;}'
+ ' .mytitle { font-family: "Verdana","Helvetica","MS Sans Serif",verdana; font-size: medium; font-weight: bold; font-style: italic;}'
+ ' H3 { font-family: "Verdana","Helvetica","MS Sans Serif",verdana; font-size: medium; font-style: italic;font-weight: bold; text-decoration: underline;}'
+ ' .mysection{ font-family: "Verdana","Helvetica","MS Sans Serif",verdana; font-size: large; font-weight: bold; text-decoration: underline;}'
+ ' .mylocation{ font-family: "Verdana","Helvetica","MS Sans Serif",verdana; font-size: x-small; font-weight: bold; text-decoration: underline;}'
+ ' .jump{ font-family: "Verdana","Helvetica","MS Sans Serif",verdana; font-size: x-small; font-weight: normal; text-decoration: none;font-style: italic;}'
+ ' ul.master { font-family: "Verdana","Helvetica","MS Sans Serif",verdana; font-size: x-small; font-weight: normal; text-decoration: none;list-style-type: square}'
+ ' ul.slave { font-family: "Verdana","Helvetica","MS Sans Serif",verdana; font-size: x-small; font-weight: normal; text-decoration: none;list-style-type: circle}'
+ ' table {width: 80%;border-style: solid;border-width: 1px;}'
+ ' thead { font-family: "Verdana","Helvetica","MS Sans Serif",verdana; font-size: small; font-weight: bold; background-color: #CCCCCC; vertical-align: top;border-style: solid;border-width: 1px;}'
+ ' th { font-family: "Verdana","Helvetica","MS Sans Serif",verdana; font-size: x-small; vertical-align: top;border-style: solid;border-width: 1px; font-weight: bold;}'
+ ' td { font-family: "Verdana","Helvetica","MS Sans Serif",verdana; font-size: x-small; vertical-align: top;border-style: solid;border-width: 1px;}'
+ ' --></STYLE>'
+ '</head>'
+ '<body>';
Set @TableHead = '<div><img src="http://www.faceofit.com/wp-content/uploads/2016/02/sql2016icon.jpg" alt = "SQL2016" /></div><br />'
+'<div>'
+' A long paragraph or description can optionally be placed ina div, a table summary, or a rowspan in the table'
+'</div>'
+'<table cellpadding=0 cellspacing=0 border=0>'
+ '<tr bgcolor=#FFEFD8>'
+ '<th>Server Name</th>'
+ '<th>Product</th>'
+ '<th>Provider</th>'
+ '<th>Data Source</th>'
+ '<th>Is Linked?</th></tr>';
Select @HTMLBody = (Select Row_Number() Over(Order By is_linked, name) % 2 As [TRRow],
UPPER(RIGHT(master.dbo.fn_varbintohexstr(hashbytes('MD5',name)),8) ) As
,
product As
,
provider As
,
UPPER(RIGHT(master.dbo.fn_varbintohexstr(hashbytes('MD5',data_source)),8) ) As
,
is_linked As
From sys.servers
Order By is_linked, name
For XML raw('tr'), Elements)
-- Replace the entity codes and row numbers
Set @HTMLBody = Replace(@HTMLBody, '_x0020_', space(1))
Set @HTMLBody = Replace(@HTMLBody, '_x003D_', '=')
Set @HTMLBody = Replace(@HTMLBody, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
Set @HTMLBody = Replace(@HTMLBody, '<TRRow>0</TRRow>', '')
Select @HTMLBody = @PageHead + @TableHead + @HTMLBody + @TableTail
-- return output
Select @HTMLBody
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! -
This just isn't working for me at all.
I have a folder on my Server's C: drive that contains two files: Dashboard.pdf and Dashboard.jpg. These files are overwritten on a daily basis.
ALL I want to do is use database mail to send the Dashboard.PDF file as an attachment and embed Dashboard.jpg in the body of the email. That's it.The email is being sent with the files attached perfectly.
Here's the code . . .
DECLARE @body1 varchar(4000)
set @body1 = '<head>
<title> Dashboard </title>
<meta name="Author" content="">
<meta name="Keywords" content="">
<meta name="Description" content="">
</head>
<body>
<table><tr><td valign="top" align="left"><img src="cid:Dashboard.jpg" border="0" alt=""></td></tr>
</table>
</body>'EXEC msdb.dbo.sp_send_dbmail
@recipients=N'klgtest@gmail.com',
@body= @body1,
@body_format = 'HTML',
@subject = 'Dashboard',
@profile_name = 'EmailReports',
@file_attachments ='c:\Dashboard.jpg;c:\Dashboard.pdf'Both attachments are sent and received but the jpg won't embed inside the email body. The size of the file doesn't seem to be the problem. I've tried some pairs of small images and nothing comes out in the body of the email.
I hope you or someone here can help.
Thanks!
Karen -
first, the root of the C: drive, or any drive that has %ProgramFiles% on it is protected, so only users with local admin privilesges can access it.
move your files to a subfolder, ie C:\Data, and make sure the database service account has access to that subfolder.(select * from sys.dm_server_services).
then rebuild your email.next, actually examine the header of the email. two things could be going on:
are the attachments actually there and not displayed? many/most email clients suppress images unless they come from a trusted source. see if the attachments are there but not displayed.
If the attachments were not attached, I would think it was because of the protected status of the root of the C: drive.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! - Karen Grube - Tuesday, August 14, 2018 5:27 PM
Hey Karen,
I just ran this script and it works for me.
DECLARE @body1 varchar(4000)
set @body1 = '<head>
<title> Dashboard </title>
<meta name="Author" content="">
<meta name="Keywords" content="">
<meta name="Description" content="">
</head>
<body>
<table><tr><td valign="top" align="left"><img src="cid:JpgFile.jpg" border="0" alt=""></td></tr>
</table>
</body>'EXEC msdb.dbo.sp_send_dbmail
@recipients=N'xxx@yz.com',
@body= @body1,
@body_format = 'HTML',
@subject = 'Dashboard',
@profile_name = 'DBA',
@file_attachments ='C:\temp\JpgFile.JPG;C:\temp\PdfFile.pdf'Can you put your image and pdf in a subfolders and give it a shot?
Cheers
- Lowell - Tuesday, August 14, 2018 8:06 PM
Valid point. If the client or SMTP server suppresses the images. It may appear as a cross icon on the email.
Cheers
-
To begin with, both images arrive with the email as attachments. Both readily appear as thumbnail images in gmail.
What I see in the body of the email itself is either a large empty space the size of the attachment or nothing.
So, permissions doesn't seem to be the issue at all. But there could be something wrong with the Head area of the HTML. I'll take a look at that.
I'm half thinking of turning this into an SSIS mail task instead, but we'll see.
I'll keep working on this and let you know.
THANK YOU!!! -
No, again, both attachments are there, but there is no image in the body of the email. I'm not sure why.
I'm using the Gmail smtp server. I wonder if that could be the problem. We don't have an smtp server set up on the same server as SQL. All I'm doing is pointing to gmail's outgoing SMTP port and using it to send SSRS emails, SQL Job notifications, and now this attempted email. As you can tell, the email is getting out there, but without the image in the body of the email.Any other suggestions?
Thanks,
Karen -
Karen, Gmail and Thunderbird are both examples of clients suppressing images by default.
https://www.wired.com/2013/12/turn-gmail-auto-image-loading-off/ mentions where they changed the default behavior a while back.
I think they have settings in place, where if the image comes from a known content delivery network(cdn), they might allow images.i found some generic steps to enable images, can you test them?:
can you pick up your mail with a client, and not the web site?To display images in Gmail:
- Click the gear icon in the top right corner of the page.
- Select Settings.
- On the General tab, click the "Always display external images" radio button.
- Scroll to the bottom of the page and click Save Changes.
Images Not Displaying in an Email Client
https://knowledgebase.constantcontact.com/.../5554-images-not-displaying-in-an-email-c...Search for: How can I see images in my Gmail?
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! -
I send and receive inline images all the time in both email accounts; yahoo and gmail. That's not what's happening. Somehow, I get the feeling that there may be something on my server or with Google's smtp server that isn't letting the images go out. I may try base64 encoding the image. I'm not sure how to use that in the code above, but I may try.
Just for the heck of it, I looked at one of the test emails I sent in Yahoo Mail on my smartphone. I saw the "Show Inline images" button and clicked on it, but nothing appeared. It's as though the mail client thinks it has an image, but either there really is no image or it can't display it.
I just tried another test: I set up an SSRS subscription to email me a small report as MHTML and the report did not display in the email client. It's definitely supposed to. SSRS uses the same email profile with the gmail SMTP server.
Any suggestions? Any ideas as to what the problem may be?
-
Okay, I tried sending a very small scheduled SSRS report as MHTML from our server and the report didn't display in the email client. THEN I looked at the RAW email and saw that it was being sent from our server using an old ip address set up for ipv4. I'm working with our server hosting provider to correct that to our newer ip address and we'll see if it resolves the problem. WOW! I'll let everyone know.
Viewing 11 posts - 31 through 40 (of 40 total)
You must be logged in to reply to this topic. Login to reply