SSRS 2017 Open Outlook email with multiple email addresses by clicking on a report field

Question

I'm having trouble getting more than one email address into an Outlook Email. I have set the Action "Go to URL" Text Box Properties field in a report as follows:-

="javascript:void(window.open('mailto:" + First(Fields!SUPP_EMAIL.Value, "SUPP_Email")+"'))"

The Dataset is filtered according to the Parameters in the main table. I've tried several means of getting more than one Email address into the Email window, but each time I only get the first in the list, which sort of makes sense as the Function says First! I picked the javascript suggestion up from another post on MSDN from a user who claimed that with this solution they were able to get all addresses from a filtered Dataset. The idea is that once the report has been filtered, a user can click on the/a field designated with the "Go to URL" expression and an Outlook email window will open with all the email addresses in the filtered list in the To Box. If anyone has any suggestions, I would be most grateful.

Can you please post the SQL for the report? There is a way to concatenate the values for one column to one line. — aduguid, Nov 06 '20 at 21:24
Hi there, sql for the email addresses is pretty much, and I hope not too simplistic, SELECT lasps.LA_SERVICE_PROVIDER_ID, lasps.PROVIDER_EMAIL FROM la_service_provider_service lasps — NJC59, Nov 09 '20 at 09:59

score 0 · Answer 1 · answered Nov 06 '20 at 22:52

Depending on how many email addresses you have you might hit a limit. See this answer for more details on that....

Emailing to multiple recipients with html Mailto: not working

However, assuming your dataset is quite small you could do this....

Create a new parameter (e.g. pEmail) and make it multi-value. We will hide this parameter later...

Next, the both the Available Values and Default Value for the parameter to your main dataset and use the SUPP_EMAIL field as the value.

On your URL expression use the following

="javascript:void(window.open('mailto:" 
  + JOIN(Parameters!pEmail.Value, ";")
  + "'))"

Once it working, you can set the parameter to hidden.

I've used ; as the delimiter as you said it was for outlook, Be aware that Outlook is almost unique in that respect and most mail clients use a comma not a semi-colon.

Having said all that, this would be easier to do in SQL and build a single row result set containing the URL built ready for use in your report.

Hi Alan, Many thanks for your answer as it has resolved the problem I was having. I now just have to work out a means of beating the limit on the amount of email addresses you can have in the "To" field. The user can filter the output in order to use smaller datasets, but I know they are going top come back and ask to output all at some point! I will investigate in due course how we might be able to use Groups. Unfortunately, because the email addresses are all external, this might not be possible. — NJC59, Nov 09 '20 at 09:39
I'm not sure you can beat that limit. You could maybe have a textbox containing all the email addresses separated with a semi-colon and ask the users to copy this into the email recipient list manually. If you are happy that the original answer solved your issue, could you please mark the answer as accepted.. Thanks — Alan Schofield, Nov 09 '20 at 12:58

score 0 · Accepted Answer · answered Nov 09 '20 at 23:06

You could concatenate the values in the source of the report.

Example SQL:

WITH
la_service_provider_service
AS
(
   SELECT tbl.* FROM (VALUES
    ( 1, '1@gmail.com')
   , ( 1, '2@gmail.com')
   , ( 1, '3@gmail.com')
   , ( 2, '4@gmail.com')
   , ( 2, '5@gmail.com')
   , ( 2, '6@gmail.com')
   , ( 2, '7@gmail.com')
   ) tbl ([LA_SERVICE_PROVIDER_ID], [PROVIDER_EMAIL]) 
)
SELECT 
    [lasps].[LA_SERVICE_PROVIDER_ID]
   , [lasps].[PROVIDER_EMAIL]
   , [ALL_PROVIDER_EMAILS] = 
     STUFF( (SELECT '; '+ [PROVIDER_EMAIL] 
           FROM [la_service_provider_service] AS [lasps2]
           WHERE [lasps2].[LA_SERVICE_PROVIDER_ID] = [lasps].[LA_SERVICE_PROVIDER_ID]
           FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(2000)')
        ,1,2,'')
FROM 
   [la_service_provider_service] AS [lasps];

SSRS 2017 Open Outlook email with multiple email addresses by clicking on a report field

2 Answers2

Example SQL:

Results: