ENVOI DE MAIL SANS PROFIL MAPPY AVEC SQL SERVER 2000

--------------------------------------------------------------------------
-- Script created in November 25 2003 by Arnaud CLERET			--
-- This store procedure allow to send a mail without a mappy profil	--
--------------------------------------------------------------------------

USE master
GO

DECLARE @WithLogEvent VARCHAR(5)
DECLARE @ReplaceMessage VARCHAR(7)

SET @WithLogEvent = 'false'
SET @ReplaceMessage = 'REPLACE'

EXECUTE sp_addmessage @msgnum = 50001, @severity = 16, @msgtext = N'Error during sending mail (Source : %s --- Description : %s).', @lang = 'us_english', @with_log = @WithLogEvent, @replace = @ReplaceMessage
EXECUTE sp_addmessage @msgnum = 50001, @severity = 16, @msgtext = N'Erreur lors de l''envoie du mail (Source : %1! --- Description : %2!)', @lang = 'French', @with_log = @WithLogEvent, @replace = @ReplaceMessage
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
CREATE PROCEDURE dbo.sp_sendMail
(
	@MailFrom VARCHAR(50) = @@SERVERNAME,
	@MailTo VARCHAR(255) = 'mail@demo.com',
	@MailCc VARCHAR(255) = NULL,
	@MailBcc VARCHAR(255) = NULL,
	@MailSubject VARCHAR(500) = NULL,
	@MailBody VARCHAR(8000) = NULL,
	@IsHTMLBody BIT = 0,
	@FilesAttachments VARCHAR(8000) = NULL,
	@FilesAttachmentsDelimiter CHAR(1) = ';',
	@SendUsing CHAR(1) = '2',
	@SMTPServerName VARCHAR(15) = '192.168.0.1',
	@SMTPServerPort VARCHAR(5) = '25'
)
AS
BEGIN
	SET NOCOUNT ON
		
	DECLARE @FilePath VARCHAR(255)
	DECLARE @StatusRetour INT
	DECLARE @CdoObject INT
	DECLARE @ErrorSource NVARCHAR(255)
	DECLARE @ErrorDescription NVARCHAR(500)
	
	EXECUTE @StatusRetour = sp_OACreate 'CDO.Message', @CdoObject OUTPUT
	IF (@StatusRetour <> 0) GOTO ErrorHandler
	
	EXECUTE @StatusRetour = sp_OASetProperty @CdoObject, 'From', @MailFrom
	IF (@StatusRetour <> 0) GOTO ErrorHandler
	
	EXECUTE @StatusRetour = sp_OASetProperty @CdoObject, 'To', @MailTo
	IF (@StatusRetour <> 0) GOTO ErrorHandler
	
	IF(@MailCc IS NOT NULL)
	BEGIN
		EXECUTE @StatusRetour = sp_OASetProperty @CdoObject, 'Cc', @MailCc
		IF (@StatusRetour <> 0) GOTO ErrorHandler
	END

	IF(@MailBcc IS NOT NULL)
	BEGIN
		EXECUTE @StatusRetour = sp_OASetProperty @CdoObject, 'Bcc', @MailBcc
		IF (@StatusRetour <> 0) GOTO ErrorHandler
	END

	IF(@MailSubject IS NOT NULL)
	BEGIN
		EXECUTE @StatusRetour = sp_OASetProperty @CdoObject, 'Subject', @MailSubject
		IF (@StatusRetour <> 0) GOTO ErrorHandler
	END

	IF(@MailBody IS NOT NULL)
	BEGIN
		IF(@IsHTMLBody = 0)EXECUTE @StatusRetour = sp_OASetProperty @CdoObject, 'TextBody', @MailBody
		ELSE EXECUTE @StatusRetour = sp_OASetProperty @CdoObject, 'HTMLBody', @MailBody
		IF (@StatusRetour <> 0) GOTO ErrorHandler
	END

	IF (@FilesAttachments IS NOT NULL)
	BEGIN     
		SET @FilesAttachments = LTrim(RTrim(@FilesAttachments))
		WHILE(DATALENGTH(@FilesAttachments) > 0)
		BEGIN
			IF(CHARINDEX(@FilesAttachmentsDelimiter,@FilesAttachments) > 0)
			BEGIN
				SET @FilePath = SUBSTRING(@FilesAttachments,1, CHARINDEX(@FilesAttachmentsDelimiter,@FilesAttachments)-1)
				SET @FilesAttachments = LTrim(Right(@FilesAttachments,DATALENGTH(@FilesAttachments) - DATALENGTH(@FilePath + @FilesAttachmentsDelimiter)))
				EXECUTE @StatusRetour = sp_OAMethod @CdoObject, 'AddAttachment', NULL, @FilePath
			END
			ELSE
			BEGIN
				SET @FilePath = @FilesAttachments
				EXECUTE @StatusRetour = sp_OAMethod @CdoObject, 'AddAttachment', NULL, @FilePath
				BREAK
			END
		END
	END
	
	
	EXECUTE @StatusRetour = sp_OASetProperty @CdoObject, 'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value', @SendUsing
	IF (@StatusRetour <> 0) GOTO ErrorHandler
	
	EXECUTE @StatusRetour = sp_OASetProperty @CdoObject, 'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @SMTPServerName
	IF (@StatusRetour <> 0) GOTO ErrorHandler
	
	EXECUTE @StatusRetour = sp_OASetProperty @CdoObject, 'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").Value', @SMTPServerPort
	IF (@StatusRetour <> 0) GOTO ErrorHandler
	
	EXECUTE @StatusRetour = sp_OAMethod @CdoObject, 'Configuration.Fields.Update'
	IF (@StatusRetour <> 0) GOTO ErrorHandler
	
	EXECUTE @StatusRetour = sp_OAMethod @CdoObject, 'Send'
	IF (@StatusRetour <> 0) GOTO ErrorHandler
	
	EXECUTE sp_OADestroy @CdoObject
	RETURN	

	ErrorHandler:
	EXECUTE @StatusRetour = sp_OAGetErrorInfo @CdoObject, @ErrorSource OUT, @ErrorDescription OUT 
	IF (@StatusRetour = 0)RAISERROR (50001, 16, 1, @ErrorSource, @ErrorDescription)
	ELSE RAISERROR('Error in sp_sendMail !',16,1)
	EXECUTE sp_OADestroy @CdoObject
	RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO 
SET ANSI_NULLS ON 
GO