Send images using stored procedures in email

Question

0.00/5 (No votes)

See more:

, +

I have a stored procedure that is used to send mails to a list of people .

I have stored the image in varbinary(max) in my database in imagetable .

I am unable to send the email along with images in body .

Can anybody please help ?

What I have tried:

have tried adding the image in body like ..

'<a href=''someurl/newsletter.aspx/?id=' + CAST(@ID As varchar(20))+''+CAST(@THEIMAGE As varchar(max))+''+'''>Please click here to fill your missing details';

Cursors are as follows..

Begin
-- Declare Variable To Hold The Email Address Retrived
DECLARE @Email varchar(255)
DECLARE @Name varchar(255)
DECLARE @ID int
DECLARE @THEIMAGE varbinary(max)

--Declare Cursor to loop through the table data
DECLARE EmailCursor Cursor For
Select ID,email,name from demoemail

-- Open cursor for fetching data in email variable
open EmailCursor
Fetch Next From EmailCursor into @ID,@Email,@Name
while(@@FETCH_STATUS = 0)
Begin
DECLARE @out_desc varchar(1000),
@out_mesg varchar(10)

Posted 11-Jun-17 21:12pm

Abhi1 Kanobi

Updated 11-Jun-17 22:55pm

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kornfeld Eliyahu Peter · Accepted Answer · 2017-06-11T22:55:00

Solution 1

SQL has a system-stored procedure called sp_send_dbmail[^]. One of the parameters is @file_attachments...

Quote:
[ @file_attachments= ] 'file_attachments'
Is a semicolon-delimited list of file names to attach to the e-mail message. Files in the list must be specified as absolute paths. The attachments list is of type nvarchar(max). By default, Database Mail limits file attachments to 1 MB per file.

Posted 11-Jun-17 22:55pm

Kornfeld Eliyahu Peter

Comments

Abhi1 Kanobi 12-Jun-17 5:18am

Thanks but I am using a separate stored procedure , not the one given by sql server .
How can it be done in my case '?

Thanks

Kornfeld Eliyahu Peter 12-Jun-17 5:19am

What SP do you use?

Abhi1 Kanobi 12-Jun-17 5:31am

I have created a separate SP for the same .

ALTER PROCEDURE [dbo].[sp_send_mail]
@from varchar(500) ,
@to varchar(500) ,
@subject varchar(500),
@body varchar(4000) ,
@bodytype varchar(10),
@output_mesg varchar(10) output,
@output_desc varchar(1000) output
AS
DECLARE @imsg int
DECLARE @hr int
DECLARE @source varchar(255)
DECLARE @description varchar(500)

EXEC @hr = sp_oacreate 'cdo.message', @imsg out

EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").value','2'

--SMTP Server Details
EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value',
'------'

--SMTP user name
EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").value',
'----'

--Gmail Password
EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").value',
'----'

EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpusessl").value',
'---'

EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").value',
'465'

EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").value',
"1"

EXEC @hr = sp_oamethod @imsg, 'configuration.fields.update', null
EXEC @hr = sp_oasetproperty @imsg, 'to', @to
EXEC @hr = sp_oasetproperty @imsg, 'from', @from
EXEC @hr = sp_oasetproperty @imsg, 'subject', @subject

EXEC @hr = sp_oasetproperty @imsg, @bodytype, @body
EXEC @hr = sp_oamethod @imsg, 'send', null

SET @output_mesg = 'Success'

IF @hr <>0
SELECT @hr
BEGIN
EXEC @hr = sp_oageterrorinfo null, @source out, @description out
IF @hr = 0
BEGIN

set @output_desc = @description
END
ELSE
BEGIN
SET @output_desc = ' sp_oageterrorinfo failed'
END
IF not @output_desc is NULL
SET @output_mesg = 'Error'
END
EXEC @hr = sp_oadestroy @imsg

Kornfeld Eliyahu Peter 12-Jun-17 5:32am

And there is any specific reason you wrote that monster?
An in any case - there is no sign you set the 'attachments' property anywhere... so what have you except?

Abhi1 Kanobi 12-Jun-17 5:36am

I posted that monster just for clarity sake .. :)

I have tried to implement it like this but without success .
SET @IMAGEFILE = 'test.jpg'
SET @IMAGEPATH = 'C:\Users\----\Desktop\test.jpg'

--Declare Cursor to loop through the table data
DECLARE EmailCursor Cursor For
Select ID,email,name from demoemail

-- Open cursor for fetching data in email variable
open EmailCursor
Fetch Next From EmailCursor into @ID,@Email,@Name
while(@@FETCH_STATUS = 0)
Begin
DECLARE @out_desc varchar(1000),
@out_mesg varchar(10)

DECLARE @body varchar(max) =
--'<a href=''http://newsletter.qendidate.com/?id=' + CAST(@ID As varchar(20))+'''>Please click here to fill your missing details';
--'<a href=''someurl.com/?id=1''>Please click here to fill your missing details';
'<img src="cid:'+@IMAGEFILE+'"" width="235" height="70" border="0" alt="no image"><a href=''someurl.aspx/?id=' + CAST(@ID As varchar(20))+'''>Please click here to fill your missing details';

Kornfeld Eliyahu Peter 12-Jun-17 5:48am

As you are creating a CDO.Message object, it would be wise to read the documentation and set the proper properties for attachments instead of re-inventing the wheal (which in your case is more a square)...
(Hint - your file is on the servers C:, but the recipient do not have the file at his C:, but still src of img will point to C:)

Abhi1 Kanobi 12-Jun-17 5:51am

can the image path be some url and passing the same will work ?

Kornfeld Eliyahu Peter 12-Jun-17 5:55am

If your image can be accessed via URL (I would consider it as a big security hole), than yes - it would work that way...

Abhi1 Kanobi 12-Jun-17 5:59am

Yes , you are right . the way should not be followed .
What should be the right way?

Kornfeld Eliyahu Peter 12-Jun-17 6:03am

The truly right way is to use SQL's build in sp_send_dbmail... However if you have to stick to CDO, than read the documentation and use 'sp_oasetproperty' on the Attachments property...

Abhi1 Kanobi 12-Jun-17 6:12am

OK , but will it attach the file or will it embed it in the body ,
I need to have it embedded in the body

Kornfeld Eliyahu Peter 12-Jun-17 6:24am

Attach - also your solution would not embed the image... For that you have to turn it into a base64 string and add that as the source of the image...

Abhi1 Kanobi 12-Jun-17 6:53am

Ok , I am trying