• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 105
  • Last Modified:

how to send email to multiple users using sp_send_dbmail

I am trying to send an email to mulitiple users but in the email body, i want to mention each user's name and say something like this:
Dear Mike, and then the rest of the message follows
I have already declared the user's name but don't know how to reference it in the email body.
here is what i have now:
declare rscursor cursor read_only
for 
 select name, location, email from myTable

    declare @Emails   nvarchar (100)
	declare @name  nvarchar (100)
	declare @location  nvarchar (50)
	declare @email  nvarchar (50)
	DECLARE @EmailBody  VARCHAR(1000)
   set @EmailBody = N'<H1>Dear put name here:</H1>' +
   N'this is test' +
			N'<br>Thank You' +
                      
open rscursor
fetch next from rscursor into @name, @location, @email

while @@fetch_status=0
    begin

         EXEC msdb.dbo.sp_send_dbmail
        @recipients = @Emails, 
        @subject = 'Sleep Diary Reminder Shift Sleep',

        @body = @EmailBody ,
        @profile_name = 'msge',
		@body_format = 'HTML',

        @attach_query_result_as_file = 0      

    fetch next from rscursor into @name, @location, @email
	
end
close rscursor
deallocate rscursor

Open in new window

0
moe57
Asked:
moe57
1 Solution
 
Bijay DeoGraduate Student Research AssistantCommented:
Here is how:
Declare 2 variables as below
   set @EmailBodyPart1 = N'<H1>Dear'
    set @EmailBodyPart2 = ':</H1>' +
   N'this is test' +
                  N'<br>Thank You' +

And in the body assignment,
@body = @EmailBodyPart1 + @name +  @EmailBodyPart2

The syntax is not checked with compiler, but you get the idea. Test it and use.

If satisfied, please mark the solution as accepted.
0
 
HuaMin ChenSystem AnalystCommented:
Hi,
You need to use a loop to repeatedly fetch cursor records into a list of variables, like this example
DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name 

SET @path = 'C:\Backup\'  

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM MASTER.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  

       FETCH NEXT FROM db_cursor INTO @name   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

Open in new window


After this, you can then put the name into the mail body like
set @EmailBody = N'<H1>Dear '+@name+' :</H1>' +
...

Open in new window

0
 
David ToddSenior DBACommented:
Hi,

To do that - referencing the user in the body of the email - you cant send one email to multiple users. You need to send multiple emails to individual users, as per the above comments and using a loop.

Regards
  David
0
 
moe57Author Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now