I've got the following T-SQL code below that's working from inside of SQL Server 2005. I'm looking to add a "reply-to" header so that any reply will go to the "reply-to" address, NOT the FROM address. Yes, I know about setting this up in Database Mail, but I'm looking to just add some code to my existing solution. I don't want to use Database Mail.
What I'm looking for is something similar to the following VB code, BUT I WANT A T-SQL VERSION...
Dim objMessage objMessage = Server.CreateObject("CDO.Message")
objMessage.Fields("urn:schemas:mailheader:reply-to").Value = "SOME_OTHER_EMAIL_NOT_FROM"
Here's the T-SQL code...
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value','smtp.mymailserver.com'
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").Value', 'username'
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value', 'password'
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, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
I don't understand the syntax to set headers using the T-SQL equivalents.