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

More:
Vendor Notifications on EFT Bank Transfer