Skip to Content
avatar image
Former Member

error in excel sheet when sending mail of spool list

hi all,

i have the code like this

DATA: lv_SPOOL LIKE TSP01-RQIDENT.
  data: lt_hex LIKE table of SOLIX WITH HEADER LINE.

  lv_spool = GD_SPOOL_NR.

  CALL FUNCTION 'RSPO_RETURN_ABAP_SPOOLJOB'
    EXPORTING
      RQIDENT              = lv_SPOOL
      FIRST_LINE           = 1
    TABLES
      BUFFER               = IT_SPOOL_XLS
    EXCEPTIONS
      NO_SUCH_JOB          = 1
      NOT_ABAP_LIST        = 2
      JOB_CONTAINS_NO_DATA = 3
      SELECTION_EMPTY      = 4
      NO_PERMISSION        = 5
      CAN_NOT_ACCESS       = 6
      READ_ERROR           = 7
      OTHERS               = 8.


  IF SY-SUBRC NE 0.
    MESSAGE S899(MM) WITH 'Error while reading spool'.
    STOP.
  ENDIF.
*  check sy-subrc = 0.

  CALL FUNCTION 'SAP_CONVERT_TO_CSV_FORMAT'
    EXPORTING
      I_FIELD_SEPERATOR    = '|'
      I_FILENAME           = 'TEST'
    TABLES
      I_TAB_SAP_DATA       = IT_SPOOL_XLS
    CHANGING
      I_TAB_CONVERTED_DATA = IT_SPOOL_XLS_RETURN
    EXCEPTIONS
      CONVERSION_FAILED    = 1
      OTHERS               = 2.
  IF SY-SUBRC <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
  ENDIF.

  CALL FUNCTION 'SAP_CONVERT_TO_XLS_FORMAT'
    EXPORTING
      I_FIELD_SEPERATOR    = '|'
      I_LINE_HEADER        = '-'
      I_FILENAME           = 'report.xls'
    TABLES
      I_TAB_SAP_DATA       = IT_SPOOL_XLS
    CHANGING
      I_TAB_CONVERTED_DATA = IT_SPOOL_XLS_RETURN
    EXCEPTIONS
      CONVERSION_FAILED    = 1
      OTHERS               = 2.

after this when i try to send the excel file with the FM

SO_DOCUMENT_SEND_API1

im getting the mail but all the data is in first cell of the excel sheet. can anyone please help.

<REMOVED BY MODERATOR - REQUEST OR OFFER POINTS ARE FORBIDDEN>

thanks,

anupama.

