CODEWITHSUNDEEP
sql-servert-sql
user9017400
user9017400

Reputation: 35

Group Department and send Email using DBMail

I need help in sending notification using DBMAIL In SQL Server

Send email to department with respective department information. For example Email notification to [email protected] should include only IT department information should not include Sales department information. And [email protected] has two records so both should be combined as one email rather multiple emails for each record in the table. ## 

Expected Output : "[email protected]" should receive Department "IT" and Name ( A, B)
            "[email protected]" should receive Department "Sales" and Name(C)
            "[email protected]" should receive Department "Service" and Name (D)

Create table #temp( ID Int Identity(1,1), Name varchar(50), Department varchar(25), Email varchar(1000))

insert into #temp ( Name , department, Email)
    Values('A','IT','[email protected]'),
    ('B','Sales','[email protected]'),
    ('C','Testing','[email protected]'),
    ('D','Service','[email protected]')

    DECLARE @xml NVARCHAR(MAX)
    DECLARE @body NVARCHAR(MAX)

    SET @xml = CAST(( SELECT Name AS 'td','',Department AS 'td'
    FROM  #Temp 
    FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

    SET @body ='<html><body><H3>Email notification to respective department</H3>
    <table border = 1> 
    <tr>
    <th> Name </th> <th> Department </th> </tr>'    

    SET @body = @body + @xml +'</table></body></html>'
    declare @emaillist varchar(max) 

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Notification', 
    @body = @body,
    @body_format ='HTML',
    @recipients = @emaillist,
    @subject = 'E-mail in Tabular Format'

Upvotes: 0

Views: 602

Answers (1)

Soukai
Soukai

Reputation: 463

Since your goal is to send multiple emails, and each email will require a function call, it looks like you might need a loop. Here's the skeleton of a loop that could help in this situation:

-- Pull email addresses into a table so that we can loop through each one:
DECLARE @Emails TABLE (
    ID INT IDENTITY(1, 1),
    Email VARCHAR(1000)
);

INSERT INTO @Emails (Email)
    SELECT DISTINCT Email
    FROM #temp;

-- Go through each email address, build the body, and send the email:
DECLARE @curID INT = 1;
DECLARE @maxID INT = (SELECT MAX(ID) FROM @Emails);
DECLARE @curEmail VARCHAR(1000);

WHILE @curID <= @maxID BEGIN

    SET @curEmail = (SELECT Email FROM @Emails WHERE ID = @curID);

    -- Insert code to build email body for the current email address and send email here

    SET @curID += 1;
END

You should be able to use code very similar to the example you provided to build the body and send the email itself. Just make sure to restrict the XML query to a single email address for each loop iteration.

Upvotes: 1

Related Questions