Need help formatting xp_sendmail output

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need help formatting xp_sendmail output

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-02-28 : 15:09:02

Thanks to jhocutt it's working but I don't like how the output displays the info in the email. Is there a way for me to format it: This is how it's coming out.

Is there a way to format
Requestor
Requested Shipped
Due
------------------------------------------------------------------------------
-----------------------------------------------------------------------
-----------------------------------------------------------------------
-----------------------------------
-----------------------------------------------------------------------
---- ------------------------------ ------------------------------
------------------------------
Approaching the Corporate Heart

pc@yahoo.com
02/07/2008 02/07/2008
02/06/2008
PASSION FOR CUSTOMERS 3 copies

pc@yahoo.com
02/07/2008 02/07/2008
02/06/2008
PASSION FOR CUSTOMERS 3 copies

Here's the code:
declare @requestoremail varchar(75), @myquery VARCHAR(4000)

Declare GetEmails cursor for
select requestors.requestoremail --selecting person to send email too
from spdtlibrary.dbo.libraryrequest as libraryrequest
join spdtlibrary.dbo.requestors as requestors on requestors.requestorid=libraryrequest.requestorid
join spdtlibrary.dbo.Titles as titles on Titles.Titleid = Libraryrequest.titleid
WHERE returndate is null and duedate < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP) - 7, 0)
OPEN GetEmails
FETCH NEXT FROM GetEmails INTO @requestoremail
WHILE @@FETCH_STATUS >= 0
BEGIN
Print @requestoremail
set @myquery = 'select Titles.Title), requestors.requestoremail as Requestor, convert(varchar,libraryrequest.requestdate,101)as Requested,convert(varchar,libraryrequest.shipdate,101)as Shipped,convert(varchar,libraryrequest.duedate,101)as Due
from spdtlibrary.dbo.libraryrequest as libraryrequest
join spdtlibrary.dbo.requestors as requestors on requestors.requestorid=libraryrequest.requestorid
join spdtlibrary.dbo.Titles as titles on Titles.Titleid = Libraryrequest.titleid
WHERE returndate is null and duedate < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP) - 7, 0) and requestoremail= ''' + @requestoremail + ''''

execute master..xp_sendmail
@recipients = @requestoremail,
@subject = 'Over Due Resource Library Item',
@message = 'Our records indicate that the item you recently borrowed from ETDTs Resource Library has not yet been returned. Please FEDEX it to the address below as soon as possible so that it is available for other employees to utilize. Please place a post-it note on the item to identify yourself as the requestor. (Please check with your manager about FEDEX mailing procedures in your office.) Thank you.

SSA
Regional Training Facility
444 North 3rd St.
4th Floor
Philadelphia, PA 19123
Attention: Tonia Porter',
@query = @myquery

FETCH NEXT FROM GetEmails INTO @requestoremail;
END
close GetEmails
deallocate GetEmails

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-28 : 15:45:28

You can't really format the email body with xp_sendmail. What I do instead is attach my data that has been formatted in the query.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-02-29 : 15:06:00

Bummer...thanks anyway.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources