January 3, 2006 at 3:02 pm
We have an email procedure that I'd like to specify the FROM address based on the UserId of the client logged in. This is what we have so far:
CREATE PROCEDURE s_SMTPMailSend
(
@To varchar(8000),
@Subject varchar(255),
@Body varchar(8000),
@AttachFile varchar(200),
@UserID int,
@From varchar(255)
)
as
declare @newmail integer
declare @result integer
-- Create the CDONTS.Newmail object
exec @result = sp_OACreate 'CDONTS.NewMail', @newmail out
if @result <> 0
begin
print 'Error creating object'
exec sp_displayoaerrorinfo @newmail, @result
return
end
--Set the non-optional properties
exec @result = sp_OASetProperty @newmail, 'From', @From
exec @result = sp_OASetProperty @newmail, 'To', @To
exec @result = sp_OASetProperty @newmail, 'Subject', @Subject
exec @result = sp_OASetProperty @newmail, 'Body', @Body
--Attach the file
exec @result = sp_OAMethod @newmail, 'AttachFile', null, @AttachFile
-- Send the message...
exec @result = sp_OAMethod @newmail, 'Send'
if @result <> 0
begin
print 'Error sending message'
exec sp_displayoaerrorinfo @newmail, @result
return
end
-- Destroy the object.
exec @result = sp_OADestroy @newmail
if @result <> 0
begin
print'Error destroying object'
exec sp_displayoaerrorinfo @newmail, @result
return
end
GO
We would like to incorporate something to this effect
set @From = (select email from t_CONTACT where ID = @user-id)
but if we add this and I attempt to run it from QA it still requires use to provide @FROM instead of populating it based on the UserID.
Is it possible to specify the NewValue of sp_OAsetProperty in a query? Such as this non-functional example:
exec @result = sp_OASetProperty @newmail, 'From', (select email from t_CONTACT where ID = @user-id)
Thanks for any replies!
January 3, 2006 at 3:11 pm
Just select it into the @From variable prior to setting the property. However, you also still have @From in the parameter list of the stored proc, so you need to remove it from there and declare it locally to the stored proc.
Declare @From varchar(255)
Select @From = email from t_CONTACT where ID = @user-id
-- Error handling here - what if it's NULL because there is no Contact record?
Exec @result = sp_OASetProperty @newmail, 'From', @From
January 4, 2006 at 7:17 am
Thanks PW. I didn't know you could Decalare a parameter locally like that. It worked like a charm. Thanks again!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply