Testing Remittances By Email – Keeping Emails Internal

As you’ve probably been able to tell from my previous posts, I’ve been assisting a client in configuring Microsoft Dynamics GP 2010 so they can test remittances by email.

One of the things I have done for them is produce two SQL scripts to ensure emails do not leave their organisation and reach suppliers telling them of payments being made in the test system.

The first script updates any creditor currently in Microsoft Dynamics GP with an email address in the Internet Information window accessible from the i button (Cards » Purchasing » Creditor) with an email address specified in the @TestEmail variable defined at the top of the script;

DECLARE @TestEmail VARCHAR(100)
SET @TestEmail = 'email@example.com'

UPDATE
   SY01200
SET
   INET1 = @TestEmail
   ,EmailToAddress = @TestEmail
   ,EmailCcAddress = ''
   ,EmailBccAddress = ''
WHERE
   Master_Type = 'VEN'

As the client didn’t have many creditors set up in GP with an email address, I created the second script to add an email address for all creditors on the system in bulk so, instead of spending their time doing data entry, the client could spend their time testing;

DECLARE @TestEmail VARCHAR(100)
SET @TestEmail = 'email@example.com'

INSERT INTO SY01200
   (Master_Type
   ,Master_ID
   ,ADRSCODE
   ,INETINFO
   ,EmailToAddress
   ,EmailCcAddress
   ,EmailBccAddress)
(SELECT 
   'VEN'
   ,VENDORID
   ,ADRSCODE
   ,''
   ,@TestEmail
   ,''
   ,''
FROM
   PM00300 PM3
WHERE
   (SELECT
      COUNT(*)
   FROM
      SY01200 SY12 
   WHERE
      SY12.Master_ID = PM3.VENDORID
      AND SY12.ADRSCODE = PM3.ADRSCODE) = 0)

This second script will eventually be amended to default in an internal email address into the bcc Email Address field on the live system so that a record is kept of all emails sent out of Microsoft Dynamics GP.

As always with SQL scripts, before running them please ensure you have a valid backup of the database in case of problems.