Hi Everybody,
This is my first post in the forum.
Can someone give me an example how to send an email with an attachment in a SP ?? I have been searching and i haven't found anything useful.
I have been trying several methods and i'm stuck
Right now, I'm using the next SP:
DROP PROCEDURE ST_S_SendMail
GO
CREATE PROCEDURE ST_S_SendMail
(@.FROM NVARCHAR(255),
@.TO NVARCHAR(255),
@.SUBJECT NVARCHAR(255),
@.BODY NVARCHAR(4000))
AS
DECLARE @.Object int
DECLARE @.Hresult int
DECLARE @.ErrorSource varchar (255)
DECLARE @.ErrorDesc varchar (255)
DECLARE @.V_BODY NVARCHAR(4000)
DECLARE @.hr int
DECLARE @.src varchar(255), @.desc varchar(255)
EXEC @.Hresult = sp_OACreate 'CDONTS.NewMail', @.Object OUT
IF @.Hresult = 0 begin
--SET SOME PROPERTIES
SET @.V_BODY = '' + @.BODY
EXEC @.Hresult = sp_OASetProperty @.Object, 'From', @.FROM
EXEC @.Hresult = sp_OASetProperty @.Object, 'To', @.TO
EXEC @.Hresult = sp_OASetProperty @.Object, 'Subject', @.SUBJECT
EXEC @.Hresult = sp_OASetProperty @.Object, 'Body', @.V_BODY
EXEC @.Hresult = sp_OASetProperty @.Object, 'MailFormat', 0
--CALL ATTACHMENT METHOD
EXEC @.Hresult = sp_OAMethod @.Object, 'Attachfile', 'C:\Inetpub\wwwroot\desarrollo\PolizasCP\PolC20031 103.txt', 'poliza.txt', 1
--CALL SEND METHOD
EXEC @.Hresult = sp_OAMethod @.Object, 'Send', NULL
--DESTROY THE OBJECT
EXEC @.Hresult = sp_OADestroy @.Object
end
else
begin
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
end
Thanks in advance for your helpHave you tried the following code for sending the attachment mate?
-- Check for multiple attachments separated by a semi-colon ';'.
If @.vcAttachments is not null
Begin
If right(@.vcAttachments,1) <> ';'
Select @.vcAttachments = @.vcAttachments + '; '
Select @.iPos = CharIndex(';', @.vcAttachments, 1)
While @.iPos > 0
Begin
Select @.vcAttachment = ltrim(rtrim(substring(@.vcAttachments, 1, @.iPos -1)))
Select @.vcAttachments = substring(@.vcAttachments, @.iPos + 1, Len(@.vcAttachments)-@.iPos)
EXEC @.iHr = sp_OAMethod @.iMessageObjId, 'AddAttachment', @.iRtn Out, @.vcAttachment
IF @.iHr <> 0
Begin
EXEC sp_OAGetErrorInfo @.iMessageObjId, @.vcErrSource Out, @.vcErrDescription Out
Select @.vcBody = @.vcBody + char(13) + char(10) + char(13) + char(10) +
char(13) + char(10) + 'Error adding attachment: ' +
char(13) + char(10) + @.vcErrSource + char(13) + char(10) +
@.vcAttachment
End
Select @.iPos = CharIndex(';', @.vcAttachments, 1)
End
End
----------
you can find more info about this at www.sqlservercentral.com
Hope this helps...
have a good one|||Originally posted by saulo70
Hi Everybody,
This is my first post in the forum.
Can someone give me an example how to send an email with an attachment in a SP ?? I have been searching and i haven't found anything useful.
I have been trying several methods and i'm stuck
Right now, I'm using the next SP:
DROP PROCEDURE ST_S_SendMail
GO
CREATE PROCEDURE ST_S_SendMail
(@.FROM NVARCHAR(255),
@.TO NVARCHAR(255),
@.SUBJECT NVARCHAR(255),
@.BODY NVARCHAR(4000))
AS
DECLARE @.Object int
DECLARE @.Hresult int
DECLARE @.ErrorSource varchar (255)
DECLARE @.ErrorDesc varchar (255)
DECLARE @.V_BODY NVARCHAR(4000)
DECLARE @.hr int
DECLARE @.src varchar(255), @.desc varchar(255)
EXEC @.Hresult = sp_OACreate 'CDONTS.NewMail', @.Object OUT
IF @.Hresult = 0 begin
--SET SOME PROPERTIES
SET @.V_BODY = '' + @.BODY
EXEC @.Hresult = sp_OASetProperty @.Object, 'From', @.FROM
EXEC @.Hresult = sp_OASetProperty @.Object, 'To', @.TO
EXEC @.Hresult = sp_OASetProperty @.Object, 'Subject', @.SUBJECT
EXEC @.Hresult = sp_OASetProperty @.Object, 'Body', @.V_BODY
EXEC @.Hresult = sp_OASetProperty @.Object, 'MailFormat', 0
--CALL ATTACHMENT METHOD
EXEC @.Hresult = sp_OAMethod @.Object, 'Attachfile', 'C:\Inetpub\wwwroot\desarrollo\PolizasCP\PolC20031 103.txt', 'poliza.txt', 1
--CALL SEND METHOD
EXEC @.Hresult = sp_OAMethod @.Object, 'Send', NULL
--DESTROY THE OBJECT
EXEC @.Hresult = sp_OADestroy @.Object
end
else
begin
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
end
Thanks in advance for your help
Here;s a sp which will make your life easier ...
CREATE PROCEDURE [dbo].[sp_CDOMail]
@.To varchar(1000),
@.From varchar(100),
@.Subject varchar(100),
@.Body varchar(4000),
@.CC varchar(100) = null,
@.BCC varchar(100) = null,
@.FilePath varchar(100) = Null, --inlcude last slash
@.FileName varchar(100) = Null
/************************************************** *******************
This stored procedure takes the above parameters and sends an e-mail.
All of the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
Reference to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp
DATE By Descript
------ ------ ----------------
11/01/03 SP --Modified to Send Attachments from the original query designed by Microsoft.
************************************************** *********************/
AS
Declare @.iMsg int
Declare @.hr int
Declare @.source varchar(255)
Declare @.description varchar(500)
Declare @.output varchar(1000)
Declare @.attachment varchar(200)
set @.attachment = @.FilePath + @.FileName
--************* Create the CDO.Message Object ************************
EXEC @.hr = sp_OACreate 'CDO.Message', @.iMsg OUT
--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
EXEC @.hr = sp_OASetProperty @.iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @.hr = sp_OASetProperty @.iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'Mozart'
-- Save the configurations to the message object.
EXEC @.hr = sp_OAMethod @.iMsg, 'Configuration.Fields.Update', null
-- Set the e-mail parameters.
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, 'CC', @.CC
EXEC @.hr = sp_OASetProperty @.iMsg, 'BCC', @.BCC
--Add Attachment if exists.
if @.Attachment <> ''
EXEC @.hr = sp_OAMethod @.iMsg, 'AddAttachment',null, @.Attachment , @.FileName
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @.hr = sp_OASetProperty @.iMsg, 'TextBody', @.Body
EXEC @.hr = sp_OAMethod @.iMsg, 'Send', NULL
-- Sample error handling.
IF @.hr <>0
select @.hr
BEGIN
EXEC @.hr = sp_OAGetErrorInfo NULL, @.source OUT, @.description OUT
IF @.hr = 0
BEGIN
SELECT @.output = ' Source: ' + @.source
SELECT @.output = @.output + '
Description: ' + @.description
Select @.output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you need to.
-- Clean up the objects created.
EXEC @.hr = sp_OADestroy @.iMsg
GO
IF you still have problems email me @. shaileshpatangay@.hotmail.com
--shailesh|||Thanks a lot for your help
It's working now
