Help in sending .XLSX extension file as attachment

Former Member · ‎09-18-2014

Hi All,

Could you please let me know the function module or Class which is used to send a mail with attachment .XLSX file?

I have implemented the code for .XLS using FM "SO_NEW_DOCUMENT_ATT_SEND_API1" and its working correctly.

I need help in .XLSX format attachment.

Please suggest.

Thanks in advance!!

Regards,

Shaik

Private_Member_14913 · ‎09-18-2014

Hello,

You can refer below code XLSX with Zip file.

Thanks!


  DATA: zcl_result_data TYPE REF TO cl_salv_ex_result_data_table.
  DATA: zcl_data        TYPE REF TO data.
  DATA: it_char         TYPE STANDARD TABLE OF t_char.


  DATA: lv_version      TYPE string.
  DATA: lv_file_type    TYPE salv_bs_constant.
  DATA: lv_flavour      TYPE string.
  DATA: g_xstring       TYPE xstring.
  DATA: gt_bintab       TYPE solix_tab.
  DATA: g_size          TYPE i.
  DATA:
      lv_sent_to_all   TYPE os_boolean,                     "#EC NEEDED
      lv_objkey        TYPE edi_docnum,                     "#EC NEEDED
      lv_count         TYPE i,                              "#EC NEEDED
      lv_email         TYPE adr6-smtp_addr,                 "#EC NEEDED
      lv_c_edids       TYPE sy-dbcnt,                       "#EC NEEDED
      lv_docnum(16)    TYPE c,                              "#EC NEEDED
      lv_save_hand     TYPE bal_t_logh,                     "#EC NEEDED
      lv_subject       TYPE string,                         "#EC NEEDED
      lv_subject1      TYPE so_obj_des,                     "#EC NEEDED
      lv_log           TYPE bal_s_log,                      "#EC NEEDED
      lv_handle        TYPE balloghndl,                     "#EC NEEDED
      lv_logsys        TYPE tbdls-logsys,                   "#EC NEEDED
      lv_msg           TYPE bal_s_msg,                      "#EC NEEDED
      lv_text          TYPE bcsy_text,                      "#EC NEEDED
      lv_string        TYPE string,                         "#EC NEEDED
      lt_att_hex       TYPE solix_tab,
      l_attsubject     TYPE sood-objdes,
      lr_send_request  TYPE REF TO cl_bcs,                  "#EC NEEDED
      lr_bcs_exception TYPE REF TO cx_bcs,                  "#EC NEEDED
      lr_recipient     TYPE REF TO if_recipient_bcs,        "#EC NEEDED
      lr_sender        TYPE REF TO cl_sapuser_bcs,          "#EC NEEDED
      lx_document      TYPE REF TO cx_document_bcs,         "#EC NEEDED
      lr_document      TYPE REF TO cl_document_bcs.         "#EC NEEDED


  FIELD-SYMBOLS <tab> TYPE ANY TABLE.
  UNASSIGN:  <tab>.


  CLEAR: zcl_result_data,  zcl_data, lv_version,  lv_file_type,
          lv_flavour, g_xstring, gt_bintab,g_size.
  "-------------------------------------------------------------------------------
  "   Create send request
  "-------------------------------------------------------------------------------


      lr_send_request = cl_bcs=>create_persistent( ).


  "-------------------------------------------------------------------------------
  "   "Email FROM...
  "-------------------------------------------------------------------------------
      
      lr_sender = cl_sapuser_bcs=>create( c_uname ).
      "Add sender to send request
      CALL METHOD lr_send_request->set_sender
        EXPORTING
          i_sender = lr_sender.


      LOOP AT s_email INTO s_email.
        "Email TO...
        lv_email = s_email-low.
        lr_recipient = cl_cam_address_bcs=>create_internet_address( lv_email ).
        "Add recipient to send request
        CALL METHOD lr_send_request->add_recipient
          EXPORTING
            i_recipient = lr_recipient
            i_express   = 'X'.
      ENDLOOP.


  "-------------------------------------------------------------------------------
  "   "Email BODY
  "-------------------------------------------------------------------------------
      
      lr_send_request->set_message_subject( lv_subject ).
      lr_document = cl_document_bcs=>create_document(
                                    i_type        = 'HTM'         "Since it is an HTML body
                                    i_importance  = '5'
                                    i_text        = lv_text
                                    i_subject     = lv_subject1 ).






  "-------------------------------------------------------------------------------
  "   XLSX Attachment
  "-------------------------------------------------------------------------------


  CREATE DATA zcl_data TYPE STANDARD TABLE OF t_char. "Output table type
  ASSIGN zcl_data->* TO <tab>.
  MOVE it_char[] TO <tab>. "Output Data
  zcl_result_data = cl_salv_ex_util=>factory_result_data_table(
                        r_data = zcl_data
                        s_layout = p_layo "parameter
                        t_fieldcatalog = p_fcat "parameter
                        t_sort = p_sort "parameter
                        ).
  CASE cl_salv_bs_a_xml_base=>get_version( ).
    WHEN if_salv_bs_xml=>version_25.
      lv_version = if_salv_bs_xml=>version_25.
    WHEN if_salv_bs_xml=>version_26.
      lv_version = if_salv_bs_xml=>version_26.
  ENDCASE.


  lv_file_type = if_salv_bs_xml=>c_type_xlsx.
  lv_flavour = if_salv_bs_c_tt=>c_tt_xml_flavour_export.
  "-------------------------------------------------------------------------------
  "   "transformation of data to XLSX
  "-------------------------------------------------------------------------------
  
  CALL METHOD cl_salv_bs_tt_util=>if_salv_bs_tt_util~transform
    EXPORTING
      xml_type      = lv_file_type
      xml_version   = lv_version
      r_result_data = zcl_result_data
      xml_flavour   = lv_flavour
      gui_type      = if_salv_bs_xml=>c_gui_type_gui
    IMPORTING
      xml           = g_xstring.


  "-------------------------------------------------------------------------------
  "   ZIP the file
  "-------------------------------------------------------------------------------
  " create our zipper object


  CONCATENATE text-037 p_fdate text-038 INTO filename.
  CREATE OBJECT lc_zipper.
  "add file to zip
  CALL METHOD lc_zipper->add
    EXPORTING
      name    = filename
      content = g_xstring.                                  "#EC FB_RC


  "save zip
  CALL METHOD lc_zipper->save
    RECEIVING
      zip = zip.


* --- Convert Xstring into Binary ---
  CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
    EXPORTING
      buffer     = zip
    TABLES
      binary_tab = p_lt_att_hex.




  "-------------------------------------------------------------------------------
  "   "Email BODY
  "-------------------------------------------------------------------------------      
      PERFORM f_email_body CHANGING lv_text.
      lr_send_request->set_message_subject( lv_subject ).
      lr_document = cl_document_bcs=>create_document(
                                    i_type        = 'HTM'         "Since it is an HTML body
                                    i_importance  = '5'
                                    i_text        = lv_text
                                    i_subject     = lv_subject1 ).


  "-------------------------------------------------------------------------------
  "   "Create Attachment
  "-------------------------------------------------------------------------------
      TRY.
          lr_document->add_attachment( EXPORTING
                                          i_attachment_type = 'ZIP'
                                          i_attachment_subject = l_attsubject
                                          i_att_content_hex = lt_att_hex ).
        CATCH cx_document_bcs INTO lx_document.
          MESSAGE e035.                                 "#EC MG_PAR_CNT
          RETURN.
      ENDTRY.


  "-------------------------------------------------------------------------------
  "   "Add document to send request
  "-------------------------------------------------------------------------------      
      CALL METHOD lr_send_request->set_document( lr_document ).


      lr_send_request->set_message_subject( lv_subject ).


  "-------------------------------------------------------------------------------
  "         "Send email
  "-------------------------------------------------------------------------------
      CALL METHOD lr_send_request->send(
        EXPORTING
          i_with_error_screen = 'X'
        RECEIVING
          result              = lv_sent_to_all ).

Former Member · ‎10-13-2014

Hi Samir,

Thanks for the Help. Is it possible to attachment .xlsx file with out ZIP and is it is possible to attach 2 files in same notification mail.

In my current functionality I am able to attach 2 .xls file with using FM SO_NEW_DOCUMENT_ATT_SEND_API1. Now business want it should support .xlsx files also.

Please suggest.

Thanks & Regards,

Shaik

SimoneMilesi · ‎10-13-2014

You can using class cl_ixml like explained here

or ABAP2XLSX

abap2xlsx - ABAP Development - SCN Wiki

former_member392866 · ‎10-13-2014

Hi Shaik,

As per my knowledge Without ZIP we can't send .xlsx files as mail attachment. we tried lot of ways but we couldn't get perfect solution.

As Sameer said with ZIP only possible .

Thanks,

Balaji.

SimoneMilesi · ‎10-13-2014

It's not true

using ABAP2XLSX and CL_BCS for email creation you can send XLSX as attachment...


*----XLSX file Data
DATA: lo_excel             TYPE REF TO zcl_excel,
      lo_excel_writer      TYPE REF TO zif_excel_writer,
      lo_worksheet         TYPE REF TO zcl_excel_worksheet,
      lo_style_bold_border TYPE REF TO zcl_excel_style,
      lo_border_dark       TYPE REF TO zcl_excel_style_border,
      lo_style_mixed       TYPE REF TO zcl_excel_style,
      lo_style_filled      TYPE REF TO zcl_excel_style,
      lo_style_standard    TYPE REF TO zcl_excel_style,
      column_dimension     TYPE REF TO zcl_excel_worksheet_columndime.

DATA: lv_file                   TYPE xstring,
      lv_bytecount              TYPE i,
      lt_file_tab               TYPE solix_tab,
      lv_style_bold_border_guid TYPE zexcel_cell_style,
      lv_style_mixed_guid       TYPE zexcel_cell_style,
      lv_style_filled_guid      TYPE zexcel_cell_style,
      lo_style_standard_guid    TYPE zexcel_cell_style.

DATA: lv_full_path      TYPE string,
      lv_workdir        TYPE string,
      lv_file_separator TYPE c.
*---Email Data
DATA: row TYPE zexcel_cell_row.
DATA: gv_recipient     TYPE REF TO if_recipient_bcs.
DATA: gv_send_request  TYPE REF TO cl_bcs.
DATA: gv_document      TYPE REF TO cl_document_bcs.
DATA: gv_sender        TYPE REF TO cl_sapuser_bcs.

DATA: text1_mail      TYPE bcsy_text.
DATA: lv_mail_ok      TYPE c.
DATA: lines           TYPE tline.
*DATA: lv_subject      TYPE so_obj_des.
*DATA: val_ind         TYPE ad_smtpadr.
DATA: sent_to_all     TYPE os_boolean.
DATA: lv_string       TYPE string.
DATA: binary_content  TYPE solix_tab.
DATA: size            TYPE so_obj_len.
DATA: tlines          TYPE TABLE OF tline.
DATA: gv_address       TYPE TABLE OF char62.


<<<create your internal table you want to send via mail then>>>

  DATA: lv_title TYPE zexcel_sheet_title.

  lv_title = 'Sheet1'.

  IF lo_excel IS NOT INITIAL.
    FREE lo_excel.
  ENDIF.
  CREATE OBJECT lo_excel.

  " Get active sheet
  lo_worksheet = lo_excel->get_active_worksheet( ).
  lo_worksheet->set_title( lv_title ).


**Body
  PERFORM set_body.
**Download excel
  PERFORM save_excel.
*Send mail
  PERFORM send_mail_xls.






FORM set_body. "create dynamically XLSX from internal table
DATA: lv_index TYPE i.
  DATA: lv_data(10).
  DATA: lv_data2(10).
  DATA: lv_count TYPE i.
  DATA: lv_colonna  TYPE string.
  DATA: lv_col(2).
  DATA: lv_width TYPE float.
  DATA: mailc TYPE string .
  DATA: invalid  TYPE string,
        errtxt   TYPE string.
  DATA: column(2) TYPE c.
  DATA: lv_column TYPE zexcel_cell_column_alpha.


  DEFINE set_cell.
    lo_worksheet->set_cell( ip_row    = &1
                            ip_column = &2
                            ip_value  = &3
                            ip_style  = lv_style_mixed_guid ).
    column_dimension =
        lo_worksheet->get_column_dimension( ip_column = &2 ).
    column_dimension->set_width( ip_width = &4 ).
  END-OF-DEFINITION.

  lv_index = 1.
  LOOP AT gt_file. "my xlsx table
    column = ' '.
    DO.
      SPLIT gt_file-rec AT gv_sep INTO mailc gt_file-rec.
      IF gt_file-rec IS INITIAL.
        EXIT.
      ENDIF.

      CALL FUNCTION 'HR_GB_XML_PATTERN_CHECK'
        EXPORTING
          i_string  = mailc
        IMPORTING
          e_invalid = invalid
          e_errtxt  = errtxt.

      IF invalid IS NOT INITIAL.
        PERFORM convert_unicode USING mailc
                                     invalid.
      ENDIF.


      "***

      PERFORM get_next_column CHANGING column.

      lv_width = strlen( mailc ).
      lv_column = column.

      set_cell lv_index lv_column mailc lv_width.

      "***
    ENDDO.





    lv_index = lv_index + 1.

  ENDLOOP.

ENDFORM.                    " SET_BODY
*&---------------------------------------------------------------------*
*&      Form  CONVERT_UNICODE
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_MAILC  text
*      -->P_INVALID  text
*----------------------------------------------------------------------*
FORM convert_unicode  USING    p_mailc
                               p_invalid.
  DATA: lcount TYPE i,
        llenght TYPE i.

  llenght = strlen( p_invalid ).
  DO.
    IF p_invalid+lcount(1) = space.
      EXIT.
    ENDIF.
    IF p_invalid+lcount(1) <> '.'
       AND p_invalid+lcount(1) <>',' AND  p_invalid+lcount(1) <>  '/'.
      REPLACE ALL OCCURRENCES OF p_invalid+lcount(1) IN p_mailc
        WITH space.

    ENDIF.
    ADD 1 TO lcount.
    IF lcount = llenght.
      EXIT.
    ENDIF.
  ENDDO.
ENDFORM.                    " CONVERT_UNICODE
*&---------------------------------------------------------------------*
*&      Form  SAVE_EXCEL
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM save_excel .
  CREATE OBJECT lo_excel_writer TYPE zcl_excel_writer_2007.
  lv_file = lo_excel_writer->write_file( lo_excel ).


  " Convert to binary
  CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
    EXPORTING
      buffer        = lv_file
    IMPORTING
      output_length = lv_bytecount
    TABLES
      binary_tab    = lt_file_tab.
ENDFORM.                    " SAVE_EXCEL
*&---------------------------------------------------------------------*
*&      Form  SEND_MAIL_XLS
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM send_mail_xls .

  DATA: lt_texts LIKE ztesti_doc OCCURS 0 WITH HEADER LINE.
  DATA:  lk_filename(255) TYPE c ,
           lk_subject(50) TYPE c .
  DATA: objtxt LIKE solisti1 OCCURS 0 WITH HEADER LINE.
  DATA: text            TYPE bcsy_text.


  DEFINE add_mail_address.
    val_ind = &1.

* Create recipient
    if val_ind is not initial.
      gv_recipient = cl_cam_address_bcs=>create_internet_address(
        i_address_string = val_ind ).

* Add recipient with its respective attributes to send request
      call method gv_send_request->add_recipient
        exporting
          i_recipient = gv_recipient
          i_express   = &2
          i_copy      = &3.
      lv_mail_ok = 'X'.
    endif.
  END-OF-DEFINITION.



  IF gv_recipient IS NOT INITIAL.
    FREE gv_recipient.
  ENDIF.
  IF gv_send_request IS NOT INITIAL.
    FREE gv_send_request.
  ENDIF.

**Receivers
  REFRESH gv_address.

* Create instance of CL_BCS
  gv_send_request = cl_bcs=>create_persistent( ).

  
  CLEAR lk_subject.
  MOVE 'EMAIL TITLE'  TO lk_subject.


    MOVE 'EMAIL BODY' TO objtxt.
    APPEND objtxt TO text.

  

*-- attachment name
   move 'FILENAME.xlsx' TO  lk_filename.

  

*Set sender
  gv_sender = cl_sapuser_bcs=>create( sy-uname ).
  CALL METHOD gv_send_request->set_sender( gv_sender ).
*
**Subject
  MOVE  lk_subject TO lv_subject.

* Create document
  gv_document = cl_document_bcs=>create_document(
                  i_type    = 'RAW'
                  i_text    = text
                  i_length  = '200'
                  i_subject = lv_subject ).

  CALL METHOD gv_send_request->set_document( gv_document ).

  MOVE lv_bytecount TO size.

  MOVE  lk_filename TO lv_subject.
  CALL METHOD gv_document->add_attachment
    EXPORTING
      i_attachment_type    = ''
      i_attachment_subject = lv_subject                     "ext-m01
      i_attachment_size    = size
      i_att_content_hex    = lt_file_tab.



  LOOP AT s_mail . "select-options in input for email addresses no intervals
    add_mail_address s_mail-low ' ' 'X'..
  ENDLOOP.


  CHECK lv_mail_ok IS NOT INITIAL.
* 
* Send document
  CALL METHOD gv_send_request->send(
    EXPORTING
      i_with_error_screen = 'X'
    RECEIVING
      result              = sent_to_all ).

  IF sent_to_all IS NOT INITIAL.
    COMMIT WORK.
  ELSE.
    ROLLBACK WORK.
  ENDIF.

ENDFORM.                    " SEND_MAIL_XLS
*&---------------------------------------------------------------------*
*&      Form  GET_NEXT_COLUMN
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      <--P_COLUMN  text
*----------------------------------------------------------------------*
FORM get_next_column  CHANGING column.
  DATA: next_column(1)  TYPE c.
  DATA: length  TYPE i.


  IF column = ' '.
    column = 'A'.
  ELSEIF strlen( column ) = 1.
    IF column = 'Z'.
      column = 'AA'.
    ELSE.
      PERFORM get_next USING column  CHANGING next_column.
      column = next_column.
    ENDIF.
  ELSEIF strlen( column ) = 2.
    IF column+1(1) = 'Z'.
      PERFORM get_next USING column(1)  CHANGING next_column.
      column(1) = next_column.

    ENDIF.
    PERFORM get_next USING column+1(1)  CHANGING next_column.
    column+1(1) = next_column.
  ENDIF.
ENDFORM.                    " GET_NEXT_COLUMN
*&---------------------------------------------------------------------*
*&      Form  get_next
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->COLUMN       text
*      -->NEXT_COLUMN  text
*----------------------------------------------------------------------*
FORM get_next USING column
              CHANGING next_column.

  CASE column.
    WHEN ' '.
      next_column = 'A'.
    WHEN 'A'.
      next_column = 'B'.
    WHEN 'B'.
      next_column = 'C'.
    WHEN 'C'.
      next_column = 'D'.
    WHEN 'D'.
      next_column = 'E'.
    WHEN 'E'.
      next_column = 'F'.
    WHEN 'F'.
      next_column = 'G'.
    WHEN 'G'.
      next_column = 'H'.
    WHEN 'H'.
      next_column = 'I'.
    WHEN 'I'.
      next_column = 'J'.
    WHEN 'J'.
      next_column = 'K'.
    WHEN 'K'.
      next_column = 'L'.
    WHEN 'L'.
      next_column = 'M'.
    WHEN 'M'.
      next_column = 'N'.
    WHEN 'N'.
      next_column = 'O'.
    WHEN 'O'.
      next_column = 'P'.
    WHEN 'P'.
      next_column = 'Q'.
    WHEN 'Q'.
      next_column = 'R'.
    WHEN 'R'.
      next_column = 'S'.
    WHEN 'S'.
      next_column = 'T'.
    WHEN 'T'.
      next_column = 'U'.
    WHEN 'U'.
      next_column = 'V'.
    WHEN 'V'.
      next_column = 'W'.
    WHEN 'W'.
      next_column = 'X'.
    WHEN 'X'.
      next_column = 'Y'.
    WHEN 'Y'.
      next_column = 'Z'.
    WHEN 'Z'.
      next_column = 'A'.
  ENDCASE.

ENDFORM.                    "get_next

Private_Member_14913 · ‎10-13-2014

Hello,

You can create without zip file.

After call to method cl_salv_bs_tt_util=>if_salv_bs_tt_util~transform.

You need to call FM - SCMS_XSTRING_TO_BINARY, output of this FM you can use for sending as xlsx attachment.

Below snippet code for your ref.


"transformation of data to excel
   CALL METHOD cl_salv_bs_tt_util=>if_salv_bs_tt_util~transform
     EXPORTING
       xml_type      = lv_file_type
       xml_version   = lv_version
       r_result_data = zcl_result_data
       xml_flavour   = lv_flavour
       gui_type      = if_salv_bs_xml=>c_gui_type_gui
     IMPORTING
       xml           = g_xstring.
   CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
     EXPORTING
       buffer          = g_xstring
*     APPEND_TO_TABLE = ' '
     IMPORTING
       output_length   = g_size
     TABLES
       binary_tab      = gt_bintab.

_IvanFemia_ · ‎10-14-2014

You can send XLSX file as email attachment.

I did in several projects for years.

Please check the DEMO report included in abap2xlsx for a quick and easy implementation

Ivan

peng_wen · ‎10-14-2014

Hello,

I would like to ask you to check the example implementation in the

longtext of attached note 1459896. Here you find a example how you can

hand over a extension with more than 3 characters. The example based

on the usage of class CL_BCS. We from SAP side recommend to use this

send interface. Example implementations you will find in example reports

BCS_EXAMPLE_* (replace * with 1...5).

Regards,
Wen Peng