title: Sending email dynamically to multiple recipients with message using sp_send_dbmail
body: <p>Actually i am looking to send Birthday greetings to the members. The recipients list i am getting dynamically. With the below query i can send the greetings to the members.</p>
  
  <pre><code>DECLARE @emails VARCHAR(500)
  DECLARE @bodycontent VARCHAR(500)
  SET @emails = '' 
  SET @bodycontent = ''
  use dnname
  SELECT @emails = @emails + cm.PersonalEmail + ';' FROM tblIndividualMst  im
         inner join tblContactMst cm on cm.ContactID = im.ContactID
         where im.GroupID = 4673 and im.DateOfBirth = CONVERT(VARCHAR(10),GETDATE(),110)
  DECLARE @recipList VARCHAR(500)
  SET @recipList =  (select SUBSTRING(@emails, 0, LEN(@emails)))
  SELECT @bodycontent = 'Happy BirthDay to'+ ' ' + + @bodycontent + 
         im.FullName + '' FROM tblIndividualMst  im
         inner join tblContactMst cm on cm.ContactID = im.ContactID
         where im.GroupID = 4673 and im.DateOfBirth = CONVERT(VARCHAR(10),GETDATE(),110)
  use msdb
  EXEC sp_send_dbmail @profile_name='eMail Profile',
      @recipients=@recipList,
      @subject='Happy Birthday',
      @body=@bodycontent, 
      @body_format = 'text'
  </code></pre>
  
  <p>So, if @emails have multiple recipients, how can i send the message to each members. </p>
  
  <pre><code>DECLARE @emails VARCHAR(500)
  DECLARE @bodycontent VARCHAR(500)
  SET @emails = '' 
  SET @bodycontent = ''
  use dnname
  SELECT @emails = @emails + cm.PersonalEmail + ';' FROM tblIndividualMst  im
         inner join tblContactMst cm on cm.ContactID = im.ContactID
         where im.GroupID = 4673 and im.DateOfBirth = CONVERT(VARCHAR(10),GETDATE(),110)
  DECLARE @recipList VARCHAR(500)
  SET @recipList =  (select SUBSTRING(@emails, 0, LEN(@emails)))
  SELECT @Count = @Count + Count(cm.PersonalEmail) FROM tblIndividualMst  im
         inner join tblContactMst cm on cm.ContactID = im.ContactID
         where im.GroupID = 4673 and im.DateOfBirth = CONVERT(VARCHAR(10),GETDATE(),110)
  
  Declare @i int
  set @i = 0
  while @i &lt;= @Count 
    begin
  SELECT @bodycontent = 'Happy BirthDay to'+ ' ' + + @bodycontent + 
         im.FullName + '' FROM tblIndividualMst  im
         inner join tblContactMst cm on cm.ContactID = im.ContactID
         where im.GroupID = 4673 and im.DateOfBirth = CONVERT(VARCHAR(10),GETDATE(),110)
  use msdb
  EXEC sp_send_dbmail @profile_name='eMail Profile',
     @recipients=@recipList,
     @subject='Happy Birthday',
     @body=@bodycontent,  
     @body_format = 'text'
  end
  </code></pre>
  
  <p>So, how can i modify my query. Any help appreciated, thanks !!!</p>