How to send Image with sp_send_dbmail in SQL SERVER
create trigger trgJobSeeker
on JobSeeker
for insert
as
begin
if object_id('tempdb..#JobSeeker') is not null
begin
drop table #JobSeeker
end
declare @Email varchar(100),@name varchar(50)
declare @Body varchar(max),@tableHTML varchar(max),@tableHTML1 varchar(max)
select substring(email,1,charindex('@',email)-1) as Name,EMail into #JobSeeker from inserted
select @Email=email, @name=Name from #JobSeeker
select @Body=Name from #JobSeeker
/*
To Admin
*/
SET @tableHTML =
N' <img src="https://lh3.googleusercontent.com/-gMAaKtKOWyw/T0szCDAN-iI/AAAAAAAACP0/laJwA32vGus/w689-h685-no/suri.jpg" alt="" align="right" />
<div style="clear: right">'+N'
<p > Catherine Wehage, </p>
<p > </p>
<p style=" text-indent: 5em;">'+ @name+' has expressed interest online about a Volunteer position with me.</p>
<p > </p>
<p > If there are any positions open:</p>
<p style=" text-indent: 5em;"> 1. </p>
<p style=" text-indent: 5em;"> 2. </p>
<p style=" text-indent: 5em;"> 3. </p>
<p style=" text-indent: 5em;"> 4. procedure</p>
<p > </p>
<p >Not anything:</p>
<p style=" text-indent: 5em;"> 1. Inform them </p>
<p style=" text-indent: 5em;"> 2. Ask to keep name </p>
<p style=" text-indent: 5em;"> 3. Inform them o</p>
<p> </p>
<p>Thanks,</p>
<p>'+@Body+'</p>
</p> '
/* to Candidate */
set @tableHTML1=N' <img src="https://lh3.googleusercontent.com/-gMAaKtKOWyw/T0szCDAN-iI/AAAAAAAACP0/laJwA32vGus/w689-h685-no/suri.jpg" alt="" align="right" />
<div style="clear: right">'+N'
<p > Dear '+@Body+', </p>
<p > </p>
<p style=" text-indent: 5em;">Thank you for your interest --- team! </p>
<p > </p>
<p > abcdefghij. </p>
<p > </p>
<p >Enter your test here!</p>
<p> </p>
<p>Sincerely, </p>
<p> </p>
<p>Vagalla Suresh</p>
<p> Author</p>
</p> '
/*
To Admin
*/
exec msdb.dbo.sp_send_dbmail
@profile_name='DLM-Mail',
@recipients='vagalla.suresh@gmail.com',
@subject='New Candidate Registered successfully',
@body=@tableHTML,
@body_format = 'HTML'
/* to Candidate */
exec msdb.dbo.sp_send_dbmail
@profile_name='DLM-Mail',
@recipients='vagalla.suresh@gmail.com',
@subject='Registered successfully',
@body=@tableHTML1,
@body_format = 'HTML'
end
--truncate table JobSeeker
--insert into JobSeeker(Email,IsActive) values('vagalla.suresh@gmail.com',1)
For table format
GO
/****** Object: Trigger [dbo].[trgStudentinsert] Script Date: 04-09-2014 11:02:34 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trgStudentinsert]
ON [dbo].[Student]
after UPDATE
AS
SET nocount ON
DECLARE @email VARCHAR(255),
@ordnbr VARCHAR(15),
@sql VARCHAR(max)
DECLARE @xml NVARCHAR(max)
DECLARE @body NVARCHAR(max)
DECLARE @ParentName VARCHAR(50)
DECLARE @Slot VARCHAR(5000)
IF UPDATE(interviewhistoryid)
BEGIN
IF Object_id('tempdb..#TempStudent') IS NOT NULL
BEGIN
DROP TABLE #tempstudent
END
IF Object_id('tempdb..#temp123') IS NOT NULL
BEGIN
DROP TABLE #temp123
END
IF EXISTS(SELECT 1
FROM inserted I
JOIN deleted D
ON I.studentid = D.studentid
AND I.interviewhistoryid <>
D.interviewhistoryid)
BEGIN
SELECT b. *
INTO #tempstudent
FROM student a
INNER JOIN inserted b
ON a.studentid = b.studentid
SELECT p.title + ' ' + P.firstname + ' '
+ Isnull(p.lastname, '') AS ParentName,
p.email AS ParentEmail
,
s.firstname + ' ' + Isnull(s.lastname, '') AS StudentName,
s.email AS StudentEmail,
' '
+ CONVERT(VARCHAR(20), Format (ih.date, 'MMM dd yyyy'))
+ ' between ' + ivs.scheduletime AS ScheduledOn
INTO #temp123
FROM #tempstudent S
INNER JOIN interviewhistory IH
ON s.interviewhistoryid = IH.interviewhistoryid
INNER JOIN interviewslot IVS
ON IVS.scheduleid = IH.scheduleid
INNER JOIN parent P
ON p.studentid = s.studentid
DECLARE @tableHTML NVARCHAR(max);
DECLARE @Parent VARCHAR(50),
@Student VARCHAR(50),
@ScheduledOn VARCHAR(max)
SELECT @Parent = parentname,
@Student = studentname,
@ScheduledOn = scheduledon
FROM #temp123
SELECT @email = ParentEmail+';' + studentemail
FROM #temp123
SET @tableHTML =
N'
<head>
<STYLE TYPE="text/css">
p.title{font-family:arial;font-size:12pt;color:RED;font:bold}
th{font-family:arial;font-size:10pt;color:white;background-color:blue;text-align:Centre;padding-left:5px;padding-right:5px}
.data{font-family:arial;font-size:8pt;color:black;background-color:white;border:1px solid black;text-align:left;padding-left:5px;padding-right:5px}
.datara{font-family:arial;font-size:8pt;color:black;background-color:white;border:1px solid black;text-align:right;padding-left:5px;padding-right:5px}
</STYLE>
</head>
<img src="https://lh3.googleusercontent.com/-gMAaKtKOWyw/T0szCDAN-iI/AAAAAAAACP0/laJwA32vGus/w689-h685-no/suri.jpg" alt="" align="right" />
<div style="clear: right">
<p > Hello '
+ @Parent
+ ', </p>
<p > </p>
<p > Thank you for applying your child '
+ @Student
+
' to vagalla's Program. We look forward to meeting you and your family at the online on '
+ @ScheduledOn
+ '</p>
<p > In the meantime, if you have any questions, please contact me Basketball, vagalla@mail.com or---, vagalla@program.org. </p>
<p class="title"> </p>
<p class="title"> Interview Schedule </p>
<table>
<table border = 1>
<tr>
<th>ParentName</th>
<th>ParentEmail</th>
<th>StudentName</th>
<th>StudentEmail</th>
<th>ScheduledOn</th>
</tr>'
+ Cast ( ( SELECT [td/@class]='data', td = parentname, "*"='',
[td/@class]='data', td = parentemail, "*"='', [td/@class]=
'data', td = studentname, "*"='', [td/@class]='data', td =
studentemail, "*"='', [td/@class]='data', td = scheduledon FROM
#temp123 FOR xml path('tr'), type ) AS NVARCHAR(max) )
+ N'</table>
<p > Thanks, </p> <p > Vagalla Team</p> </div>'
EXEC msdb.dbo.Sp_send_dbmail
@Profile_name='DLM-Mail',
@recipients=@email,
@subject = 'Slot Booked for the Interview',
@body = @tableHTML,
@body_format = 'HTML'
END
END
No comments:
Post a Comment