Database Mail: sending to multiple recipients from tables

• Hafiz Umar

  Old Hand

  Points: 355

  More actions

  January 10, 2007 at 9:52 pm

  #92163

  I am using database mail to send emails to our Lotus Notes SMTP server using sp_send_dbmail. I want to accomplish the following.

  I have maintained department-wise users email address in one table .  Now I want to send mail to one particular department and there can be 1-15 users as recipient for that mail.  How can I do that using sp_send_dbmail?

• MohammedU

  SSCoach

  Points: 19644

  More actions

  January 11, 2007 at 2:02 pm

  #682773

  You have to generate the ';' seperated list ...here is example...

  declare

  @i varchar(1000)

  select

  @i = ''

  select

  @i = @i+';'+name from sysobjects where id <10

  select

  @i

  MohammedU
  Microsoft SQL Server MVP

• Hafiz Umar

  Old Hand

  Points: 355

  More actions

  January 14, 2007 at 9:23 pm

  #683147

  DECLARE @email VARCHAR(4000)

  SET @email = ''

  SELECT @email = RTRIM(@email) + RTRIM(email) + ';'

  FROM Users

  WHERE email <> '' AND DepCode = 'A'

  execute sp_send_dbmail --

  --setting @recipient to @email

  @recipient = @email

  Thanx for help

• MohammedU

  SSCoach

  Points: 19644

  More actions

  January 14, 2007 at 9:52 pm

  #683148

  You got it..

  If your email id datatype is varchar then you don't need to use RTRIM function...

   

  MohammedU
  Microsoft SQL Server MVP

• colin Robinson-345240

  Mr or Mrs. 500

  Points: 567

  More actions

  March 19, 2007 at 3:02 pm

  #695817

  I went for an interview today and they explaind their current issue with sql2005 as a problem getting sql2005 to mail their lotus notes users. I suggested xp sendmail with a caveat it may not support 2005.

  But i would be interested how to set up win 2003 to use sp_send_dbmail im presming the outlook client is still required?

   

  Cheers

• Marvin D

  SSCarpal Tunnel

  Points: 4058

  More actions

  March 23, 2007 at 1:45 pm

  #696703

  There is no longer a need for an email client when using sp_send_dbmail with SQL Server 2005.  There is also a way to use the CDO object in SQL 2000 that eliminates the email client as well.  Let me know and I'll send you a stored proc for that one.

   

   

  Marvin Dillard
  Senior Consultant
  Claraview Inc