This is the extension of my previous 2 blogs:

Blog 1: Describes on working with XSL Transformation

Download MS Excel with formatting options using XSLT – Part 1

Blog 2: Describes on downloading the Excel created using XSL Transformation into Presentation Server

Download MS Excel with formatting options using XSLT – Part 2

In this blog, we are going to attach the generated Excel to the email and send it to external Email IDs.

As discussed in Blog 2, the Output of the XSL Transformation is an XML String.

Step 1:

Convert the XML string into Internal table

Step 2:

Attach the Internal table contents to the mail and send the mail.

We use the Class ‘CL_BCS’ to achieve this.

The Code Snippet is given below. This is easily understandable and ready to use as it is (dont forget to change the email id).

REPORT  zdemo_excel_xslt.

TYPES: BEGIN OF ty_data,
        carrid    TYPE s_carr_id,
        connid    TYPE s_conn_id,
        fldate    TYPE s_date,
        price     TYPE s_price,
        planetype TYPE s_planetye,
        seatsmax  TYPE s_seatsmax,
        seatsocc  TYPE s_seatsocc,
       END OF ty_data.

* Data declaration
DATA: gv_fullpath          TYPE string,
      gv_path              TYPE string,
      gv_user_action       TYPE i,
      gv_default_extension TYPE string,
      gv_default_file_name TYPE string,
      gv_file_filter       TYPE string,
      gv_filename          TYPE string,
      gv_xmlstr            TYPE string,
      gv_initialpath       TYPE string.

DATA: gt_xml_table         TYPE STANDARD TABLE OF string,
      gt_data              TYPE STANDARD TABLE OF ty_data,
      gt_content           TYPE bcsy_text.

PARAMETERS: r_down RADIOBUTTON GROUP gr1,
                           r_mail RADIOBUTTON GROUP gr1.

* Start of selection
START-OF-SELECTION.

  ” Populate internal table
  PERFORM get_data.

  ” Call XSL transformation
  CALL TRANSFORMATION z_excel_download
    SOURCE table = gt_data
           var = ‘Testing variable’
    RESULT XML gv_xmlstr.

  IF r_mail IS NOT INITIAL.
    PERFORM convert_to_table
              USING gv_xmlstr
              CHANGING gt_content.

    PERFORM send_mail USING gt_content.
  ENDIF.

  IF r_down IS NOT INITIAL.
    APPEND gv_xmlstr TO gt_xml_table.
    PERFORM download.
  ENDIF.

*&———————————————————————*
*&      Form  GET_DATA
*&———————————————————————*
*———————————————————————-*
FORM get_data .

  REFRESH gt_data[].
  SELECT carrid
         connid
         fldate
         price
         planetype
         seatsmax
         seatsocc
    FROM sflight
    INTO TABLE gt_data
    UP TO 50 ROWS.

ENDFORM.                    ” GET_DATA

*&———————————————————————*
*&      Form  DOWNLOAD
*&———————————————————————*
*———————————————————————-*
FORM download .

  ” Determine file type
  MOVE ‘.XLS’ TO gv_default_extension.
  MOVE ‘XLS files (.XLS)|.XLS’ TO gv_file_filter.

  ” Pop up to save file in presentation server
  CALL METHOD cl_gui_frontend_services=>file_save_dialog
    EXPORTING
      default_extension = gv_default_extension
      default_file_name = gv_default_file_name
      file_filter       = gv_file_filter
      initial_directory = gv_initialpath
    CHANGING
      filename          = gv_filename
      path              = gv_path
      fullpath          = gv_fullpath
      user_action       = gv_user_action
    EXCEPTIONS
      cntl_error        = 1
      error_no_gui      = 2
      OTHERS            = 3.
  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
               WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.

  ” Download the Excel in the desired location
  CALL FUNCTION ‘GUI_DOWNLOAD’
    EXPORTING
      filename                = gv_fullpath
      filetype                = ‘ASC’
    TABLES
      data_tab                = gt_xml_table
    EXCEPTIONS
      file_write_error        = 1
      no_batch                = 2
      gui_refuse_filetransfer = 3
      invalid_type            = 4
      OTHERS                  = 5.
  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
    WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.

