October 29, 2015 at 3:46 pm
I have a stored procedure to send email and it works. The issue is when the recipient clicks on Reply, it shows their name in the TO field instead of the sender's address. I sent a test email to my Exchange person and he said its not an Exchange issue but rather maybe a setting in the stored procedure. Is it something with the CDO configurations?
------------------------------------------------------------------------------------------
-- Stored Procedure "dbo. send_smtp_mail"
------------------------------------------------------------------------------------------
if exists( select * from dbo.sysobjects where id = object_id( '[dbo].[ send_smtp_mail]'))
drop procedure [dbo].[send_smtp_mail]
go
create procedure [dbo].[send_smtp_mail]
@subject varchar(255),
@body varchar(4000),
@from varchar(255),
@to varchar(255),
@cc varchar(255),
@bcc varchar(255),
@server varchar(255),
@attachment varchar(255) = null
as begin
set nocount on
set transaction isolation level read uncommitted
declare @message_id int
declare @return_code int
declare @source varchar(255)
declare @description varchar(512)
-- Open a connection to the SMTP server.
exec @return_code = sp_OACreate 'CDO.Message', @message_id out
exec @return_code = sp_OASetProperty @message_id, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value', '2'
exec @return_code = sp_OASetProperty @message_id, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @server
exec @return_code = sp_OAMethod @message_id, 'Configuration.Fields.Update', null
-- Define the message.
exec @return_code = sp_OASetProperty @message_id, 'To', @to
exec @return_code = sp_OASetProperty @message_id, 'From', @from
exec @return_code = sp_OASetProperty @message_id, 'Subject', @subject
exec @return_code = sp_OASetProperty @message_id, 'CC', @cc
exec @return_code = sp_OASetProperty @message_id, 'BCC', @bcc
exec @return_code = sp_OASetProperty @message_id, 'ReplyTo', @to
exec @return_code = sp_OASetProperty @message_id, 'TextBody', @body
-- Add attachment if it exists. -- code for multiple attachments
IF @attachment IS NOT NULL AND LEN(@attachment) > 0 BEGIN
Declare @files table(fileid int identity(1,1),[file] varchar(1000))
Declare @file varchar(1000)
Declare @filecount int ; set @filecount=0
Declare @counter int ; set @counter = 1
DECLARE @outVar INT
SET @outVar = NULL
INSERT @files SELECT value FROM V5PROD..fn_split(@attachment,',')
SELECT @filecount=@@ROWCOUNT
WHILE @counter<(@filecount+1)
BEGIN
SELECT @file = [file]
FROM @files
WHERE fileid=@counter
EXEC @return_code = sp_OAMethod @message_id, 'AddAttachment',@outVar OUT, @file
SET @counter=@counter+1
END
END
-- Send the mail.
exec @return_code = sp_OAMethod @message_id, 'Send', null
-- Did we take an error?
if @return_code != 0
begin
-- Yes. Display error code.
print 'Error ' + convert(varchar, @return_code)
-- Get the error message.
exec @return_code = sp_OAGetErrorInfo null, @source out, @description out
if @return_code = 0
begin
-- Display the error message.
print '[' + isnull(@source, '') + ']' + isnull(@description, '')
end
else
begin
print 'sp_OAGetErrorInfo failed.'
return
end
end
exec @return_code = sp_OADestroy @message_id
end
go
October 30, 2015 at 2:51 am
Your ReplyTo is @To, that needs to be @From
Also not a fan of sp_OA calls, they use memory outside of the SQL allocation, have you looked at converting this to database mail instead?
October 30, 2015 at 8:38 am
anthony.green (10/30/2015)
Your ReplyTo is @To, that needs to be @FromAlso not a fan of sp_OA calls, they use memory outside of the SQL allocation, have you looked at converting this to database mail instead?
Omg I can't believe I overlooked that. Thank you!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply