topic Re: spool excel and mail.. in Application Development and Automation Discussions

spool excel and mail..

Former Member — Thu, 04 Oct 2007 05:23:23 GMT

Hi guys..

I am executing a big report in bachgroud and converting the spool into PDF using FM 'CONVERT_ABAPSPOOLJOB_2_PDF' and then emailing it as attachment..

1. Its truncated - i am not getting all the columns in PDF file.. can you pls tell me how to get the full report into PDF attachment.

2. Otherwise , can any one tell me how to get the excel attachment (complete report into excel) instead of PDF attachment and then mail it..

Thanks a lot in advance..

Re: spool excel and mail..

Former Member — Thu, 04 Oct 2007 05:24:38 GMT

Hi,

Please refer this sample program:

http://www.sapdevelopment.co.uk/reporting/rep_spooltopdf.htm

Best regards,

Prashant

Re: spool excel and mail..

Former Member — Thu, 04 Oct 2007 05:26:06 GMT

there is a function module for conerting data into Excel file

WS_EXCEL

u can use this one and after that mail it in attachment....

reward points if useful...

Re: spool excel and mail..

Former Member — Thu, 04 Oct 2007 05:42:44 GMT

Hey Prashant,

i have used the exact code for converting the PDF from spool and then mailing it as attachment. But the PDF attachment file is truncated and i am not getting most of the columns. Its displaying upto 6-7 column and truncating rest of the column in right side...

this is the exact problem .. its still pending..

Re: spool excel and mail..

Former Member — Thu, 04 Oct 2007 05:45:24 GMT

Hi SUjoy,

I think your spool itself is truncated bcz it would be exceeding 255 characters.

Could you please check spool in SP01 & Confirm.

If spool is fine, then please check whether you are converting the spool as follows:

<b>* Transfer the 132-long strings to 255-long strings</b>

LOOP AT it_pdf_output.
    TRANSLATE it_pdf_output USING ' ~'.
    CONCATENATE gd_buffer it_pdf_output INTO gd_buffer.
  ENDLOOP.

  TRANSLATE gd_buffer USING '~ '.

  DO.
    it_mess_att = gd_buffer.
    APPEND it_mess_att.
    SHIFT gd_buffer LEFT BY 255 PLACES.
    IF gd_buffer IS INITIAL.
      EXIT.
    ENDIF.
  ENDDO.

Best regards,

Prashant

Re: spool excel and mail..

Former Member — Thu, 04 Oct 2007 06:01:18 GMT

yes prashant i have even written the below peice of code..but i am not getting the full size report..

here is my code:

FORM conv_to_pdf .

IF sy-batch = 'X'.

PERFORM get_job_details.

PERFORM obtain_spool_id.

PERFORM conv_spool_to_pdf.

ELSE.

LEAVE LIST-PROCESSING.

ENDIF.

IF p_delspl EQ 'X'.

PERFORM delete_spool.

ENDIF.

ENDFORM. " conv_to_pdf

&----

*& Form get_job_details

&----

Form to get Job details

----

FORM get_job_details .

Get current job details

CALL FUNCTION 'GET_JOB_RUNTIME_INFO'

IMPORTING

eventid = v_eventid

eventparm = v_eventparm

external_program_active = v_external_program_active

jobcount = v_jobcount

jobname = v_jobname

stepcount = v_stepcount

EXCEPTIONS

no_runtime_info = 1

OTHERS = 2.

ENDFORM. " get_job_details

&----

*& Form obtain_spool_id

&----

Form to Obtain Spool Id

----

FORM obtain_spool_id .

CHECK NOT ( v_jobname IS INITIAL ).

CHECK NOT ( v_jobcount IS INITIAL ).

SELECT * FROM tbtcp

INTO TABLE t_tbtcp

WHERE jobname = v_jobname

AND jobcount = v_jobcount

AND stepcount = v_stepcount

AND listident <> '0000000000'

ORDER BY jobname

jobcount

stepcount.

READ TABLE t_tbtcp INTO w_tbtcp INDEX 1.

IF sy-subrc = 0.

v_spool_nr = w_tbtcp-listident.

ENDIF.

ENDFORM. " obtain_spool_id

&----

*& Form conv_spool_to_pdf

&----

Form to Convert the spool to PDF form

----

FORM conv_spool_to_pdf .

***********************************************************************

CALL FUNCTION 'CONVERT_ABAPSPOOLJOB_2_PDF'

EXPORTING

src_spoolid = v_spool_nr

no_dialog = c_no

dst_device = c_device

IMPORTING

pdf_bytecount = v_bytecount

TABLES

pdf = t_pdf_output

EXCEPTIONS

err_no_abap_spooljob = 1

err_no_spooljob = 2

err_no_permission = 3

err_conv_not_possible = 4

err_bad_destdevice = 5

user_cancelled = 6

err_spoolerror = 7

err_temseerror = 8

err_btcjob_open_failed = 9

err_btcjob_submit_failed = 10

err_btcjob_close_failed = 11

OTHERS = 12.

CHECK sy-subrc = 0.

Transfer the 132-long strings to 255-long strings

LOOP AT t_pdf_output.

TRANSLATE t_pdf_output USING '~'.

CONCATENATE v_buffer t_pdf_output INTO v_buffer.

ENDLOOP.

TRANSLATE v_buffer USING '~'.

DO.

t_mess_att = v_buffer.

APPEND t_mess_att.

SHIFT v_buffer LEFT BY 255 PLACES.

IF v_buffer IS INITIAL.

EXIT.

ENDIF.

ENDDO.

****************************************************************

*CALL FUNCTION 'RSPO_DOWNLOAD_SPOOLJOB'

EXPORTING
id =
fname =
.

ENDFORM. " conv_spool_to_pdf

&----

*& Form process_email

&----

Form to send e-mail

----

FORM process_email .

DESCRIBE TABLE t_mess_att LINES v_recsize.

CHECK v_recsize > 0.

PERFORM send_email USING p_email1.

ENDFORM. " process_email

&----

*& Form send_email

&----

Form to send e-mail to p_email destination as on the selection screen

----

-->P_EMAIL Email Address

----

FORM send_email USING p_email.

REFRESH t_mess_bod.

Default subject matter

v_subject = 'Subject'.

v_attachment_desc = 'Attachname'.

CONCATENATE 'attach_name' ' ' INTO gd_attachment_name.

t_mess_bod = 'Message Body text, line 1'.

APPEND t_mess_bod.

t_mess_bod = 'Message Body text, line 2...'.

APPEND t_mess_bod.

If no sender specified - default blank

IF p_sender EQ space.

v_sender_type = space.

ELSE.

v_sender_type = 'INT'.

ENDIF.

Send file by email as .xls speadsheet

PERFORM send_file_as_email_attachment

TABLES t_mess_bod

t_mess_att

USING p_email

'Example .xls documnet attachment'

'PDF'

v_attachment_name

v_attachment_desc

p_sender

v_sender_type

CHANGING v_error

v_reciever.

ENDFORM. " send_email

&----

*& Form send_file_as_email_attachment

&----

Sending File as e-mail attachment

----

-->t_message

-->t_attach
-->p_email
-->p_mtitle
-->p_format
-->p_filename
-->p_attdescription
-->p_sender_address
-->p_sender_addres_type

----

FORM send_file_as_email_attachment TABLES t_message

t_attach

USING p_email

p_mtitle

p_format

p_filename

p_attdescription

p_sender_address

p_sender_addres_type

CHANGING p_error

p_reciever.

DATA: v_error TYPE sy-subrc,

v_reciever TYPE sy-subrc,

v_mtitle LIKE sodocchgi1-obj_descr,

v_email LIKE somlreci1-receiver,

v_format TYPE so_obj_tp ,

v_attdescription TYPE so_obj_nam ,

v_attfilename TYPE so_obj_des ,

v_sender_address LIKE soextreci1-receiver,

v_sender_address_type LIKE soextreci1-adr_typ,

v_receiver LIKE sy-subrc.

DATA: t_packing_list LIKE sopcklsti1 OCCURS 0 WITH HEADER LINE,

