Sending Business Alert Email Notification to email addresses contained in the database

Instead of hard coded the email address in the Recipients tab on the Business Alert Wizard, users can define the email destination from the database.

1. Define the query. Make sure the destination email address is presented in the 1st column

SELECT

M.EMAIL,

[Employee]=DBO.EENAME(L.EE_NO),

[Leave Date]=L.LEAVE_DATE,

[Leave Type]=T.DESCRIPTION

from plLeaveDayRecord L

INNER JOIN plEmployee E ON E.EE_NO=L.EE_NO

INNER JOIN plDept D ON D.DEPT_CODE=E.DEPT_CODE

INNER JOIN plEmployee M ON M.EE_NO=D.MANAGER

INNER JOIN plLeaveType T ON T.LEAVE_CODE=L.LEAVE_CODE

WHERE

L.LEAVE_DATE>=CONVERT(date, getdate()) AND L.LEAVE_DATE <= dateadd(d, 31, GETDATE())

ORDER BY M.EMAIL, L.LEAVE_DATE, DBO.EENAME(L.EE_NO)

This query will list the coming approved leave of the coming 31 days of each department and is brought the email address of the employee's department manager at column 1

2. Define the Business Alert Job as usual

Leave blank to the the To: field 

   Every Monday, each department manager will receive the email from the Business Alert at 9:00am something like that,

p s. If the column name of the 2nd column is CC, the column content will be put into the CC field in the email.