XP_Sendmail for Microsoft Great Plains Dynamics GP – FAQ

Customizing Microsoft Dynamics GP as the internal activity is not easy one, and then you need automatic emailing, we recommend to you check with specialists.  In this small article we would like to concentrate on SQL mail or xp_sendmail extended stored procedure:

1. MAPI client setup on your SQL Server 2005 or 2000.  This is relatively easy task.  You should run your SQL server service under domain account, which will be setup in exchange to email your stuff from Great Plains, more likely Accounts Receivable account, if you plan to email invoices to your customers.  Just install Microsoft Office Outlook 2007, 2003 or 2000 – they’ll do the job – you don’t really need the “power” of Outlook 2007
2. Adobe PDF writer.  It is not enough to install third party free PDF printer emulation programs, you will need genuine PDF writer to generate PDF form of your invoices from simple customized Microsoft Great Plains Dexterity application – please check with us, we have this Dex customization let’s say “solution”, as it is not an off-the-shelf product.  PDF writer is required if you plan to email letters to your customers, where you will be attaching invoices in PDF format
3. Security.  XP_SENDMAIL works under SQL security realm.  If you use sa for testing, it might not work for regular GP users, especially in attachment.  SA impersonates in windows admin, so you should not trust the testing of using sa if you plan to email pdf attachments.
4. SQL user id impersonalization.  Well, in case of Great Plains Dynamics GP user id is really SQL login, propagated to DYNAMICS and company databases.  And considering the fact, that attachment should be authorized by Windows domain user, you have to imprersonate SQL login.  We spent few hours with Microsoft technical support to make that happen, however at this time, December 2007, Microsoft SQL Server 2005 seems to have this as internal bug
5. Attachment security work around.  Obviously the format of this tiny article doesn’t allow us to give you all the source codes, however we are happy to give you the hint right in the article – please consider creating SQL Server job to run emailing procedure (we’ll give you the code) with SA user credentials
6. Linux and UNIX customers.  If you deploy Redhat or another Linux or Unix solution, you can try to import PDF files created in Windows Microsoft Dynamics GP logic to your Linux solution and do invoices emailing from there
7. eConnect, C# and VB.Net approaches.  Technically xp_sendmail  could email Crystal Reports version, GP ReportWriter and PDF files, in the solution we propose you try out PDF format, and if you need further logic advancements, we are here to help you.  eConnect is very flexible tool especially for e-commerce GP integrations