How to send an Email

  • Hi Experts,

    if I run one Query then I will get this result:

    Assume,

    select responsibleprovider,emailaddress from table1

    responsibleprovideremailaddress

    aaaa, bbbbbaaaar@uphs.upenn.edu

    ddddd, Ppppddddp@uphs.upenn.edu

    if I run another query I get this result:

    Assume,

    select Count ,responsibleprovider from tablereport

    Count responsibleprovider

    6aaaa, bbbbb

    8ddddd, Pppp

    I need to send this people email about the number of reports waiting for them (knowing their email address from the previous SQL):

    For example, contact Email will get email: You have 1 report

    aaaa, bbbbb will get email: You have 4 reports

    I am new to Mail functionality in SQL Server ,

    Could you please advice me , how to send an mail to repective preoviders with some text.

    Thanks!

  • Assuming you already have Database Mail setup with a default profile in place you could do something like this. Just replace the use of the CTEs I created to bring in test data with references to the real tables where your data resides.

    DECLARE @sql NVARCHAR(MAX) = N'';

    WITH cte(responsibleprovider, emailaddress)

    AS (

    SELECT 'aaaa, bbbbb',

    'aaaar@uphs.upenn.edu'

    UNION ALL

    SELECT 'ddddd, Pppp',

    'ddddp@uphs.upenn.edu'

    ),

    cte2(ReportCount, responsibleprovider)

    AS (

    SELECT 6,

    'aaaa, bbbbb'

    UNION ALL

    SELECT 8,

    'ddddd, Pppp'

    )

    SELECT @sql += 'EXEC msdb.dbo.sp_send_dbmail

    @recipients = ' + QUOTENAME(emailaddress, '''') + ',

    @subject = N''You have ' + CAST(reportcount AS NVARCHAR(11)) + ' report.'',

    @body = N''Say something else here...''

    '

    FROM cte

    JOIN cte2 ON cte.responsibleprovider = cte2.responsibleprovider;

    PRINT @sql;

    --EXEC(@sql);

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply