SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Send email to different recipients


Send email to different recipients

Author
Message
sanmon_11
sanmon_11
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 37
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
sanmon_11
sanmon_11
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 37
Add the link for see the Query:



http://img200.imagevenue.com/img.php?image=889928666_Query1_122_432lo.JPG
Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73324 Visits: 40966
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!
sanmon_11
sanmon_11
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 37
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.[EMAIL]
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 2 jose.a.garcia@nidec-motor.com
766 JAIME SALAZAR CORDERO 2 jose.a.garcia@nidec-motor.com
10329 JAVIER BRISEÑO GARCIA 2 jose.a.garcia@nidec-motor.com
10342 FLORINA GOMEZ CEPEDA 1 osvaldo.santander@nidec-motor.com
10352 SANTIAGO FIDENCIO ZUÑIGA HERNANDEZ 1 osvaldo.santander@nidec-motor.com
10355 MARIA TERESA CERVANTES GUTIERREZ 1 osvaldo.santander@nidec-motor.com
10364 EDUARDO RIVERA CRUZ 1 osvaldo.santander@nidec-motor.com
10372 ALVARO MANUEL MARTINEZ LOMAS 1 osvaldo.santander@nidec-motor.com
10384 ALBERTO FERNANDO DELGADO LOPEZ 1 osvaldo.santander@nidec-motor.com
10434 SERGIO ISABEL PERALES HERNANDEZ 1 osvaldo.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??
Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73324 Visits: 40966
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.[EMAIL]
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.[EMAIL],
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.[EMAIL] = c.[EMAIL]
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 [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!
sanmon_11
sanmon_11
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 37
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?
Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73324 Visits: 40966
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!
sanmon_11
sanmon_11
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 37
Well, the Query return two records, all records of the table before but concatenated.

but I don't know if there help me!!!
Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73324 Visits: 40966
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!
sanmon_11
sanmon_11
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 37
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??
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search