Modify send mail procedure

douglasfilipe you can create a table to store the mail information and then replace the sp_send_dbmail with an INSERT INTO statement using this modified procedure:

Let me know if this works for you. @ me in replies, or I'll lose your thread!!!  

CREATE TABLE dbo.MailLog (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    RecipientEmail NVARCHAR(255),
    Subject NVARCHAR(255),
    Message NVARCHAR(MAX),
    SentDateTime DATETIME DEFAULT GETDATE()
);

-- Modify the procedure
ALTER PROCEDURE [dbo].[SendMailMessage]
(
    @AMEI INT,
    @DeclarationID UNIQUEIDENTIFIER,
    @Action NVARCHAR(50)
)
WITH EXECUTE AS CALLER
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Get the mail message(s)
    DECLARE
        @Role NVARCHAR(50),
        @MailSubject NVARCHAR(255),
        @MailMessage NVARCHAR(MAX),
        @LogMessage NVARCHAR(MAX),
        @OwnerAMEI INT,
        @OwnerFullName NVARCHAR(255),
        @RecipientAMEI INT,
        @RecipientFullName NVARCHAR(255),
        @RecipientEmail NVARCHAR(255),
        @RecipientLanguageCode CHAR(2),
        @AssessmentYear INT

    -- An item in the outbox can have multiple receivers
    DECLARE cur_messages CURSOR FOR
        SELECT mm.[Role], ISNULL(mm.Subject, mm.Subject), ISNULL(mm.Message, mm.Message)
        FROM WF_MailMessages mm
        WHERE mm.Action = @Action
            AND mm.LanguageCode = 'EN'

    OPEN cur_messages
    FETCH NEXT FROM cur_messages INTO @Role, @MailSubject, @MailMessage

    WHILE @@FETCH_STATUS = 0 BEGIN

        -- Get assessment year from declaration
        SELECT @AssessmentYear = Assessment
        FROM Declarations
            WHERE ID = @DeclarationID

        -- Get recipient's information
        SELECT @RecipientAMEI = AMEI
        FROM WF_DeclarationActors
        WHERE DeclarationID = @DeclarationID
            AND [Role] = @Role

        SELECT @RecipientFullName = e.FullName, @RecipientEmail = e.Email, @RecipientLanguageCode = e.LanguageCode
        FROM Employees e
        WHERE e.AMEI = @RecipientAMEI
            AND e.Assessment = @AssessmentYear

        -- Get owner information
        SELECT @OwnerAMEI = AMEI
        FROM WF_DeclarationActors
        WHERE DeclarationID = @DeclarationID
            AND [Role] = 'OWNER'

        SELECT @OwnerFullName = e.FullName
        FROM Employees e
        WHERE e.AMEI = @OwnerAMEI
            AND e.Assessment = @AssessmentYear

        -- Check recipient has a different language than EN
        If (@RecipientLanguageCode is not null AND @RecipientLanguageCode <> 'EN')
        BEGIN
            DECLARE @NewSubject NVARCHAR(255),
            @NewMessage NVARCHAR(MAX)

            SELECT @NewSubject = ISNULL(mm.Subject, mm.Subject), @NewMessage = ISNULL(mm.Message, mm.Message)
            FROM WF_MailMessages mm
            WHERE mm.Action = @Action
                AND mm.LanguageCode = @RecipientLanguageCode

            IF @NewSubject is not null SET @MailSubject = @NewSubject
            IF @NewMessage is not null SET @MailMessage = @NewMessage
        END

        -- Substitute placeholders
        SET @MailSubject = REPLACE(@MailSubject, '#OWNER#', @OwnerFullName)
        SET @MailMessage = REPLACE(@MailMessage, '#OWNER#', @OwnerFullName)
        SET @MailMessage = REPLACE(@MailMessage, '#N+1#', @RecipientFullName)
        SET @MailMessage = REPLACE(@MailMessage, '#MANAGEMENT#', @RecipientFullName)
        SET @MailMessage = REPLACE(@MailMessage, '#COMP_OFF#', @RecipientFullName)
        SET @MailMessage = REPLACE(@MailMessage, '#URL#', 'mysite')

        -- Instead of sending mail, insert into the MailLog table
        INSERT INTO dbo.MailLog (RecipientEmail, Subject, Message)
        VALUES (@RecipientEmail, @MailSubject, @MailMessage);

        BEGIN CATCH
            SET @LogMessage = 'Error occurred during SendNotification:' + CONVERT(VARCHAR, @AMEI) + ', ' + CONVERT(VARCHAR(150), @RecipientEmail)
            SET @LogMessage = (SELECT ERROR_MESSAGE() AS ErrorMessage)

            EXEC InsertLog2 @AMEI, @LogMessage , NULL, @DeclarationID, @OwnerAMEI
        END CATCH

        SET @LogMessage = 'SendMailMessage(' + @Action + ', ' + @RecipientLanguageCode + '): ' + @MailMessage
        EXEC InsertLog2 @AMEI, @LogMessage , NULL, @DeclarationID, @OwnerAMEI

        FETCH NEXT FROM cur_messages INTO @Role, @MailSubject, @MailMessage
    END

    CLOSE cur_messages
    DEALLOCATE cur_messages
END