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]
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)
+ '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'
@Profile_Name = @MAILPROFILE,
@recipients = @ToMAIL,
@body = @MESSAGE,
@subject = @HEADER;
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.