ENDFORM.                    ” DOWNLOAD
*&———————————————————————*
*&      Form  SEND_MAIL
*&———————————————————————*
* Send Mail
*———————————————————————-*
FORM send_mail USING fpt_content TYPE bcsy_text.

  TYPES: BEGIN OF ty_receivers,
          rec_string TYPE adr6-smtp_addr,
          cc         TYPE flag,
          bcc        TYPE flag,
         END OF ty_receivers.

  DATA: lp_send_request     TYPE REF TO cl_bcs,
        lp_document         TYPE REF TO cl_document_bcs,
        lp_cx_bcs_exception TYPE REF TO cx_bcs,
        lp_recipient        TYPE REF TO if_recipient_bcs.

  DATA: lt_receivers      TYPE TABLE OF ty_receivers,
        lt_receivers_main TYPE TABLE OF ty_receivers,
        lt_addr_split     TYPE TABLE OF adr6-smtp_addr.

  DATA: ls_receivers      TYPE ty_receivers,
        ls_receivers_main TYPE ty_receivers,
        ls_addr_check     TYPE sx_address,
        ls_addr_valid     TYPE sx_address,
        ls_receive        TYPE ad_smtpadr.

  DATA: lv_subject        TYPE so_obj_des,
        lv_subject_str    TYPE string,
        lv_sent_result    TYPE os_boolean.

  DATA: lt_to_address     TYPE bcsy_smtpa,
        ls_to_address     LIKE LINE OF lt_to_address,
        lt_cc_address     TYPE bcsy_smtpa,
        lt_bcc_address    TYPE bcsy_smtpa,
        lv_head_address   TYPE ad_smtpadr.

  CLEAR ls_to_address.
  REFRESH lt_to_address[].
  ls_to_address = ‘xxx@xxx.com‘. ” Replace with Receivers Email address
  APPEND ls_to_address TO lt_to_address.

  TRY .
** Create Persistent Send Request
      lp_send_request = cl_bcs=>create_persistent( ).

      IF lp_send_request IS NOT INITIAL.
** Creation of office document
        lv_subject = ‘Test: Mail with Excel’.

        lp_document = cl_document_bcs=>create_document(
                        i_type    = ‘XLS’
                        i_subject = lv_subject
                        i_text    = fpt_content ).

** Add document to send request
        CALL METHOD lp_send_request->set_document( lp_document ).

** Set subject string
        lv_subject_str = ‘Test: Mail with Excel’.
        CALL METHOD lp_send_request->set_message_subject
          EXPORTING
            ip_subject = lv_subject_str.

* Receivers
        CLEAR lt_receivers.
        lv_head_address = ‘xxx@xxx.com‘. ” Replace with Receivers Email address
        IF lv_head_address IS NOT INITIAL.
          CLEAR ls_receivers.
          ls_receivers-rec_string = lv_head_address.
          TRANSLATE ls_receivers-rec_string TO LOWER CASE. “#EC TRANSLANG
          APPEND ls_receivers TO lt_receivers.
          CLEAR ls_receivers.
        ENDIF.

** To Address
        LOOP AT lt_to_address INTO ls_receive.
          CLEAR ls_receivers.
          ls_receivers-rec_string = ls_receive.
          TRANSLATE ls_receivers-rec_string TO LOWER CASE. “#EC TRANSLANG
          APPEND ls_receivers TO lt_receivers.
          CLEAR ls_receivers.
        ENDLOOP.

** CC Address
        LOOP AT lt_cc_address INTO ls_receive.
          CLEAR ls_receivers.
          ls_receivers-rec_string = ls_receive.
          ls_receivers-cc         = ‘X’.
          TRANSLATE ls_receivers-rec_string TO LOWER CASE. “#EC TRANSLANG
          APPEND ls_receivers TO lt_receivers.
          CLEAR ls_receivers.
        ENDLOOP.
** CC Address
        LOOP AT lt_bcc_address INTO ls_receive.
          CLEAR ls_receivers.
          ls_receivers-rec_string = ls_receive.
          ls_receivers-bcc         = ‘X’.
          TRANSLATE ls_receivers-rec_string TO LOWER CASE. “#EC TRANSLANG
          APPEND ls_receivers TO lt_receivers.
          CLEAR ls_receivers.
        ENDLOOP.

