Updating Email File Formats in SQL

The best laid plans...  Word templates are not my favorite thing.  There I said it.  It is not because I don't think are pretty and functional and allow for more flexibility than report writer reports.  It's just that they can be time consuming in a way that clients don't expect.  Modifications that might be worth 1-2 hours of effort end up taking double or triple due to the quirks of word templates. 


I have found that this disconnect happens most frequently with clients who already have modified report writer reports.  The process to recreate a word template that looks like the report writer report can be challenging and time consuming.  So in those cases, I try to use HTML for email delivery.  That way clients can keep using the report writer report, and the cost to implement email delivery can be quite low.


Sometimes, though, you get stuck.  HTML doesn't work well with complicated layouts, and can be unpredictable in terms of how it displays for different recipients.  So then we have to use the word templates and send in PDF.  This is fine, except if you have already set up customers for emailing.  So now you have to change the format in Cards-Sales-Customer-Email Settings.

For that purpose, you can use the script below.  Keep in mind that EmailDocumentID (in this case it is set to Sales Invoice) and EmailSeriesID (in this case it is set to Sales)  would vary for different modules and documents.  The EmailDocumentFormat is the field you want to update.  It is the same as the dropdown (1-docx, 2-html, 3-pdf, 4-xps).

--Update sales invoice email settings for all customers to use PDF
update SY04905 set EmailDocumentFormat=3 where EMAILSeriesID=3 and EmailDocumentID=3 and MODULE1=11 and EmailDocumentEnabled=1

As always, test first and always back up your data!

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a director with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.