December 13, 2006 at 2:49 am
Hi all,
Can any body tell me, how to use .xls file or .csv file as a attachment to the xp_sendmail procedure instead of .txt attachment (Is it possible???). I need it very urgently, since it is my customers requirement. If you can provide me any sample code for same, will be higly appreciated. I could not found the solution any where.
December 13, 2006 at 5:18 am
Have you tried using @attachments = 'x:\miDir\myExcelFile.xls'
Haven't used xp_sendmail in years, but I can't recall that it should only work for .txt files..?
/Kenneth
December 13, 2006 at 10:50 pm
Hi Kenneth
Thanks for the same.
My xp_sendmail generates the .TXT file by default using query output. I want redirect that output to either .xls or .csv instead of .txt. How do I do that????
December 13, 2006 at 11:03 pm
You can change your query to send the output to .xls...
Post the query...
MohammedU
Microsoft SQL Server MVP
December 13, 2006 at 11:58 pm
Hi Mohammed / Kenneth
My query is something like this which sends the output to .txt instead I want it in .xls or .csv
CREATE procedure mail @v_docno as char(10)
AS
declare @rec varchar(50)
declare @qry varchar(250)
declare @sub varchar(100)
declare @indno char(10)
declare @cc varchar(100)
declare @cc1 varchar(100)
declare @RGP char(4)
SET QUOTED_IDENTIFIER OFF
set @qry ="select b.c_itemcd, c.c_itemdesc,b.n_challan_qty
from commercial.dbo.t_grrhd a,commercial.dbo.t_grrdt b, commercial.dbo.t_itemmst c
where b.c_itemcd = c.c_itemcd
and a.c_docno = b.c_docno
and a.c_docno='"+ @v_docno+"'"
select @rec=(select c_indent_rgpno from t_grrhd where c_docno = @v_docno)
if @rec=''
select @rec='xyz@abc.com'
else
select @RGP =(select substring(c_indent_rgpno,1,4) from t_grrhd where c_docno = @v_docno)
if @RGP ='GRGP'
begin
select @rec= b.c_mailid from t_grrhd a, t_department b, t_rgphd c
where a.c_indent_rgpno=c.c_docno and
c.c_deptcd=b.c_doccode
and a.c_docno= @v_docno
select @cc1= b.c_ccmailid from t_grrhd a, t_department b, t_rgphd c
where a.c_indent_rgpno=c.c_docno and
c.c_deptcd=b.c_doccode
and a.c_docno= @v_docno
end
else
begin
select @rec= b.c_mailid from t_grrhd a, t_department b
where substring(a.c_indent_rgpno,1,4)=b.c_doccode
and a.c_docno= @v_docno
select @cc1= b.c_ccmailid from t_grrhd a, t_department b
where substring(a.c_indent_rgpno,1,4)=b.c_doccode
and a.c_docno= @v_docno
end
select @indno=c_indent_rgpno from t_grrhd where c_docno=@v_docno
SET QUOTED_IDENTIFIER ON
select @cc=@cc1
select @sub='This is an Auto Mail for' +@indno
EXEC master.dbo.xp_sendmail
@recipients= @rec,
@message=' ' ,
@subject=@sub,
@attach_results = 'TRUE', @width=2500,
@query=@qry,
@copy_recipients=@cc
GO
I think this will help you...
Please tell me where I will require to make the changes in above query for getting the output attachment in xls
December 14, 2006 at 5:15 am
Looks like you're not attaching any file at all, what you're doing is that you include the results of an inline query. (@attach_results)
If you desperatley need an Excel file(?) then I believe you have to create that 'outside', and then use @attachments parameter to attach it.
On the other hand, you could also educate your recipients of the mail, that it would take them probably 20 seconds to copy and paste your current results into Excel...
/Kenneth
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply