Not sure where's best to ask this.
I've been trying to port the below code to a stored
procedure from a test I made in VBScript (it works fine in
VBScript). However, I _think_ I'm having problem with the

exec @hr=sp_OASetProperty @objectmessage, 'Fields
("Configuration")',
@object

line. I've tried it with

exec @hr=sp_OASetProperty @objectmessage, 'Fields
("Configuration")',
@object

instead, and it gives a type mismatch error, which I
assume is due to
trying to pass the @object token instead of an actual
object. How to
solve this? (sanitised sproc follows)

create procedure dbo.I_mail_send
(
@EmailTo nvarchar(100),
@EmailFrom nvarchar(100) = '[Unknown]',
@EmailSubject nvarchar(100) = '[No Subject]',
@EmailText nvarchar(3500) = ''
)
as
declare @hr int,
@object int,
@objectmessage int,
@recipient char(200)

exec @hr=sp_OACreate 'CDO.Configuration', @object OUTPUT
if @hr <> 0 goto ErrorHandler
exec @hr=sp_OASetProperty @object, 'Fields
("SMTPServer")', '<ip>'
if @hr <> 0 goto ErrorHandler
exec @hr=sp_OASetProperty @object, 'Fields
("smtpconnectiontimeout")', 30
if @hr <> 0 goto ErrorHandler
exec @hr=sp_OASetProperty @object, 'Fields
("sendusername")', '<loginname>'
if @hr <> 0 goto ErrorHandler
exec @hr=sp_OASetProperty @object, 'Fields
("sendpassword")', '<password>'
if @hr <> 0 goto ErrorHandler
exec @hr=sp_OAMethod @object,'Fields.Update'
if @hr <> 0 goto ErrorHandler
exec @hr=sp_OACreate 'CDO.Message', @objectmessage OUTPUT
if @hr <> 0 goto ErrorHandler
exec @hr=sp_OASetProperty @objectmessage, 'Fields
("Configuration")',
@object
if @hr <> 0 goto ErrorHandler
exec @hr=sp_OASetProperty @objectmessage, 'MIMEFormatted',
true
if @hr <> 0 goto ErrorHandler
exec @hr=sp_OAMethod @objectmessage, 'Fields.Update'
if @hr <> 0 goto ErrorHandler
exec @hr=sp_OASetProperty @objectmessage, 'To', @EmailTo
if @hr <> 0 goto ErrorHandler
exec @hr=sp_OASetProperty @objectmessage, 'From',
@EmailFrom
if @hr <> 0 goto ErrorHandler
exec @hr=sp_OASetProperty @objectmessage, 'Subject',
@EmailSubject
if @hr <> 0 goto ErrorHandler
exec @hr=sp_OASetProperty @objectmessage, 'TextBody',
@EmailText
if @hr <> 0 goto ErrorHandler
exec @hr=sp_OAMethod @objectmessage, 'Send'
if @hr <> 0 goto ErrorHandler
return 0
ErrorHandler:
declare @source varchar(255),
@description varchar(255)
exec sp_OAGetErrorInfo @hr, @source out, @description out
print 'ERROR'
print @source
print @description
return @hr
go

Report this post to a moderator