send mail stored procedure reply issue

  • 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

  • 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?

  • anthony.green (10/30/2015)


    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?

    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