[SQL Server] Envoyer des Mails sans passer par la procédure sp_send_dbmail

-- =============================================

-- Author:        Zouhaier KHARROUBI

-- Create date:   07/03/2017

-- Description:   Envoyer des mails via un serveur SMTP

-- NB : Il faut configuer le serveur SMTP sur IIS 6.0

-- Si IIS 6.0 n'est pas installé il faut l'ajouter en passant par Outils d'administration \ Gestionnaire de serveur \ Fonctionnalités

-- et cliquere sur le lien Ajouter des fonctionnalités

-- Les références de l'objet CDOSY sont indiquées sur le MSDN

-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp

-- https://support.microsoft.com/fr-fr/help/312839/how-to-send-e-mail-without-using-sql-mail-in-sql-server

-- =============================================

La configuration d'un serveur SMTP se fait comme indiqué dans les imprimes écrans ci-dessous :

smpt config 1

smpt config 2

smpt config 3

smpt config 4

smpt config 5

smpt config 6

smpt config 7

smpt config 8

smpt config 9

smpt config 10

smpt config 11

smpt config 12

smpt config 13

smpt config 14

Pour tester le bon fonctionnement du serveur SMTP il faut mettre dans C:\inetpub\mailroot\Pickup un fichier de test de mail.

Ce fichier doit porter le nom email.txt et son contenu doit être comme ci-dessous :

From: Cette adresse e-mail est protégée contre les robots spammeurs. Vous devez activer le JavaScript pour la visualiser.
To: Cette adresse e-mail est protégée contre les robots spammeurs. Vous devez activer le JavaScript pour la visualiser.
Subject: Email test

Ceci est un test du mail

Ci-dessous le script SQL qui permet d'utiliser le serveur SMTP précédemment configuré pour envoyer des mails :

 

CREATE PROCEDURE [dbo].[usp_Envoyer_smtpmail]

   @ExpediteurMail varchar(150) ,

   @DestinataireMail varchar(150) ,

   @DestinataireMailCaches varchar(150) ,

   @ObjetMail varchar(100)=' ',

   @ContenuMail varchar(MAX) =' ',

   @PiecesJointes varchar(250)= NULL --séparer par ; (point virgule) la liste des pièces jointes

   AS

  

   DECLARE @iMessage int

   , @hr int

   , @rv INT

   , @source varchar(255)

   , @description varchar(500)

   , @sortie varchar(1000)

--************* Création de l'objet CDO.Message ************************

   EXEC @hr = sp_OACreate'CDO.Message', @iMessage OUT

--*************** Configuration de l'Ojet Message ******************

-- Configuration du serveur SMTP

-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp

   EXEC @hr = sp_OASetProperty@iMessage, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

-- Configuration du nom du serveur et de l'adresse IP

-- Remplacer NomServeurMessagerie par le nom ou l'adresse IP de votre serveur SMTP

   EXEC @hr = sp_OASetProperty@iMessage, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'audpanexch'

-- Enregistrer la configuration de l'Ojet Message

   EXEC @hr = sp_OAMethod@iMessage, 'Configuration.Fields.Update', null

-- Définir les paramètres du mail à envoyer

   EXEC @hr = sp_OASetProperty@iMessage, 'To', @DestinataireMail

   EXEC @hr = sp_OASetProperty@iMessage, 'BCC', @DestinataireMailCaches  

   EXEC @hr = sp_OASetProperty@iMessage, 'From', @ExpediteurMail

   EXEC @hr = sp_OASetProperty@iMessage, 'Subject', @ObjetMail

   EXEC @hr = sp_OAMethod@iMessage, 'AddAttachment', @rv out, @PiecesJointes

-- Renseigner le format ( HTML ou TXT) du mail à envoyer. Renseigner 'HTMLBody' pour le format HTML et 'TextBody' pour le format TXT.

   EXEC @hr = sp_OASetProperty@iMessage, 'HTMLBody', @ContenuMail

   EXEC @hr = sp_OAMethod@iMessage, 'Send', NULL

-- Gestion des erreurs.

   IF @hr <>0

     select @hr

     BEGIN

       EXEC @hr = sp_OAGetErrorInfoNULL, @source OUT, @description OUT

       IF @hr = 0

         BEGIN

           SELECT @sortie = ' Source: ' + @source

           PRINT @sortie

           SELECT @sortie = ' Description: ' + @description

           PRINT @sortie

         END

       ELSE

         BEGIN

           PRINT ' sp_OAGetErrorInfo failed.'

           RETURN

         END

     END

-- Supprimer l'objet crée

   EXEC @hr = sp_OADestroy@iMessage