** Scenarios where mail id are provided separated by ‘,’ split is done here
        LOOP AT lt_receivers INTO ls_receivers.
          CLEAR lt_addr_split.
          SPLIT ls_receivers-rec_string AT ‘,’ INTO TABLE lt_addr_split.
          CHECK lt_addr_split IS NOT INITIAL.
          CLEAR ls_receivers_main.
          LOOP AT lt_addr_split INTO ls_receivers_main-rec_string.
            SHIFT ls_receivers_main-rec_string LEFT DELETING LEADING space.
            ls_receivers_main-cc = ls_receivers-cc.
            ls_receivers_main-bcc = ls_receivers-bcc.
            APPEND ls_receivers_main TO lt_receivers_main.
            CLEAR ls_receivers_main.
          ENDLOOP.
        ENDLOOP.

        IF lt_receivers_main IS NOT INITIAL.
          SORT lt_receivers_main.
          DELETE ADJACENT DUPLICATES FROM lt_receivers_main COMPARING rec_string.
          LOOP AT lt_receivers_main INTO ls_receivers_main.
            ls_addr_check-type = ‘INT’.
            ls_addr_check-address = ls_receivers_main-rec_string.
            CLEAR ls_addr_valid.
            CALL FUNCTION ‘SX_INTERNET_ADDRESS_TO_NORMAL’
              EXPORTING
                address_unstruct    = ls_addr_check
              IMPORTING
                address_normal      = ls_addr_valid
              EXCEPTIONS
                error_address_type  = 1
                error_address       = 2
                error_group_address = 3
                OTHERS              = 4.
            IF sy-subrc EQ 0.
              ls_receivers_main-rec_string = ls_addr_valid-address.
              lp_recipient = cl_cam_address_bcs=>create_internet_address( ls_receivers_main-rec_string ).
**  Add receipent to send request
              CALL METHOD lp_send_request->add_recipient
                EXPORTING
                  i_recipient  = lp_recipient
                  i_express    = ‘ ‘
                  i_copy       = ls_receivers_main-cc
                  i_blind_copy = ls_receivers_main-bcc.
            ENDIF.
          ENDLOOP.
        ENDIF.

** Send mail Immediately
        CALL METHOD lp_send_request->set_send_immediately( ‘X’ ).

** Send Mail
        CLEAR lv_sent_result.
        CALL METHOD lp_send_request->send(
          EXPORTING
            i_with_error_screen = ‘X’
          RECEIVING
            result              = lv_sent_result ).
        IF lv_sent_result IS INITIAL.
          ” Error Message
        ENDIF.

        WRITE: ‘Mail sent’.
        COMMIT WORK.

      ENDIF.
    CATCH cx_bcs INTO lp_cx_bcs_exception.
      ” Handle Exception here
      EXIT.
  ENDTRY.

ENDFORM.                    ” SEND_MAIL
*&———————————————————————*
*&      Form  CONVERT_TO_TABLE
*&———————————————————————*
* Convert string to table
*———————————————————————-*
FORM convert_to_table  USING    fpv_xmlstr  TYPE string
                       CHANGING fpt_content TYPE bcsy_text.

  DATA: lv_str_len TYPE i,
        lv_lines TYPE i,
        lv_mod TYPE i,
        lv_offset TYPE i,
        lv_offset1 TYPE i,
        lv_length TYPE i,
        lv_soli TYPE char255,
        lv_xmlstr TYPE string,
        lv_exit TYPE char1,
        ls_content LIKE LINE OF fpt_content.

  lv_str_len = STRLEN( fpv_xmlstr ).
  lv_lines = lv_str_len / 255.
  lv_mod = lv_str_len MOD 255.
  IF lv_mod GT 0.
    lv_lines = lv_lines + 1.
  ENDIF.

  lv_xmlstr = fpv_xmlstr.
  lv_length = 255.
  CLEAR: lv_offset, lv_offset1, lv_exit.
  DO lv_lines TIMES.
    lv_offset1 = lv_offset + 255.
    IF lv_offset1 GT lv_str_len.
      lv_length = lv_str_len – lv_offset.
      lv_exit = ‘X’.
    ENDIF.
    lv_soli = lv_xmlstr+lv_offset(lv_length).
    ls_content = lv_soli.
    APPEND ls_content TO fpt_content.
    IF lv_exit IS NOT INITIAL.
      EXIT.
    ENDIF.
    lv_offset = lv_offset + 255.
  ENDDO.

ENDFORM.                    ” CONVERT_TO_TABLE

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply