Testing Remittances By Email – Keeping Emails Internal

Microsoft Dynamics GPAs 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.

What should we write about next?

If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.

Your Name

Your Email

Suggested Topic

Suggestion Details

Looking for support or consultancy with Microsoft Dynamics GP?

I no longer work with Microsoft Dynamics GP, but the last company I worked for was ISC Software in the UK; if you’re looking for support or consultancy services with Microsoft Dynamics GP you can contact them here.

2 thoughts on “Testing Remittances By Email – Keeping Emails Internal

Leave a Reply

Your email address will not be published. Required fields are marked *