Send an email with well formatted excel as attachment

REPORT  zemail.

TABLES: adr6. "E-Mail Addresses (Business Address Services)

TYPE-POOLS: sbdst, soi.

*--------------------------------------------------------------------*
*  SELECTION SCREEN
*--------------------------------------------------------------------*
SELECTION-SCREEN BEGIN OF BLOCK mail WITH FRAME TITLE text-002.
PARAMETERS: p_addr LIKE adr6-smtp_addr.
SELECTION-SCREEN END OF BLOCK mail.
SELECTION-SCREEN BEGIN OF BLOCK blk WITH FRAME TITLE text-001.
PARAMETERS: p_class TYPE sbdst_classname  DEFAULT 'HRFPM_EXCEL_STANDARD',
            p_type  TYPE sbdst_classtype  DEFAULT 'OT',
            p_key   TYPE sbdst_object_key DEFAULT 'Z_DOI_TEST',
            p_name  TYPE bds_propva       DEFAULT 'ZTEST'.
SELECTION-SCREEN END OF BLOCK blk.

*--------------------------------------------------------------------*
*  INTERNAL TABLE DECLARATION
*--------------------------------------------------------------------*
DATA: w_text            TYPE bcsy_text,
      it_excel          TYPE TABLE OF solix,
      it_fields         TYPE soi_fields_table,
      wa_field          LIKE LINE OF it_fields,
      it_data           TYPE STANDARD TABLE OF spfli.

*--------------------------------------------------------------------*
*  VARIABLE DECLARATION
*--------------------------------------------------------------------*
DATA: w_size            TYPE i,
      w_sent_to_all     TYPE os_boolean,
      w_send_request    TYPE REF TO cl_bcs,
      w_doc             TYPE REF TO cl_document_bcs,
      w_sender          TYPE REF TO cl_sapuser_bcs,
      w_recipient       TYPE REF TO cl_cam_address_bcs.
FIELD-SYMBOLS: <fs_tab> TYPE STANDARD TABLE,
               <fs_wa>,
               <fs_val>.

*--------------------------------------------------------------------*
*  START-OF-SELECTION
*--------------------------------------------------------------------*
START-OF-SELECTION.
  PERFORM prep_data.
  PERFORM prep_attach_excel.
  PERFORM send_email.

*&---------------------------------------------------------------------*
*&      Form  prep_attach
*&---------------------------------------------------------------------*
*       Prepare for attachmetn
*----------------------------------------------------------------------*
FORM prep_attach_excel.
  TYPES: BEGIN OF ty_otline,
          oleline(128),
         END OF ty_otline.
  DATA: l_control        TYPE REF TO i_oi_container_control,
        l_container      TYPE REF TO cl_gui_custom_container,
        l_doc_signatures TYPE sbdst_signature,
        l_doc_signature  LIKE LINE OF l_doc_signatures,
        l_bds_inst       TYPE REF TO cl_bds_document_set,
        l_components     TYPE sbdst_components,
        l_component      LIKE LINE OF l_components,
        l_uris           TYPE sbdst_uri,
        l_uri            LIKE LINE OF l_uris,
        l_uri_value      TYPE bds_uri,
        l_mime_type      TYPE bds_mimetp,
        l_doc_format     TYPE soi_document_type,
        l_doc_type       TYPE soi_document_type,
        l_ole_types      TYPE TABLE OF ty_otline,
        l_doc            TYPE REF TO i_oi_document_proxy,
        l_sheet          TYPE REF TO i_oi_spreadsheet,
        l_error          TYPE REF TO i_oi_error.
  CALL METHOD c_oi_container_control_creator=>get_container_control
    IMPORTING
      control = l_control
      error   = l_error.
  PERFORM raise_message USING l_error.
  CREATE OBJECT l_container
    EXPORTING
      container_name = 'CONTAINER'.
  CALL METHOD l_control->init_control
    EXPORTING
      r3_application_name = 'TEST'
      parent              = l_container
      inplace_enabled     = 'X'
    IMPORTING
      error               = l_error.
  PERFORM raise_message USING l_error.
  l_doc_signature-prop_name = 'DESCRIPTION'.
  l_doc_signature-prop_value = p_name.
  APPEND l_doc_signature TO l_doc_signatures.

  CREATE OBJECT l_bds_inst.
  CALL METHOD l_bds_inst->get_info
    EXPORTING
      classname       = p_class
      classtype       = p_type
      object_key      = p_key
    CHANGING
      components      = l_components
      signature       = l_doc_signatures
    EXCEPTIONS
      nothing_found   = 1
      error_kpro      = 2
      internal_error  = 3
      parameter_error = 4
      not_authorized  = 5
      not_allowed     = 6.
  CALL METHOD l_bds_inst->get_with_url
    EXPORTING
      classname       = p_class
      classtype       = p_type
      object_key      = p_key
    CHANGING
      uris            = l_uris
      signature       = l_doc_signatures
    EXCEPTIONS
      error_kpro      = 1
      internal_error  = 2
      nothing_found   = 3
      not_authorized  = 4
      not_allowed     = 5
      parameter_error = 6.
  READ TABLE: l_uris INTO l_uri INDEX 1,
              l_components INTO l_component INDEX 1.
  l_uri_value = l_uri-uri.
  l_mime_type = l_component-mimetype.
  CASE l_mime_type.
    WHEN 'application/msword'.
      l_doc_format = soi_docformat_compound.
    WHEN 'application/vnd.ms-excel'.
      l_doc_format = soi_docformat_compound.
    WHEN 'application/vnd.visio'.
      l_doc_format = soi_docformat_compound.
    WHEN 'application/x-rtf' OR 'text/rtf'.
      l_doc_format = soi_docformat_rtf.
    WHEN 'application/x-oleobject'.
      l_doc_format = soi_docformat_compound.
    WHEN 'text/plain'.
      l_doc_format = soi_docformat_text.
    WHEN OTHERS.
      l_doc_format = soi_docformat_native.
  ENDCASE.
*conversion of mimetype to ole type
  CALL METHOD c_oi_container_control_creator=>mime_to_ole
    EXPORTING
      mimetype = l_mime_type
      version  = ''
    IMPORTING
      oletypes = l_ole_types.
  READ TABLE l_ole_types INTO l_doc_type INDEX 1.
*create an instance document for each document that you wanna open
  CALL METHOD l_control->get_document_proxy
    EXPORTING
      document_type   = l_doc_type
      document_format = l_doc_format
      no_flush        = 'X'
    IMPORTING
      document_proxy  = l_doc
      error           = l_error.
  PERFORM raise_message USING l_error.
*open an existed document
  CALL METHOD l_doc->open_document
    EXPORTING
      open_inplace = 'X'
      document_url = l_uri_value.

  CALL METHOD l_doc->get_spreadsheet_interface
    EXPORTING
      no_flush        = space
    IMPORTING
      sheet_interface = l_sheet
      error           = l_error.
  PERFORM raise_message USING l_error.
*select sheet
  CALL METHOD l_sheet->select_sheet
    EXPORTING
      name  = 'Sheet1'
    IMPORTING
      error = l_error.
  PERFORM raise_message USING l_error.
*rename sheet name
  CALL METHOD l_sheet->set_sheet_name
    EXPORTING
      oldname = 'Sheet1'
      newname = 'TEST'
    IMPORTING
      error   = l_error.
  PERFORM raise_message USING l_error.
*append data into selected sheet
  CALL METHOD l_sheet->set_selection
    EXPORTING
      top     = 2
      left    = 1
      rows    = 1
      columns = 1
    IMPORTING
      error   = l_error.
  PERFORM raise_message USING l_error.
  CALL METHOD l_sheet->insert_range
    EXPORTING
      name    = 'RANGE'
      rows    = 10
      columns = 9
    IMPORTING
      error   = l_error.
  PERFORM raise_message USING l_error.
  CALL METHOD l_sheet->insert_one_table
    EXPORTING
      wholetable = 'X'
      data_table = <fs_tab>
      fields_table = it_fields
      rangename  = 'RANGE'
    IMPORTING
      error      = l_error.
  PERFORM raise_message USING l_error.
*save spreedsheet into internal table(binary data)
  CALL METHOD l_doc->save_document_to_table
    IMPORTING
      error          = l_error
    CHANGING
      document_table = it_excel
      document_size  = w_size.
  PERFORM raise_message USING l_error.
  CALL METHOD l_doc->close_document
    IMPORTING
      error = l_error.
  PERFORM raise_message USING l_error.
  CALL METHOD l_doc->release_document
    IMPORTING
      error = l_error.
  PERFORM raise_message USING l_error.
ENDFORM.                    "prep_attach

*&---------------------------------------------------------------------*
*&      Form  raise_message
*&---------------------------------------------------------------------*
*       Raise message
*----------------------------------------------------------------------*
*      -->P_ERROR    text
*----------------------------------------------------------------------*
FORM raise_message USING p_error TYPE REF TO i_oi_error.
  IF p_error->has_failed EQ abap_true.
    CALL METHOD p_error->raise_message
      EXPORTING
        type = 'E'.
    LEAVE LIST-PROCESSING.
  ENDIF.
ENDFORM.                    "raise_message

*&---------------------------------------------------------------------*
*&      Form  prep_data
*&---------------------------------------------------------------------*
*       create internal table and fill data
*----------------------------------------------------------------------*
FORM prep_data.
  DATA: l_char_type TYPE REF TO cl_abap_typedescr,
        l_struct_type TYPE REF TO cl_abap_structdescr,
        l_table_type TYPE REF TO cl_abap_tabledescr,
        l_components  TYPE cl_abap_structdescr=>component_table,
        l_component   LIKE LINE OF l_components,
        l_counter(1) TYPE n,
        l_handler TYPE REF TO data,
        l_struct_handler TYPE REF TO data.

  l_char_type = cl_abap_elemdescr=>get_c( 10 ).
  DO 9 TIMES.
    l_counter = sy-index.
    CONCATENATE 'TXT' l_counter INTO l_component-name.
    wa_field-fieldname = l_component-name.
    wa_field-position  = sy-index.
    wa_field-offset    = ( sy-index - 1 ) * 10.
    wa_field-intlength = 10.
    wa_field-decimals  = 0.
    wa_field-exid      = 'C'.
    APPEND wa_field TO it_fields.
    l_component-type ?= l_char_type.
    APPEND l_component TO l_components.
  ENDDO.
  l_struct_type = cl_abap_structdescr=>create( l_components ).
  l_table_type  = cl_abap_tabledescr=>create( l_struct_type ).
  CREATE DATA l_handler TYPE HANDLE l_table_type.
  CREATE DATA l_struct_handler TYPE HANDLE l_struct_type.
  ASSIGN l_handler->* TO <fs_tab>.
  ASSIGN l_struct_handler->* TO <fs_wa>.
  DO 10 TIMES.
    DO 9 TIMES.
      ASSIGN COMPONENT sy-index OF STRUCTURE <fs_wa> TO <fs_val>.
      <fs_val> = sy-index.
    ENDDO.
    APPEND <fs_wa> TO <fs_tab>.
  ENDDO.
ENDFORM.                    "prep_data

*&---------------------------------------------------------------------*
*&      Form  send_email
*&---------------------------------------------------------------------*
*       SEND EMAIL WITH WELL FORMATED EXCEL ATTACHMENT
*----------------------------------------------------------------------*
FORM send_email.
  DATA: l_bcs_exception TYPE REF TO cx_bcs.
*Create persistent send request
  TRY.
      w_send_request = cl_bcs=>create_persistent( ).
      APPEND: '<html><body>'                         TO w_text,
                 'Hi,'                               TO w_text,
                 '<p>the attached is the excel!</p>' TO w_text,
              '</body></html>'                       TO w_text.
*Create document from internal table with text
      w_doc = cl_document_bcs=>create_document(
*             i_type = 'RAW'
                 i_type = 'HTM'
                 i_text = w_text
                 i_subject = 'CL_BCS DEMO'
               ).
*attachment
      CALL METHOD w_doc->add_attachment
        EXPORTING
          i_attachment_type    = 'XLS'
          i_attachment_subject = 'TEST'
          i_att_content_hex    = it_excel.
*add document to send request
      CALL METHOD w_send_request->set_document( w_doc ).
*set sender
      w_sender = cl_sapuser_bcs=>create( sy-uname ).
      CALL METHOD w_send_request->set_sender
        EXPORTING
          i_sender = w_sender.
*add recipient
      w_recipient = cl_cam_address_bcs=>create_internet_address( p_addr ).
      CALL METHOD w_send_request->add_recipient
        EXPORTING
          i_recipient = w_recipient
          i_express   = 'X'.
*send document
      CALL METHOD w_send_request->set_send_immediately( 'X' ).
      CALL METHOD w_send_request->send(
        EXPORTING
          i_with_error_screen = 'X'
        RECEIVING
          result              = w_sent_to_all
      ).
      IF w_sent_to_all EQ 'X'.
        WRITE: / 'Sent successfully!'.
      ENDIF.
      COMMIT WORK.
    CATCH cx_bcs INTO l_bcs_exception.
  ENDTRY.
ENDFORM.                    "send_email