Today's #TipTuesday is around email functionality in Microsoft Dynamics GP and some tips on how to test it. In my post last week, I made some mistakes in testing that have led to this post. Long story short: I neglected to edit all the tables that had live email addresses when I did some testing. There are some other things I could have also done to prevent compounding the problem, and all of that is what I intend to cover in this post.

The background to this post is the standard non-Workflow-related email for emailing documents to customers or vendors. While some of what I'm about to say may apply to Workflow emails, I am going to be explicit here and say I didn't document all of the places where Workflow may contain data and it's not covered here. The only mention of Workflow is concerning valid Series settings in the SY04902 table.

Overview

There are a couple of scenarios where someone may be testing email functionality:

  1. Configuring new functionality and testing that configuration.
  2. Troubleshooting a production issue or training new staff in a test environment.

When configuring something new, by far the safest approach is to always use internal or test email addresses. Testing at this stage is about ensuring the configuration is valid, that emails are sent/received, and that "fake" data can be used for that, not real customer or vendor email information. I typically would use real email addresses for the business side of the configuration, i.e., reply to email addresses and things like that, but not for the receiving end of the configuration.

The complications arise when using a test environment to test email functionality if it's already configured for production. It's too easy to accidentally send an email to a real recipient unintentionally. The rest of this post is around this type of scenario.

Tables

There is one primary table for storing email addresses for most of the email functionality built into Dynamics GP, but it may not be the only one to update if users need to test email functionality (& overwrite production email addresses).

  • SY01200 - Internet info (including email)
    • This is information at a Master Record + Address ID level
    • This is a company table, not specific to a module so the fields are generic.
    • Master_ID is customer ID, vendor ID, employee ID etc.
    • Master_Type will be "CUS", "VEN" or "EMP" for sales, purchasing and payroll modules, respectively.
    • The EmailToAddress, EmailCCAddress and EmailBCCAddress are the specific ones used in the "new" Word template email functionality.
    • INETxx fields are the other addresses that have been in this window for years, but they are not used in any out-of-the-box functionality I'm aware of (even if there are email addresses populated there).
  • RM00106 - RM Statements Email addresses
    • This is pre-GP 2013 functionality around emailing customer statements, not the new functionality introduced later which included emailing other types of documents from Microsoft Word templates.
    • The email addresses stored here are what is populated on the Customer card, under Options ("Send email statements" section).
  • SY04906 - Email Card addresses
    • *This* is the table I neglected to update when testing something last week. This table is populated if the "Email Address based on DocType" option in the cards is utilized (Vendor Email Options or Customer Email Options windows specifically).
    • What this is tracking is which email to use for which document type.
    • Series 3 is Sales and Series 4 is Purchasing
    • Module1 is as follows
      • 9 = Receivables Management
      • 11 = Sales Order Processing
      • 12 = Purchase Order Processing
      • 19 = Payables Management

That's it for the tables where new transactions will be referenced; however if the goal is to obfuscate production data in a test environment or test company, there are additional tables to update that contain the history of emailed transactions and/or setup information where internal reply-to email addresses are stored.

  • SY04910 - Email Details
    • This is one table that tracks the details of what was emailed including the message ID used, the email To, CC, and BCC, the reply to etc.
  • SY04915 - Email History
    • This is one table that tracks the details and more, including who triggered the sending of the email and date/time stamps not recorded in the SY04910 table.
  • SY04901 - Message Setup
    • This table tracks the individual message IDs and will contain reply-to email addresses if any are populated on the Message ID.
  • SY04902 - Email Series Setup
    • This table tracks the series setup for email functionality, including the Reply-To email address by series.
    • This table contains some of the same Series as above, plus potentially more depending on what is configured:
      • 3 = Sales
      • 4 = Purchasing
      • 2 = Financial (Workflow)
      • 6 = Payroll (Workflow)
      • 7 = Project (Workflow)
      • 16 = Administration (Workflow)
      • 99 = All (Workflow)

Some of the tables identified in this section are in release GP 2013 R2 and newer. To my knowledge, the two tables that existed before that are SY01200 and RM00106, and the rest are new.

Testing considerations

Training staff scenario

In the scenarios I outlined originally, when training new staff on functionality that is email-related, even if using existing production data copied to a Test company/environment, consider creating new customers or vendors to test with. By starting with data completely made up from scratch, the likelihood of mistakenly emailing a live vendor or customer is minimal. Of course, this entails also entering the documents from scratch but in training, this should be possible.

Reproducing an issue scenario

If attempting to reproduce an issue from production and using existing production data after copying it to Test, I recommend overwriting the settings so as not to send test emails to real vendors or customers. Assuming their actual email address isn't the issue, it will not affect the testing to do this. (I say this as if that's never been the issue in the past but if it's a badly typed email address, hopefully it's identified before going to the trouble of refreshing a test environment/company!). The safest route is to update the data on the vendors/customers used to test within Dynamics GP. I know from experience that it's not always something there is time for, so updating the tables may make sense.

If going the SQL route, before doing any testing: spot-check the vendors that will be used in testing. Look up the vendors in SY04906 before testing anything. If any of the vendors appear here, make sure the email address is a test email, not a real production email.

It's always a good idea to spot-check within Dynamics GP too, just in case! Spot-check both the Internet Information window and the Email Options window in the customer or vendor used in testing. The latter is specifically looking for vendors or customers that have "Email based on DocType" enabled.

General testing advice

Other than the suggestions above for "before sending an email" testing, the other thing I highly recommend is to start small with testing batches. Test a small batch, then immediately check your Sent Items folder (in Outlook) to see where the test emails went. With each batch, check Outlook's Sent Items to ensure that nothing went to a different email than expected.

Summary

When testing email functionality, check that the emails were received at the email address that it was intended to be sent to. The comments above about checking your sent items are precautionary, to be aware of where the sent items went. Once it is confirmed that the emails were not sent anywhere unintended, then check the receiving end. If using your email for testing and see a bunch of emails coming in from testing, it's far too easy to assume that all of them came in! (Ask me how I know! LOL…)