Submit Your Suggestions for Vista

Here's some code for the SSRS report:

DECLARE @list varchar(max)

Select @list = COALESCE(@list+';','')+Email from bPREH WHERE PRCo=@PRCo AND ActiveYN='Y'

SELECT @list AS [Emails]

Have fun!

I suggest a very simple SSRS report, if the people doing the emailing have OUTLOOK or some other email program on their computer:

1. Create SSRS report that pulls email addresses for only active employees.
2. In the report, create a MAILTO link with the following added together:
   1. mailto:noreply@yourcompany.com?bcc=
   2. employeeOne@co.com;
   3. employeeTwo@co.com;...etc
3. Then whenever you want to email everyone, open the report and tap the link. That will put all the BCC addresses into the BCC field of a new email from your email program, and the user can then type out whatever subject and message they want before hitting SEND.
4. For help creating the list of email addresses,
   1. Do it in the SQL: I use GROUP_CONCAT functions found here: https://archive.codeplex.com/?p=groupconcat. (Either that or use COALESCE or ugly XML techniques). SQL Server 2017+ have the STRING_AGG() function that can be used. OR
   2. Do it in the REPORT: https://shakanand.wordpress.com/2012/07/05/ssrs-concatenating-value-from-detail-rows-into-a-single-value/

If you are trying to use the SMTP server already defined and in use within Viewpoint, you could use something like a WF Notifier Job to do the same thing (not tested):

1.  Create a UD table to store the messages. Example: "PR Company Messages"
   1. Fields needed: Seq, Subject, ReplyTo, Body
2. Create a WF Notifier Query to 
   1. Grab CONCATED list of all active employee emails (see above section on doing it in SQL)
   2. Grab rows from the PR Company Messages table
   3. Setup as "Event" query/job, with "key field" being Seq
3. Create a WF Notifier Job to use that query, sending an email to the CONCATED list of emails. Make it run once a day or so. It should only fire off when it detects a new row in the PR Company Messages table.
4. The Job should also be set with a valid REPLY-TO address.

Personally I'd rather use the SSRS report method as it requires a lot less setup and maintenance. The only advantage I can really see of the 2nd method is VP keeps a log of all the messages it sends, so anyone could check VA MESSAGE ARCHIVE to see if the messages were sent. Otherwise it's up to the sender to keep a copy in their SENT folder in their client.