Send email to different recipients

  • Well, I need through of a Query send Email to different recipients.

    in the link below, show the Query

    http://img200.imagevenue.com/img.php?image=889928666_Query1_122_432lo.JPG

    in the image below show as I wanted to send the email

    http://img200.imagevenue.com/img.php?image=889544477_result1_122_260lo.JPG

    Please, could you help me. because I did try several ways to do as with cursor and While Loop but I get the result.

    I managed to be sent to different recipients but can not get the variable @ body aceopte me to send multiple records in the body of the Email

    thanks in advance

  • Add the link for see the Query:

    http://img200.imagevenue.com/img.php?image=889928666_Query1_122_432lo.JPG

  • your image is blocked at my work, some filesharing violation according to our IT guys......can you paste the code you are referencing instead of an image?

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • ok this is my Query:

    SELECT T3.NoEmpleado AS 'No.Empleado', LTRIM(RTRIM(T3.NombreEmpleado)) + ' ' + LTRIM(RTRIM(T3.ApellidoPaterno)) + ' ' + LTRIM(RTRIM(T3.ApellidoMaterno)) AS 'Nombre Empleado',

    T2.IDArea, T2.

    FROM dbo.tblCursoArea T1

    INNER JOIN tblArea T2 ON T2.IDAREA = T1.IDAREA

    INNER JOIN tblEmpleados T3 ON T3.IdArea = T2.IDArea

    WHERE T1.PROGRAMADO = 1

    The Result is:

    719 JOSE ASUNCION FIGUEROA ESTRADA 2jose.a.garcia@nidec-motor.com

    766 JAIME SALAZAR CORDERO 2jose.a.garcia@nidec-motor.com

    10329JAVIER BRISEÑO GARCIA 2jose.a.garcia@nidec-motor.com

    10342FLORINA GOMEZ CEPEDA 1osvaldo.santander@nidec-motor.com

    10352SANTIAGO FIDENCIO ZUÑIGA HERNANDEZ 1osvaldo.santander@nidec-motor.com

    10355MARIA TERESA CERVANTES GUTIERREZ 1osvaldo.santander@nidec-motor.com

    10364EDUARDO RIVERA CRUZ 1osvaldo.santander@nidec-motor.com

    10372ALVARO MANUEL MARTINEZ LOMAS 1osvaldo.santander@nidec-motor.com

    10384ALBERTO FERNANDO DELGADO LOPEZ 1osvaldo.santander@nidec-motor.com

    10434SERGIO ISABEL PERALES HERNANDEZ 1osvaldo.santander@nidec-motor.com

    I need send by email the next:

    in one email:

    to: osvaldo.santander@nidec-motor.com

    Subject : 'TEST',

    Body:

    FLORINA GOMEZ CEPEDA

    SANTIAGO FIDENCIO ZUÑIGA HERNANDEZ

    MARIA TERESA CERVANTES GUTIERREZ

    EDUARDO RIVERA CRUZ

    ALVARO MANUEL MARTINEZ LOMAS

    ALBERTO FERNANDO DELGADO LOPEZ

    SERGIO ISABEL PERALES HERNANDEZ

    in the Other email:

    to: jose.a.garcia@nidec-motor.com

    Subject : 'TEST',

    Body:

    JOSE ASUNCION FIGUEROA ESTRADA

    JAIME SALAZAR CORDERO

    JAVIER BRISEÑO GARCIA

    How can I make this??

  • this one is tough, because i don't have the tables to test agaisnt.

    this MIGHT return the email/string you want to send.

    if it does, then this query is what you would use as the cursor to send individual emails.

    does this return anything liek what you were thinking of sending?

    WITH CTE AS

    (

    SELECT

    DISTINCT T2.

    FROM dbo.tblCursoArea T1

    INNER JOIN tblArea T2

    ON T2.IDAREA = T1.IDAREA

    INNER JOIN tblEmpleados T3

    ON T3.IdArea = T2.IDArea

    WHERE T1.PROGRAMADO = 1

    )

    SELECT T2.,

    Notes = STUFF((

    SELECT CHAR(13) + CHAR(10)

    + LTRIM(RTRIM(T3.NombreEmpleado))

    + ' ' + LTRIM(RTRIM(T3.ApellidoPaterno))

    + ' '

    + LTRIM(RTRIM(T3.ApellidoMaterno))

    FROM dbo.tblCursoArea T1

    INNER JOIN tblArea T2

    ON T2.IDAREA = T1.IDAREA

    INNER JOIN tblEmpleados T3

    ON T3.IdArea = T2.IDArea

    WHERE ns. = c.

    ORDER BY ns.NoteSeq

    FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,2,'') --STUFF removes the preceeding CrLf/CHAR13/10

    FROM CTE c

    ORDER BY ;

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • my friend, I get the next error

    Msg 4104, Level 16, State 1, Line 12

    The multi-part identifier "T2.EMAIL" could not be bound.

    Msg 4104, Level 16, State 1, Line 24

    The multi-part identifier "Notes.EMAIL" could not be bound.

    Msg 4104, Level 16, State 1, Line 25

    The multi-part identifier "Notes.NoteSeq" could not be bound.

    Do you know why?

  • i was modifying an example named "notes" to match your data, but without the actual tables, I could not test it for valid objects.

    if you can fix the syntax issues...notes and T2 are probably the "c" alias...., it should produce a string and an email.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Well, the Query return two records, all records of the table before but concatenated.

    but I don't know if there help me!!!

  • well now you have the desired contents for the email; now it's just a cursor based on that query:

    here's part two of the example, where you send individualized emails:

    DECLARE

    @isql VARCHAR(2000),

    @email VARCHAR(64),

    @notes VARCHAR(max)

    DECLARE c1 CURSOR FOR SELECT Email,Notes From OurBigger_Query_with_FORXML

    OPEN c1

    FETCH NEXT FROM c1 INTO @email,@notes

    WHILE @@FETCH_STATUS <> -1

    BEGIN

    declare @body1 varchar(4000)

    set @body1 = 'This is a Simple Email Example generated on ' + CONVERT( VARCHAR( 20 ), GETDATE(), 113 ) +

    ' to demonstrate sending a basic notification' + @notes

    --this assumes a profile was set as "default", so i don't have to explicitly specify which one to use. else you get this error:

    /*

    Msg 14636, Level 16, State 1, Procedure sp_send_dbmail, Line 112

    No global profile is configured. Specify a profile name in the @profile_name parameter.

    */

    EXEC msdb.dbo.sp_send_dbmail

    --@profile_name='Stormrage DBMail',

    @recipients=@email,

    @subject = 'Simple Email Example',

    @body = @body1,

    @body_format = 'HTML'

    --@body_format = 'TEXT'

    FETCH NEXT FROM c1 INTO @email,@notes

    END

    CLOSE c1

    DEALLOCATE c1

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • ok my friend,

    Make sure you know that the NOTE field that returns records, repeat the result

    Like this:

    JOSE ASUNCION FIGUEROA ESTRADA JOSE ASUNCION FIGUEROA ESTRADA JOSE ASUNCION FIGUEROA ESTRADA JOSE ASUNCION FIGUEROA ESTRADA JOSE ASUNCION FIGUEROA ESTRADA JOSE ASUNCION FIGUEROA ESTRADA JOSE ASUNCION FIGUEROA ESTRADA JOSE ASUNCION FIGUEROA ESTRADA JOSE ASUNCION FIGUEROA ESTRADA JOSE ASUNCION FIGUEROA ESTRADA JOSE ASUNCION FIGUEROA ESTRADA JOSE ASUNCION FIGUEROA ESTRADA JOSE ASUNCION FIGUEROA ESTRADA JOSE ASUNCION FIGUEROA ESTRADA JOSE ASUNCION FIGUEROA ESTRADA JAIME SALAZAR CORDERO JAIME SALAZAR CORDERO JAIME SALAZAR CORDERO JAIME SALAZAR CORDERO JAIME SALAZAR CORDERO JAIME SALAZAR CORDERO JAIME SALAZAR CORDERO JAIME SALAZAR CORDERO JAIME SALAZAR CORDERO JAIME SALAZAR CORDERO JAIME SALAZAR CORDERO JAIME SALAZAR CORDERO JAIME SALAZAR CORDERO JAIME SALAZAR CORDERO JAIME SALAZAR CORDERO JAVIER BRISEÑO GARCIA JAVIER BRISEÑO GARCIA JAVIER BRISEÑO GARCIA JAVIER BRISEÑO GARCIA JAVIER BRISEÑO GARCIA JAVIER BRISEÑO GARCIA JAVIER BRISEÑO GARCIA JAVIER BRISEÑO GARCIA JAVIER BRISEÑO GARCIA JAVIER BRISEÑO GARCIA JAVIER BRISEÑO GARCIA JAVIER BRISEÑO GARCIA JAVIER BRISEÑO GARCIA JAVIER BRISEÑO GARCIA JAVIER BRISEÑO GARCIA

    Do you know why??

  • change your query to select distinct email, notes.

    when you merge them with FOR XML, you often have duplicates.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • My friend,

    Now it's very goood,

    Do you know as to show result of the NOTE field vertically in the Email Body?

    for example as this one:

    JOSE ASUNCION FIGUEROA ESTRADA

    JAIME SALAZAR CORDERO

    JAVIER BRISEÑO GARCIA

    Thank so much!!!

  • come on, you should know this.

    html does not respect whitespace!

    find and replace char(13)(char10) with <br />, and isnert <br /> wherever else you want new lines to appear.

    sanmon_11 (10/10/2012)


    My friend,

    Now it's very goood,

    Do you know as to show result of the NOTE field vertically in the Email Body?

    for example as this one:

    JOSE ASUNCION FIGUEROA ESTRADA

    JAIME SALAZAR CORDERO

    JAVIER BRISEÑO GARCIA

    Thank so much!!!

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • ok my friend, 🙂

    thank so much for your time and support

    excellent contribution!!!

Viewing 14 posts - 1 through 13 (of 13 total)

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