t_contents LIKE solisti1 OCCURS 0 WITH HEADER LINE,

t_receivers LIKE somlreci1 OCCURS 0 WITH HEADER LINE,

t_attachment LIKE solisti1 OCCURS 0 WITH HEADER LINE,

t_object_header LIKE solisti1 OCCURS 0 WITH HEADER LINE,

v_cnt TYPE i,

v_sent_all(1) TYPE c,

w_doc_data LIKE sodocchgi1.

v_email = p_email.

v_mtitle = p_mtitle.

v_format = p_format.

v_attdescription = p_attdescription.

v_attfilename = p_filename.

v_sender_address = p_sender_address.

v_sender_address_type = p_sender_addres_type.

Fill the document data.

w_doc_data-doc_size = 1.

Populate the subject/generic message attributes

w_doc_data-obj_langu = sy-langu.

w_doc_data-obj_name = 'SAPRPT'.

w_doc_data-obj_descr = v_mtitle .

w_doc_data-sensitivty = 'F'.

Fill the document data and get size of attachment

CLEAR w_doc_data.

READ TABLE t_attach INDEX v_cnt.

w_doc_data-doc_size =

( v_cnt - 1 ) * 255 + STRLEN( t_attach ).

w_doc_data-obj_langu = sy-langu.

w_doc_data-obj_name = 'SAPRPT'.

w_doc_data-obj_descr = v_mtitle.

w_doc_data-sensitivty = 'F'.

CLEAR t_attachment.

REFRESH t_attachment.

t_attachment[] = t_attach[].

Describe the body of the message

CLEAR t_packing_list.

REFRESH t_packing_list.

t_packing_list-transf_bin = space.

t_packing_list-head_start = 1.

t_packing_list-head_num = 0.

t_packing_list-body_start = 1.

DESCRIBE TABLE t_message LINES t_packing_list-body_num.

t_packing_list-doc_type = 'RAW'.

APPEND t_packing_list.

Create attachment notification

t_packing_list-transf_bin = 'X'.

t_packing_list-head_start = 1.

t_packing_list-head_num = 1.

t_packing_list-body_start = 1.

DESCRIBE TABLE t_attachment LINES t_packing_list-body_num.

t_packing_list-doc_type = v_format.

t_packing_list-obj_descr = v_attdescription.

t_packing_list-obj_name = v_attfilename.

t_packing_list-doc_size = t_packing_list-body_num * 255.

APPEND t_packing_list.

Add the recipients email address

CLEAR t_receivers.

REFRESH t_receivers.

t_receivers-receiver = v_email.

t_receivers-rec_type = 'U'.

t_receivers-com_type = 'INT'.

t_receivers-notif_del = 'X'.

t_receivers-notif_ndel = 'X'.

APPEND t_receivers.

CALL FUNCTION 'SO_DOCUMENT_SEND_API1'

EXPORTING

document_data = w_doc_data

put_in_outbox = 'X'

sender_address = v_sender_address

sender_address_type = v_sender_address_type

commit_work = 'X'

IMPORTING

sent_to_all = v_sent_all

TABLES

packing_list = t_packing_list

contents_bin = t_attachment

contents_txt = t_message

receivers = t_receivers

EXCEPTIONS

too_many_receivers = 1

document_not_sent = 2

document_type_not_exist = 3

operation_no_authorization = 4

parameter_error = 5

x_error = 6

enqueue_error = 7

OTHERS = 8.

Populate zerror return code

v_error = sy-subrc.

Populate zreceiver return code

LOOP AT t_receivers.

v_receiver = t_receivers-retrn_code.

ENDLOOP.

ENDFORM. " send_file_as_email_attachment

Re: spool excel and mail..

Former Member — Thu, 04 Oct 2007 06:05:43 GMT

Did you check your spool in SP01 transaction. Is that truncated ?

Re: spool excel and mail..

Former Member — Thu, 04 Oct 2007 06:16:22 GMT

Hi prashant,

while displaying the report from spool, its ok. But the same report is truncated in the PDf attachment..