Using cdosys from a stored procedure slightly and it is sending out email correctly.
Reference:http://support.microsoft.com/kb/555287
The main points to double check is that we want to be sure thee smtauthenticate parameter is set to 1 to indicate "cdoBasic authentication". We also set the sendUsing value to 2 in order to specify the message is to be delivered using the network.
One common pitfall is that we need to make sure all of the parameters in the cdo.fields section are typed in the RightCase eg, SMTPAUTHENTICATE will not work, but changing it to smtpauthenticate will work, because the fields are CaseSensitive
Reference: http://support.microsoft.com/kb/265527
Here is the code for the StoredProcedure:
==============================================================
==============================================================
CREATE PROCEDURE [dbo].[Send_Email_CDOSYS_SMTPAUTH]
(
@From varchar(100),
@To varchar(4000),
@cc varchar(4000),
@bcc varchar(4000),
@Subject varchar(100)=" ",
@Body varchar(MAX)=" ",
@Body_Type TINYINT,
@Attachments varchar(1024)=Null,
@Err_Loc TINYINT OUTPUT)
AS
DECLARE @iMsg INT
DECLARE @hr INT
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
Declare @Outvar INT
Declare @iPos INT
Declare @Attachment varchar(1024)
-- ** CREATE THE CDO MESSAGE OBJECT
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
If @hr <>0
BEGIN
SET @Err_Loc = 1
goto sp_Send_Sql_Mail_End
END
set @Err_Loc = 0
-- ** CONFIGURE message object
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").value', '2'
If @hr <>0
BEGIN
SET @Err_Loc = 2
goto sp_Send_Sql_Mail_End
END
-- ** CONFIGURE SERVER
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value', 'www.YourMailServer.com'
If @hr <>0
BEGIN
SET @Err_Loc = 3
goto sp_Send_Sql_Mail_End
END
-- ** CONFIGURE USERNAME
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").value', 'YourEmailUserName'
If @hr <>0
BEGIN
SET @Err_Loc = 4
goto sp_Send_Sql_Mail_End
END
-- ** CONFIGURE AUTHENTICATION
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").value','1'
If @hr <>0
BEGIN
SET @Err_Loc = 6
goto sp_Send_Sql_Mail_End
END
-- ** CONFIGURE TIMEOUT
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout").value','60'
If @hr <>0
BEGIN
SET @Err_Loc = 7
goto sp_Send_Sql_Mail_End
END
-- ** CONFIGURE PASSWORD
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").value', 'YourEmailPassword'
If @hr <>0
BEGIN
SET @Err_Loc = 8
goto sp_Send_Sql_Mail_End
END
-- ** CONFIGURE SMTP PORT
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").value','25'
If @hr <>0
BEGIN
SET @Err_Loc = 9
goto sp_Send_Sql_Mail_End
END
-- ** SAVE CONFIGURATIONS TO THE MESSAGE OBJECT
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.fields.update', null
If @hr <>0
BEGIN
SET @Err_Loc = 10
goto sp_Send_Sql_Mail_End
END
-- ** SET EMAIL PARAMETERS
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
If @hr <>0
BEGIN
SET @Err_Loc = 11
goto sp_Send_Sql_Mail_End
END
EXEC @hr = sp_OASetProperty @iMsg, 'cc', @cc
If @hr <>0
BEGIN
SET @Err_Loc = 12
goto sp_Send_Sql_Mail_End
END
EXEC @hr = sp_OASetProperty @iMsg, 'bcc', @bcc
If @hr <>0
BEGIN
SET @Err_Loc = 13
goto sp_Send_Sql_Mail_End
END
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
If @hr <>0
BEGIN
SET @Err_Loc = 14
goto sp_Send_Sql_Mail_End
END
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
If @hr <>0
BEGIN
SET @Err_Loc = 15
goto sp_Send_Sql_Mail_End
END
If @Body_Type= 2
BEGIN
EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body
END
else
BEGIN
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
END
-- ** CONFIGURE THE ATTACHMENT
-- Check for multiple attachments separated by a semi-colon ';'.
If @Attachments is not null AND @Attachments <> ''
Begin
If right(@Attachments,1) <> ';'
Select @Attachments = @Attachments + '; '
Select @iPos = CharIndex(';', @Attachments, 1)
While @iPos > 0
Begin
Select @Attachment = ltrim(rtrim(substring(@Attachments, 1, @iPos -1)))
Select @Attachments = substring(@Attachments, @iPos + 1, Len(@Attachments)-@iPos)
EXEC @Hr = sp_OAMethod @iMsg, 'AddAttachment', @OutVar Out, @Attachment
IF @Hr <> 0
Begin
Set @Err_Loc = 16
/*EXEC sp_OAGetErrorInfo @iMessageObjId, @ErrSource Out, @ErrDescription Out*/
EXEC sp_OAGetErrorInfo @imsg
select @hr
goto sp_Send_Sql_Mail_End
/*SELECT @outvar
Select @Body = @Body + char(13) + char(10) + char(13) + char(10) +
char(13) + char(10) + 'Error adding attachment: ' +
char(13) + char(10) + @ErrSource + char(13) + char(10) +
@Attachment*/
End
Select @iPos = CharIndex(';', @Attachments, 1)
End
End
-- ** SEND THE EMAIL
EXEC @hr = sp_OAMethod @iMsg, 'Send', null
If @hr <>0
BEGIN
EXEC @hr = sp_OAGetErrorInfo Null, @Source OUT, @description OUT
if @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
SET @Err_Loc = 18
goto sp_Send_Sql_Mail_End
END
sp_Send_Sql_Mail_End:
EXEC @hr = sp_OADestroy @iMsg
RETURN @Err_Loc
==============================================================
==============================================================
Here is how to call the procedure in order to test:
==============================================================
declare @errout tinyint
exec
Send_Email_CDOSYS_SMTPAUTH
'from@yourDomain.com',
'to@yourDomain.com',
'',
'',
'subject test',
'body test',
1,
@Err_Loc = @errout output
==============================================================
==============================================================
