Vendor Notifications on EFT Bank Transfer

 

I got a request from one of my clients to notify vendors by mail once they release his payment to the EFT bank, where I had to create a trigger on CM20202 to monitor payments and send the mail to the vendor, below the script I used:

Create TRIGGER [dbo].[SendVendorMails]
   ON  [dbo].[CM20202]
   FOR INSERT
AS
BEGIN
 
DECLARE @MAILPROFILE VARCHAR(8000)
DECLARE @ToMAIL  VARCHAR(8000)
DECLARE @MESSAGE     VARCHAR(8000)
DECLARE @HEADER         VARCHAR(8000)

SET @HEADER = 'Payment Transfer'
SET @MESSAGE = 'Dear Esteemed Vendor,' + char(10) + char(10)

+ 'Kindly be advised that we have processed payment with the amount of (' + CONVERT(VARCHAR(500), (SELECT [Checkbook_Amount] FROM INSERTED)) + ') to your account. ' + char(10) + char(10)
+ 'Your kind confirmation of subject payment to the following email is highly appreciated (payables@XXXX.com)' + char(10)
+ char(10)
+ char(10)
+ 'Regards,' + char(10) + char(10)
+ 'Accounts Payable Unit' + char(10)

SELECT @ToMAIL = COMMENT1 FROM PM00200 WHERE VENDORID = (SELECT [CustomerVendor_ID] FROM INSERTED)
SET @MAILPROFILE        = 'Administrator'
EXEC msdb.dbo.sp_send_dbmail
@Profile_Name = @MAILPROFILE,
@recipients = @ToMAIL,
@body = @MESSAGE,
@subject = @HEADER;

END

Note: the vendor e-mail address was saved in COMMENT1 field in the vendor card, you can modify the query to pull vendor mail from different field.

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
www.mohdaoud.com