dcsimg


CREATE PROCEDURE SendMail 
     @mailbox varchar(80) = 'Bill Wunder',
     @notifyMissing VARCHAR(30) = 'test1',
     @notifyFail VARCHAR(30) = 'test2'
AS

DECLARE @messageID INT
DECLARE @messageSender VARCHAR(100)
DECLARE @messageRecipient VARCHAR(100)
DECLARE @messageSubject VARCHAR(100)
DECLARE @messageBody varchar(100)
DECLARE @messageAttachment VARCHAR(100)
DECLARE @messageQuery VARCHAR(2000)
DECLARE @cmd VARCHAR(1000)
DECLARE @CMDstatus INT
DECLARE @myName VARCHAR(100)

SET NOCOUNT ON
/*
 sent status codes
	0  - not sent
	1  - sent
	99 - error sending mail
*/

select @myName = db_name()
  + '.'
  + user_name(objectproperty(@@procid, 'OwnerId'))
  + '.'
  + object_name(@@procid)

DECLARE messageCursor CURSOR 
FOR
 SELECT ID  
 FROM messages
 WHERE sentStatus = 0

OPEN messageCursor
FETCH NEXT FROM messageCursor INTO @messageID
WHILE (@@fetch_status = 0)
  BEGIN 
    SELECT @messageSender = sender,
           @messageRecipient = recipient,
           @messageSubject = subject,
           @messageBody = body,
           @messageAttachment = attachment,
           @messageQuery = query 
    FROM messages
    WHERE ID = @messageID
    IF @messageSender <> @mailbox
      EXECUTE @CMDstatus = sendMAPIMail 
                            @messageID, 
                            @messageSender,
                            @messageRecipient, 
                            @messageSubject 
    ELSE
      BEGIN       
        /* can only send query results 
           as attachment if there is no
           attachment specified in the 
           message record */	
        IF @messageAttachment IS NULL
          BEGIN
            IF @messageQuery IS NULL  
              EXECUTE @CMDstatus = master..xp_sendmail 
                    @recipients = @messageRecipient,
                    @subject = @messageSubject,
                    @message = @messageBody/*,
                    @no_header = 'true' --!*/
            ELSE
              EXECUTE @CMDstatus = master..xp_sendmail 
                    @recipients = @messageRecipient,
                    @subject = @messageSubject,
                    @message = @messageBody,
                    @query = @messageQuery,
                    @no_header = 'true',
                    @attach_results = 'true'
          END
        ELSE
          BEGIN
            SELECT @cmd = 'dir ' + @messageAttachment
            EXEC @CMDstatus = master..xp_cmdshell @cmd
            IF @CMDstatus <> 0
              BEGIN
                SELECT @messageSubject = @myName 
                     + ' error - attachment not found. message ID: ' 
                     + CONVERT(VARCHAR(5),@messageID)
                EXEC master..xp_sendmail 
                             @recipients = @notifyMissing,
                             @subject = @messageSubject,
                             @message = @messageAttachment            
              END
            ELSE
              BEGIN
                IF @messageQuery IS NULL
                  EXECUTE @CMDstatus = master..xp_sendmail 
                                       @recipients = @messageRecipient,
                                       @subject = @messageSubject,
                                       @message = @messageBody
                ELSE 
                  EXECUTE @CMDstatus = master..xp_sendmail 
                                       @recipients = @messageRecipient,
                                       @subject = @messageSubject,
                                       @message = @messageBody,
                                       @query = @messageQuery,
                                       @no_header = 'true'
              END
          END 
      END
    /* if send OK mark record as sent (1) 
       else mark sent as failed (99) and 
       notify someone */
    IF (@CMDstatus = 0)
      UPDATE messages
      SET sentStatus = 1, 
          sentDate = GETDATE()
      WHERE ID = @messageID
    ELSE  
      BEGIN
        SELECT @messageSubject = @myName 
              + ' - Error sending mail. message ID: ' 
              + CONVERT(VARCHAR(5),@messageID)
        EXEC master..xp_sendmail 
                 @recipients = @notifyFail,
                 @subject = @messageSubject
        UPDATE messages
        SET sentStatus = 99, 
            sentDate = GETDATE()
        WHERE ID = @messageID
      END
    FETCH NEXT FROM messageCursor INTO @messageID  
END -- unsent mail
CLOSE messageCursor
DEALLOCATE messageCursor