Edited by: Alvaro Tejada Galindo on Jan 7, 2009 2:36 PM

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    avatar image
    Former Member
    Sep 02, 2008 at 04:18 AM

    Hi Deep,

    Check this thread for sample code:

    excel-file-as-attachment-in-mail

    Regards,

    Chandra Sekhar

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 02, 2008 at 04:25 AM

    hi,

    u can use FM: LIST_TO_ASCI , as a solution of ur problem u can also consider below mention example of sending data in excel format through mail

    example:

    i exported the data to memory using the FM LIST_FROM_MEMORY.

    CALL FUNCTION 'LIST_FROM_MEMORY'
    TABLES
    listobject = t_listobject
    EXCEPTIONS
    not_found = 1
    OTHERS = 2.
    IF sy-subrc 0.
    MESSAGE e000(su) WITH text-001.
    ENDIF.
    

    then i converted it into ASCII using LIST_TO_ASCI,

    CALL FUNCTION 'LIST_TO_ASCI'
    TABLES
    listasci = t_xlstab
    listobject = t_listobject
    EXCEPTIONS
    empty_list = 1
    list_index_invalid = 2
    OTHERS = 3.
    IF sy-subrc NE 0.
    MESSAGE e003(yuksdbfzs).
    ENDIF.
    

    This gives the data in ASCII format separated by '|' and the header has '-', dashes. If you use this internal table directly without any proccesing in SO_NEW_DOCUMENT_ATT_SEND_API1, then you will not get a good excel sheet attachment. To overcome this limitation, i used cl_abap_char_utilities=>newline and cl_abap_char_utilities=>horizontal_tab to add horizontal and vertical tabs to the internal table, replacing all occurences of '|' with

    cl_abap_char_utilities=>horizontal_tab.

    Set the doc_type as 'XLS', create the body and header using the packing_list and pass the data to be downloaded to SO_NEW_DOCUMENT_ATT_SEND_API1 as contents_bin.

    This will create an excel attachment.

    Sample code for formatting the data for the attachment in excel format.

    *	Format the data for excel file download 
    LOOP AT t_xlstab INTO wa_xlstab .
    DESCRIBE TABLE t_xlstab LINES lw_cnt.
    CLEAR lw_sytabix.
    lw_sytabix = sy-tabix.
    *	If not new line then replace '|' by tabs 
    IF NOT wa_xlstab EQ cl_abap_char_utilities=>newline.
    REPLACE ALL OCCURRENCES OF '|' IN wa_xlstab
    WITH cl_abap_char_utilities=>horizontal_tab.
    MODIFY t_xlstab FROM wa_xlstab .
    CLEAR wa_xlstab.
    wa_xlstab = cl_abap_char_utilities=>newline.
    IF lw_cnt NE 0 .
    lw_sytabix = lw_sytabix + 1.
    *	Insert new line for the excel data 
    INSERT wa_xlstab INTO t_xlstab INDEX lw_sytabix.
    lw_cnt = lw_cnt - 1.
    ENDIF.
    CLEAR wa_xlstab.
    ENDIF.
    ENDLOOP.
    

    Sample code for creating attachment and sending mail:

    FORM send_mail .
    *	Define the attachment format 
    lw_doc_type = 'XLS'.
    *	Create the document which is to be sent 
    lwa_doc_chng-obj_name = 'List'.
    lwa_doc_chng-obj_descr = w_subject. "Subject
    lwa_doc_chng-obj_langu = sy-langu.
    *	Fill the document data and get size of message 
    LOOP AT t_message.
    lt_objtxt = t_message-line.
    APPEND lt_objtxt.
    ENDLOOP.
    
    DESCRIBE TABLE lt_objtxt LINES lw_tab_lines.
    IF lw_tab_lines GT 0.
    READ TABLE lt_objtxt INDEX lw_tab_lines.
    lwa_doc_chng-doc_size = ( lw_tab_lines - 1 ) * 255 + STRLEN( lt_objtxt ).
    lwa_doc_chng-obj_langu = sy-langu.
    lwa_doc_chng-sensitivty = 'F'.
    ELSE.
    lwa_doc_chng-doc_size = 0.
    ENDIF.
    *	Fill Packing List For the body of e-mail 
    lt_packing_list-head_start = 1.
    lt_packing_list-head_num = 0.
    lt_packing_list-body_start = 1.
    lt_packing_list-body_num = lw_tab_lines.
    lt_packing_list-doc_type = 'RAW'.
    APPEND lt_packing_list.
    *	Create the attachment (the list itself) 
    DESCRIBE TABLE t_xlstab LINES lw_tab_lines.
    *	Fill the fields of the packing_list for creating the attachment: 
    lt_packing_list-transf_bin = 'X'.
    lt_packing_list-head_start = 1.
    lt_packing_list-head_num = 0.
    lt_packing_list-body_start = 1.
    lt_packing_list-body_num = lw_tab_lines.
    lt_packing_list-doc_type = lw_doc_type.
    lt_packing_list-obj_name = 'Attach'.
    lt_packing_list-obj_descr = w_docdesc.
    lt_packing_list-doc_size = lw_tab_lines * 255.
    APPEND lt_packing_list.
    *	Fill the mail recipient list 
    lt_reclist-rec_type = 'U'.
    LOOP AT t_recipient_list.
    lt_reclist-receiver = t_recipient_list-address.
    APPEND lt_reclist.
    ENDLOOP.
    *	Finally send E-Mail 
    CALL FUNCTION 'SO_NEW_DOCUMENT_ATT_SEND_API1'
    EXPORTING
    document_data = lwa_doc_chng
    put_in_outbox = 'X'
    commit_work = 'X'
    IMPORTING
    sent_to_all = lw_sent_to_all
    TABLES
    packing_list = lt_packing_list
    object_header = lt_objhead
    contents_bin = t_xlstab
    contents_txt = lt_objtxt
    receivers = lt_reclist
    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.
    

    Code Formatted by: Alvaro Tejada Galindo on Jan 7, 2009 2:36 PM

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      hi, can you please tell me how to export to the memory. i tried using FM list_to_memory but its not working.

      thanks,

      anupama.

  • avatar image
    Former Member
    Jan 07, 2009 at 09:11 AM

    solved

    Add comment
    10|10000 characters needed characters exceeded