Custom Emailing for Microsoft Great Plains Dynamics GP: SQL Mail

In this small article we recommend you to use MS SQL 2005 mail: xp_sendmail extended stored procedure.  Another requirement is to use Adobe PDF Writer (version is not important, whatever is compatible with Windows XP an Vista will do the job):

1. SQL Mail setup.  The easiest way to setup SQL mail is to launch SQL Server service under windows domain account.  Then you install MS Office Outlook client on the server (MAPI application), make it work for Windows domain login – virtually sending and receiving email.  In order to be sure that you are done with SQL email setup, open MS SQL Server query and try to send probe email: xp_sendmail ‘john@gmail.com’, ‘Test’;
2. Email attachment problem and work around.  Well if you will try to use non-sa user to call xp_sendmail with attachment, something like the following: xp_sendmail ‘john@gmail.com’, ‘Test’, @attachments=’C:\invoice.pdf’ – you will get error message, where it will recommend you to use impersonated windows account to work with attachments.  Seems like SQL 2005 has some internal issues with SQL login impersonation, and at this time (December 2007) it simply putting doesn’t work.  Work around is the following: create SQL job which will be running under sa account and will be calling xp_sendmail, schedule this job to run, say every 10 minutes, and it will check all the documents to be ready to be emailed to your customers with attachments
3. Adobe PDF Writer.  Microsoft Dynamics GP requires you to use Adobe PDF Writer to save documents, such as SOP Invoices and Sales Orders to the file.  You may think to try using free third party PDF printing solutions, such as Cute PDF, however this approach doesn’t work with current and historical versions of Microsoft Great Plains Dynamics GP: 6.0, 7.0, 7.5, 8.0, 9.0 and 10.0
4. Great Plains ReportWriter forms: SOP Blank, Long and Short forms.  Here you want to place your company logo, please review Microsoft Dynamics GP guidelines for your graphical logo format to have it printed smoothly and professionally.  If you would need several different logos on your invoice form, you should consider deploying blank, short and long SQP invoice forms to do the job – actually you could make them all these three forms the same, just with different logos
5. Alternatives to SQL Mail.  You can consider Sendmail Windows utility or Telnet.  Both of them tend to rely on Linux and Unix features and skills, so technical discussion fells out of scope for this tiny article