Dynamics GP - How to Setup Business Alerts

• Configure Database Mail
• SQL Management Studio>Connect to Instance>Management>Database Mail
• Go through the wizard
• Enable Email
• Create Profile
• Use the e-mail address of the account you want to use to send mails
• Enter Account Name (email address)
• Enter email address, Display name, Reply email (all email address)
• Server Name - smtp server address (smtp.office365.com if you're using office 365)
• https://support.office.com/en-in/article/Outlook-settings-for-POP-and-IMAP-access-for-Office-365-for-business-or-Microsoft-Exchange-accounts-7fc677eb-2491-4cbc-8153-8e7113525f6c?ui=en-US&rs=en-IN&ad=IN
• Port - 587 if using office 365
• Tick requires SSL
• Select Basic Authentication
• Fill in office 365 login and password
• Manage profile Security>Tick public>Next, Next
• Right click database mail>Send test email
• Configure GP Email Settings
• GP>Tools>Setup>Company>Email Settings
• Configure Business Alerts
• GP>Tools>Setup>Business Alerts
• Go through the wizard

For More advanced Email Alerts, you can use SQL to send mail directly

Original Solution
http://stackoverflow.com/questions/13300881/how-to-send-email-from-sql-server

• Create SQL Mail Profile and Account
• Run this code

-------------------------------------------------------------------------
sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO
-------------------------------------------------------------------------

• Use this code to send a single e-mail

--------------------------------------------------------------------------

USE msdb
GO
EXEC sp_send_dbmail @profile_name='TEST',
@recipients='gregory@davyntt.com',
@subject='Test message',
@body='This is the body of the test message.',
@Query = 'select * from iv00101',
@attach_query_result_as_file = 1

----------------------------------------------------------------------------

• Use this code to loop through a table and send multiple emails

---------------------------------------------------------------------------------
DECLARE @email_id NVARCHAR(450),@id BIGINT,@max_id BIGINT,@query NVARCHAR(1000)

SELECT @id=MIN(id),@max_id=MAX(id) FROM [email_adresses]

WHILE @id<@max_id
BEGIN
    SELECT @email_id=email_id
    FROM [email_adresses]

    set @query='sp_send_dbmail @profile_name=''yourprofilename'',
                        @recipients='''+@email_id+''',
                        @subject=''Test message'',
                        @body=''This is the body of the test message."
'

    EXEC @query
    SELECT @id=MIN(id)FROM [email_adresses] where id>@id

END
-------------------------------------------------------------------------------------

• Use these queries to determine if your mail is sending properly

-------------------------------------------------------------------------------------
SELECT * FROM msdb.dbo.sysmail_mailitems
SELECT * FROM msdb.dbo.sysmail_sentitems
SELECT * FROM msdb.dbo.sysmail_log