April 5, 2004 at 2:45 am
I am sending mail through sql through "sp_oamethod" but mail is working fine but without attachment,due to some limitation i am not using xp_sendmail
statment i am using
EXEC @resultcode = sp_OAMethod @oMail, 'AttachFile',@filepath
total procedure is
It is working fine but without attachment
DECLARE @SenderAddress varchar(100)
DECLARE @RecipientAddress varchar(100)
DECLARE @Subject varchar(200)
DECLARE @Body varchar(8000)
DECLARE @oMail int --Object reference
DECLARE @resultcode int
Declare @filepath varchar(200)
SET @SenderAddress = 'abc@abc.com'
SET @RecipientAddress= 'abc@abc.com'
set @filepath='d:/sdisvr09/sigma_timeline/index.htm'
SELECT @Subject = 'subject of email for today ' + CAST(getdate() AS varchar(12))
SET @Body = 'This is the body of my email'
EXEC @resultcode = sp_OACreate 'CDONTS.NewMail', @oMail OUT
IF @resultcode = 0
BEGIN
EXEC @resultcode = sp_OASetProperty @oMail, 'BodyFormat', 0
EXEC @resultcode = sp_OASetProperty @oMail, 'MailFormat', 0
EXEC @resultcode = sp_OASetProperty @oMail, 'Importance', 1
EXEC @resultcode = sp_OASetProperty @oMail, 'From', @SenderAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'To', @RecipientAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject
EXEC @resultcode = sp_OASetProperty @oMail, 'Body', @Body
EXEC @resultcode = sp_OAMethod @oMail, ' AttachFile' ,@filepath
EXEC @resultcode = sp_OAMethod @oMail, 'Send', NULL
EXEC sp_OADestroy @oMail
END
GO
Plz suggest
Thanks
April 5, 2004 at 8:48 am
Lots can go wrong with adding an attachment, the file is missing, in use etc. Try putting some error check to find out what happened. Example:
EXEC @iHr = sp_OAMethod @oMail, 'AddAttachment', @iRtn Out, @filename
IF @iHr <> 0
Begin
EXEC sp_OAGetErrorInfo @iMessageObjId, @vcErrSource Out, @vcErrDescription Out
Select @Body = @Body + char(13) + char(10) + char(13) + char(10) +
char(13) + char(10) + 'Error adding attachment: ' +
char(13) + char(10) + @vcErrSource + char(13) + char(10) +
@filename
End
Francis
April 5, 2004 at 10:36 am
This runs on the server, so be sure the path/file is valid ON THE SERVER not your workstation.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply