dcsimg

CREATE procedure sendMAPIMail 
	@messageId int,                              
	@sender varchar(100),
	@recipient varchar(100),
	@subject varchar(100), 
	@password varchar(30) = 'none', 
	@mapiPath varchar(80) = 'C:\mailbox\'
AS
declare @cmd varchar(8000)
declare @cmdStatus int

/* 
   To add a sending mailbox:

   Create a new mailbox on the Exchange Server.
   Use the domain account that the SQL Server 
   service uses to login. This should keep permission 
   problems to a minimum.

   Grant 'send as' permission on this new mailbox to 
   the SQL Server login name. This will allow the 
   mapisend program to use trusted security. With 
   send as permission granted, the password is still a 
   required parm, but it is not verified.

   Do not 'hide' the new account from the GAL. If 
   mapisend doesn't see the send from mailbox in 
   the GAL, the send will fail

   Create a folder under the @mapiPath for each 
   sending mailbox.

   MAPISEND has an option for cc recipients, but I 
   could not get it to work. It also claims to support 
   multiple entries in a semicolon seperated list
   in the recipient list. Could not get this to work 
   either. Could be because SQL Server doesn't like 
   semicolons too much.

*/

SET NOCOUNT ON

/* Put the body in a text file */
SELECT @cmd = 'osql -E -Q "SET NOCOUNT ON ' 
 + 'SELECT body FROM mail.dbo.messages WHERE Id = ' 
 + convert(varchar(10),@messageID) + '" -h-1 -w2000 -o ' 
 + @mapiPath + rtrim(@sender) + '\' 
 + convert(varchar(10),@messageID) + '.txt'
SELECT @cmd
EXEC @cmdStatus = master..xp_cmdshell @cmd, no_output

/* send the email */
IF @cmdStatus = 0
  BEGIN
    SELECT @cmd = @mapiPath + 'mapisend -u "' 
          + rtrim(@sender) + '" -p "' + @password 
          + '" -r "' + @recipient + '" -s "' + @subject 
          + '" -t "' + @mapiPath + rtrim(@sender) + '\' 
          + convert(varchar(10),@messageID) + '.txt">' 
          + @mapiPath + rtrim(@sender) + '\' 
          + convert(varchar(10),@messageID) + '.out'
    EXEC @cmdStatus = master..xp_cmdshell @cmd, no_output
  END

/* remove the text file created above */
IF @cmdStatus = 0
  BEGIN
    SELECT @cmd = 'del ' + @mapiPath 
            + rtrim( @sender) + '\' 
            + convert(varchar(10),@messageID) + '.txt'
    EXEC @cmdStatus = master..xp_cmdshell @cmd, no_output
  END

RETURN @cmdStatus