Skip to Content
author's profile photo Former Member
Former Member

mail, output of spool as an excel attachment

Hello All,

I'm required to schedule a report, and e-mail the output as an excel attachment.

I have gone through many threads, but could'nt find a solution.

Queries:

1) usage of FM 'SAP_CONVERT_TO_XLS_FORMAT' .

Am i supposed to have a physical file on my system, in which i'll get converted data to excel, to be passed as a parameter.

2) Is there any preference on using the FM

'SO_NEW_DOCUMENT_ATT_SEND_API1' and 'SO_DOCUMENT_SEND_API1'.

Thanks.

Ushma.

Edited by: Ushma Jain on Jun 6, 2009 1:54 PM

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • Best Answer
    Posted on Jun 06, 2009 at 12:11 PM

    if u have an output internal table as report output, u can directly convert the output internal table to xls without considering converting spool to excel.

    "document information
      w_doc_chng-obj_name = text-005.            " Excel
      w_doc_chng-obj_descr = text-006.           "Excel For not deleted planned Orders
      w_doc_chng-sensitivty = text-007.          " F ->Functional object
      w_doc_chng-doc_size = ( message_lines - 1 ) * 255 + STRLEN( wa_imessage-line ).  " calculating total size of doc
    
    
      " displaying planned order no , material , plant in the excel
    
      CONCATENATE 'PLANNED ORDER' 'MATERIAL' 'PLANT' INTO wa_objbin SEPARATED BY c_tab.
      APPEND wa_objbin TO it_objbin.
    
      CLEAR : wa_objbin.
      LOOP AT it_not_del INTO wa_not_del.
        CONCATENATE wa_not_del-plnum wa_not_del-matnr wa_not_del-pwwrk INTO wa_objbin SEPARATED BY c_tab.
        CONCATENATE c_ret wa_objbin INTO wa_objbin.
        APPEND wa_objbin TO it_objbin.
      ENDLOOP.
    
      DESCRIBE TABLE it_objbin LINES v_lines_bin.   " no of lines for excel data
    
    
      " pack the data as RAW
      CLEAR wa_it_objpack-transf_bin.                     "Obj. to be transported not in binary form
      wa_it_objpack-head_start = 1.                   "Start line of object header in transport packet
      wa_it_objpack-head_num = 0.                     "Number of lines of an object header in object packet
      wa_it_objpack-body_start = 1.                   "Start line of object contents in an object packet
      wa_it_objpack-body_num = message_lines.         "Number of lines of the mail body
      wa_it_objpack-doc_type = text-008.           "RAW
      APPEND wa_it_objpack TO it_objpack.
    
    
    

    Edited by: Kartik Tarla on Jun 6, 2009 5:41 PM

    Edited by: Kartik Tarla on Jun 6, 2009 5:42 PM

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 20, 2009 at 08:42 AM

    1) PC file is mandatory for this FM, it generates a true Excel format file, so it can be run only interactively. To generate an excel file on application server, you must generate a text format file (CSV). To generate it generically, use something like this:

    DATA gt_itab TYPE TABLE OF t000.
    DATA gt_csv TYPE TABLE OF string.
    SELECT * FROM t000 INTO TABLE gt_itab.
    PERFORM convert_itab_to_csv USING gt_itab ';' CHANGING gt_csv.
    *----------------------------------------------------------------------*
    FORM convert_itab_to_csv 
          USING 
            it_any              TYPE ANY TABLE
            i_column_separator  TYPE clike
          CHANGING 
            et_csv              TYPE STANDARD TABLE.
      FIELD-SYMBOLS <ls_any> TYPE ANY.
      FIELD-SYMBOLS <l_any> TYPE ANY.
      DATA l_field_num TYPE i.
      DATA l_string TYPE string.
      DATA l_big_char TYPE c LENGTH 255.
      REFRESH et_csv.
      LOOP AT it_any ASSIGNING <ls_any>.
        l_field_num = 0.
        CLEAR l_string.
        DO.
          ADD 1 TO l_field_num.
          ASSIGN COMPONENT l_field_num OF STRUCTURE <ls_any> TO <l_any>.
          IF sy-subrc <> 0.
            EXIT.
          ENDIF.
          WRITE <l_any> TO l_big_char LEFT-JUSTIFIED.
          IF l_field_num = 1.
            l_string = l_big_char.
          ELSE.
            CONCATENATE l_string i_column_separator l_big_char INTO l_string.
          ENDIF.
        ENDDO.
        APPEND l_string TO et_csv.
      ENDLOOP.
    ENDFORM.
    

    2) SO_NEW_DOCUMENT_ATT_SEND_API1 is just a wrapper of SO_DOCUMENT_SEND_API1 (first one calls second one with some default values and all other parameters are passed as is)

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 18, 2009 at 12:26 PM

    <font color=blue>Hi,

    Check this sample program. It works. Change accordigly.

    <pre>

    report zvenkat_mail_xls_attach.

    ----


    " Data retrieval related declarations

    ----


    types:

    begin of t_emp_dat,

    pernr type pa0001-pernr,

    persg type pa0001-persg,

    persk type pa0001-persk,

    plans type pa0001-plans,

    stell type pa0001-stell,

    end of t_emp_dat.

    data:

    w_emp_data type t_emp_dat.

    data:

    i_emp_data type standard table of t_emp_dat.

    ----


    " Mail related declarations

    ----


    "Variables

    data :

    g_sent_to_all type sonv-flag,

    g_tab_lines type i.

    "Types

    types:

    t_document_data type sodocchgi1,

    t_packing_list type sopcklsti1,

    t_attachment type solisti1,

    t_body_msg type solisti1,

    t_receivers type somlreci1.

    "Workareas

    data :

    w_document_data type t_document_data,

    w_packing_list type t_packing_list,

    w_attachment type t_attachment,

    w_body_msg type t_body_msg,

    w_receivers type t_receivers.

    "Internal Tables

    data :

    i_document_data type standard table of t_document_data,

    i_packing_list type standard table of t_packing_list,

    i_attachment type standard table of t_attachment,

    i_body_msg type standard table of t_body_msg,

    i_receivers type standard table of t_receivers.

    parameters:p_mail type char120.

    ----


    "Start-of-selection.

    ----


    start-of-selection.

    perform get_data.

    perform build_xls_data_table.

    ----


    "End-of-selection.

    ----


    end-of-selection.

    perform send_mail.

    &----


    "Form get_data from PA0001

    &----


    form get_data.

    select pernr

    persg

    persk

    plans

    stell

    from pa0001

    into corresponding fields of table i_emp_data

    up to 4 rows.

    endform. " get_data

    &----


    "Form build_xls_data_table

    &----


    form build_xls_data_table.

    "If you have Unicode check active in program attributes then

    "you will need to declare constants as follows.

    class cl_abap_char_utilities definition load.

    constants:

    con_tab type c value cl_abap_char_utilities=>horizontal_tab,

    con_cret type c value cl_abap_char_utilities=>cr_lf.

    concatenate 'PERNR' 'PERSG' 'PERSK' 'PLANS' 'STELL'

    into w_attachment

    separated by con_tab.

    concatenate con_cret

    w_attachment

    into w_attachment.

    append w_attachment to i_attachment.

    clear w_attachment.

    loop at i_emp_data into w_emp_data.

    concatenate w_emp_data-pernr

    w_emp_data-persg

    w_emp_data-persk

    w_emp_data-plans

    w_emp_data-stell

    into w_attachment

    separated by con_tab.

    concatenate con_cret w_attachment

    into w_attachment.

    append w_attachment to i_attachment.

    clear w_attachment.

    endloop.

    endform. "build_xls_data_table

    &----


    "Form send_mail

    "----


    "PACKING LIST

    "This table requires information about how the data in the

    "tables OBJECT_HEADER, CONTENTS_BIN and CONTENTS_TXT are to

    "be distributed to the documents and its attachments.The first

    "row is for the document, the following rows are each for one

    "attachment.

    &----


    form send_mail .

    "Subject of the mail.

    w_document_data-obj_name = 'MAIL_TO_HEAD'.

    w_document_data-obj_descr = 'Regarding Mail Program by SAP ABAP'.

    "Body of the mail

    perform build_body_of_mail

    using:space,

    'Hi,',

    'I am fine. How are you? How are you doing ? ',

    'This program has been created to send simple mail',

    'with Subject,Body with Address of the sender. ',

    'Regards,',

    'Venkat.O,',

    'SAP HR Technical Consultant.'.

    "Write Packing List for Body

    describe table i_body_msg lines g_tab_lines.

    w_packing_list-head_start = 1.

    w_packing_list-head_num = 0.

    w_packing_list-body_start = 1.

    w_packing_list-body_num = g_tab_lines.

    w_packing_list-doc_type = 'RAW'.

    append w_packing_list to i_packing_list.

    clear w_packing_list.

    "Write Packing List for Attachment

    w_packing_list-transf_bin = 'X'.

    w_packing_list-head_start = 1.

    w_packing_list-head_num = 1.

    w_packing_list-body_start = 1.

    describe table i_attachment lines w_packing_list-body_num.

    w_packing_list-doc_type = 'XLS'.

    w_packing_list-obj_descr = 'Excell Attachment'.

    w_packing_list-obj_name = 'XLS_ATTACHMENT'.

    w_packing_list-doc_size = w_packing_list-body_num * 255.

    append w_packing_list to i_packing_list.

    clear w_packing_list.

    "Fill the document data and get size of attachment

    w_document_data-obj_langu = sy-langu.

    read table i_attachment into w_attachment index g_tab_lines.

    w_document_data-doc_size = ( g_tab_lines - 1 ) * 255 + strlen( w_attachment ).

    "Receivers List.

    w_receivers-rec_type = 'U'. "Internet address

    w_receivers-receiver = p_mail.

    w_receivers-com_type = 'INT'.

    w_receivers-notif_del = 'X'.

    w_receivers-notif_ndel = 'X'.

    append w_receivers to i_receivers .

    clear:w_receivers.

    "Function module to send mail to Recipients

    call function 'SO_NEW_DOCUMENT_ATT_SEND_API1'

    exporting

    document_data = w_document_data

    put_in_outbox = 'X'

    commit_work = 'X'

    importing

    sent_to_all = g_sent_to_all

    tables

    packing_list = i_packing_list

    contents_bin = i_attachment

    contents_txt = i_body_msg

    receivers = i_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.

    if sy-subrc = 0 .

    message i303(me) with 'Mail has been Successfully Sent.'.

    else.

    wait up to 2 seconds.

    "This program starts the SAPconnect send process.

    submit rsconn01 with mode = 'INT'

    with output = 'X'

    and return.

    endif.

    endform. " send_mail

    &----


    " Form build_body_of_mail

    &----


    form build_body_of_mail using l_message.

    w_body_msg = l_message.

    append w_body_msg to i_body_msg.

    clear w_body_msg.

    endform. " build_body_of_mail

    </pre>

    Thanks

    venkat.O<font>

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jun 06, 2009 at 12:12 PM

    code continued as above

    " pack the data as excel
      wa_it_objpack-transf_bin = text-009.      " X
      wa_it_objpack-head_start = 1.
      wa_it_objpack-head_num = 1.
      wa_it_objpack-body_start = 1.
      wa_it_objpack-body_num = v_lines_bin.         "no of lines of it_not_del to give no of undeleted planned orders
      wa_it_objpack-doc_type = text-010.         " XLS ->  excel fomat
      wa_it_objpack-obj_name = text-011.         " EXCEL ATTACHMENT
    
      " attachment name
      CONCATENATE text-012 '.XLS' INTO wa_it_objpack-obj_descr.      " PLANNED ORDERS
      wa_it_objpack-doc_size = v_lines_bin * 255.
      APPEND wa_it_objpack TO it_objpack.
    
      " creating email id
      CONCATENATE p_email text-017  INTO v_mailaddr.   "@firmenich.com
    
    * e-mail receivers.
      CLEAR wa_it_reclist.
      wa_it_reclist-receiver = v_mailaddr.
      wa_it_reclist-express =  text-009.                      " X
      wa_it_reclist-rec_type = text-013.                      " U ->  Internet address
      APPEND wa_it_reclist TO it_reclist.
    
    
      " sending mail
      CALL FUNCTION 'SO_NEW_DOCUMENT_ATT_SEND_API1'
        EXPORTING
          document_data              = w_doc_chng
          put_in_outbox              = 'X'
          commit_work                = 'X'
        TABLES
          packing_list               = it_objpack
          contents_bin               = it_objbin
          contents_txt               = imessage
          receivers                  = it_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.
    
      COMMIT WORK.
    
      IF sy-subrc <> 0.
        MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
                WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
    
      ELSE.
        MESSAGE i397.     "E-mail Sent successfully !!
      ENDIF.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jun 06, 2009 at 01:45 PM

    hi..i think we cant control the output format in the email..it depend on what the basis people setting in the transaction code SCOT..double clicking the internet mail and from there you can choose the format to be sent out through email..available option are PDF format, HTML, XLS and TXT. Communicate with the basis people..hope this will help

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.