SAP - Custom FM to Send XLSX Attachments In Mail - Reusable Asset

Generally when there is a requirement, to send excel attachments in a mail, we face issues with XLSX format. XLS Attachment are easy to do, but the formatting of data is an issue . Also if we are able to resolve the same Through HTML Templates, the size of attachment sometimes becomes an issue.

The benefit of sending XLSX attachment is formatting is automatically done, auto width optimization is taken care of and data issues are not observed.

Here we have created a reusable function module which can be used in any program to create a XLSX attachment in any mail program.


Function Module: ZSEND_MAIL_XLSX_ATTACHMENT

ATTRIBUTES:

IMPORTING PARAMETERS:


TABLES:


STRUCTURE:


EXCEPTION:


CODE FOR FM:

FUNCTION zsend_mail_xlsx_attachment.
*"----------------------------------------------------------------------
*"*"Local Interface:
*"  IMPORTING
*"     VALUE(I_BODY) TYPE  SOLI_TAB
*"     VALUE(I_FIELDCAT) TYPE  LVC_T_FCAT
*"     VALUE(I_FILENAME) TYPE  SO_OBJ_DES
*"     VALUE(I_SUBJECT) TYPE  SO_OBJ_DES
*"     VALUE(I_SENDER) TYPE  AD_SMTPADR
*"  TABLES
*"      T_DATA TYPE  STANDARD TABLE
*"      T_RECEIVER STRUCTURE  ZRECEIVER_LIST
*"  EXCEPTIONS
*"      ERROR_TRANSFORM
*"----------------------------------------------------------------------

  DATA: lv_version TYPE string, " Version
        lv_flavour TYPE fpm_file_name, " Flavour
        lv_result  TYPE os_boolean,
        lv_string  TYPE xstring, "vac53733 10642
        lv_size    TYPE sood-objlen,
        lv_filetyp TYPE salv_bs_constant,
        lv_length  TYPE so_obj_len,
        lv_len     TYPE i.

  DATA: send_request  TYPE REF TO cl_bcs,
        document      TYPE REF TO cl_document_bcs,
        sender1        TYPE REF TO cl_sapuser_bcs,
        sender        TYPE REF TO cl_cam_address_bcs,
        recipient     TYPE REF TO if_recipient_bcs,
        bcs_exception TYPE REF TO cx_bcs,
        lc_result     TYPE REF TO cl_salv_ex_result_data_table,
        lc_data       TYPE REF TO data.

*Internal table declarations.
  DATA: lt_header    TYPE soli_tab,
        lt_binary    TYPE solix_tab,
        lwa_header   TYPE soli,
        lwa_receiver TYPE zreceiver_list.

  lv_length = 255.
  TRY.
      send_request = cl_bcs=>create_persistent( ).

      document = cl_document_bcs=>create_document(
                              i_type    = 'RAW'
                              i_text    = i_body
                              i_length  = lv_length
                              i_subject = i_subject ).

      GET REFERENCE OF t_data[] INTO lc_data.

      CLEAR: lc_result.
      lc_result =
      cl_salv_ex_util=>factory_result_data_table(
           r_data           = lc_data
           t_fieldcatalog   = i_fieldcat ).

      CLEAR: lv_version.
      lv_version = cl_salv_bs_a_xml_base=>get_version( ).

      CLEAR: lv_filetyp.
      lv_filetyp = if_salv_bs_xml=>c_type_xlsx.

      CLEAR: lv_flavour.
      lv_flavour = if_salv_bs_c_tt=>c_tt_xml_flavour_export.

      CLEAR: lv_string.
      CALL METHOD cl_salv_bs_tt_util=>if_salv_bs_tt_util~transform
        EXPORTING
          xml_type      = lv_filetyp
          xml_version   = lv_version
          r_result_data = lc_result
          xml_flavour   = lv_flavour
          gui_type      = if_salv_bs_xml=>c_gui_type_gui
        IMPORTING
          xml           = lv_string.

      CLEAR: lt_binary, lv_length.

      CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
        EXPORTING
          buffer        = lv_string
        IMPORTING
          output_length = lv_len
        TABLES
          binary_tab    = lt_binary.

      CONCATENATE '&SO_FILENAME=' i_filename '.XLSX' INTO lwa_header.
      APPEND lwa_header TO lt_header.

      lv_size = xstrlen( lv_string ).

      CALL METHOD document->add_attachment
        EXPORTING
          i_attachment_type    = 'BIN' " type of data
          i_attachment_subject = i_filename
          i_attachment_size    = lv_size   " XTRING length of lv_string
          i_att_content_hex    = lt_binary
          i_attachment_header  = lt_header. "file details

*     add document to send request
      CALL METHOD send_request->set_document( document ).
      IF i_sender IS NOT INITIAL.

        sender = cl_cam_address_bcs=>create_internet_address(
                                              i_sender ).

        send_request->set_sender( sender ).
      ELSE.

        sender1 = cl_sapuser_bcs=>create( sy-uname ).
        CALL METHOD send_request->set_sender
          EXPORTING
            i_sender = sender1.
      ENDIF.
      LOOP AT t_receiver INTO lwa_receiver.
        recipient = cl_cam_address_bcs=>create_internet_address(
                                              lwa_receiver-smtp_addr ).

        CALL METHOD send_request->add_recipient
          EXPORTING
            i_recipient  = recipient
            i_express    = lwa_receiver-express
            i_copy       = lwa_receiver-copy
            i_blind_copy = lwa_receiver-blind_copy
            i_no_forward = lwa_receiver-no_forward.
      ENDLOOP.

*     ---------- send document ---------------------------------------
      CALL METHOD send_request->send(
        EXPORTING
          i_with_error_screen = 'X'
        RECEIVING
          result              = lv_result ).
      IF lv_result = 'X'.
        WRITE 'Document Sent Successfully'(098).
      ENDIF.

      COMMIT WORK.
    CATCH cx_bcs INTO bcs_exception.
      RAISE error_transform.
      EXIT.
  ENDTRY.
ENDFUNCTION.

HOW TO USE FM:

Sample Code:

REPORT ztest_mail_xlsx.

TYPES: BEGIN OF gty_bseg,
         bukrs TYPE bseg-bukrs,
         belnr TYPE bseg-belnr,
         gjahr TYPE bseg-gjahr,
         buzei TYPE bseg-buzei,
         bschl TYPE bseg-bschl,
         koart TYPE bseg-koart,
         sgtxt TYPE bseg-sgtxt,
       END OF gty_bseg.

DATA: gt_bseg  TYPE TABLE OF gty_bseg,
      gwa_bseg TYPE gty_bseg.

DATA: lv_filename TYPE so_obj_des,
      lv_subject  TYPE so_obj_des.

DATA: gt_body      TYPE TABLE OF soli,
      gwa_body     TYPE soli,
      gt_fieldcat  TYPE TABLE OF lvc_s_fcat,
      gwa_fieldcat TYPE lvc_s_fcat,
      gt_receiver  TYPE TABLE OF zreceiver_list,
      gwa_receiver TYPE zreceiver_list.

SELECT bukrs belnr gjahr buzei bschl koart sgtxt UP TO 50 ROWS
  FROM bseg
  INTO TABLE gt_bseg.

lv_filename = 'BSEG Entries'.
lv_subject = 'Check The Entries In BSEG.'.

****Mail ID's
gwa_receiver-smtp_addr = 'abc@xyz.com'.
gwa_receiver-express = 'X'.
APPEND gwa_receiver TO gt_receiver.
CLEAR: gwa_receiver.

gwa_receiver-smtp_addr = 'def@xyz.com'.
gwa_receiver-express = 'X'.
gwa_receiver-copy = 'X'.
APPEND gwa_receiver TO gt_receiver.
CLEAR: gwa_receiver.

***Body***
gwa_body = 'Dear Customer,'.
APPEND gwa_body TO gt_body.

CLEAR gwa_body.
APPEND gwa_body TO gt_body.

CONCATENATE 'Please find attached statement of accounts as on' '31.12.9999' '.' INTO gwa_body SEPARATED BY space.
APPEND gwa_body TO gt_body.

CLEAR gwa_body.
APPEND gwa_body TO gt_body.

gwa_body = 'Request you to kindly check and confirm the balances.'.
APPEND gwa_body TO gt_body.

CLEAR gwa_body.
APPEND gwa_body TO gt_body.

gwa_body = 'Note: Discrepanies In The Balance Needs To Be Notified Within 7 Days Of Receipt Of Statement; Else The Balance Would Be Considered As Confirmed.'.
APPEND gwa_body TO gt_body.

CLEAR gwa_body.
APPEND gwa_body TO gt_body.
CLEAR gwa_body.
APPEND gwa_body TO gt_body.

gwa_body = 'Regards,'.
APPEND gwa_body TO gt_body.

gwa_body = 'Finance Backoffice Team'.
APPEND gwa_body TO gt_body.

****Field Catalog*****
gwa_fieldcat-fieldname = 'BUKRS'.
gwa_fieldcat-reptext   = 'CCode'.
APPEND gwa_fieldcat TO gt_fieldcat.
CLEAR: gwa_fieldcat.

gwa_fieldcat-fieldname = 'BELNR'.
gwa_fieldcat-reptext   = 'Doc.No.'.
APPEND gwa_fieldcat TO gt_fieldcat.
CLEAR: gwa_fieldcat.

gwa_fieldcat-fieldname = 'GJAHR'.
gwa_fieldcat-reptext   = 'Doc.Year'.
APPEND gwa_fieldcat TO gt_fieldcat.
CLEAR: gwa_fieldcat.

gwa_fieldcat-fieldname = 'BUZEI'.
gwa_fieldcat-reptext   = 'Item No.'.
APPEND gwa_fieldcat TO gt_fieldcat.
CLEAR: gwa_fieldcat.

gwa_fieldcat-fieldname = 'BSCHL'.
gwa_fieldcat-reptext   = 'Doc Type'.
APPEND gwa_fieldcat TO gt_fieldcat.
CLEAR: gwa_fieldcat.

gwa_fieldcat-fieldname = 'KOART'.
gwa_fieldcat-reptext   = 'Field.No.'.
APPEND gwa_fieldcat TO gt_fieldcat.
CLEAR: gwa_fieldcat.

gwa_fieldcat-fieldname = 'SGTXT'.
gwa_fieldcat-reptext   = 'Long Text'.
APPEND gwa_fieldcat TO gt_fieldcat.
CLEAR: gwa_fieldcat.
****************************
CALL FUNCTION 'ZSEND_MAIL_XLSX_ATTACHMENT'
  EXPORTING
    i_body          = gt_body
    i_fieldcat      = gt_fieldcat
    i_filename      = lv_filename
    i_subject       = lv_subject
    i_sender        = 'ghi@xyz.com'
  TABLES
    t_data          = gt_bseg
    t_receiver      = gt_receiver
  EXCEPTIONS
    error_transform = 1
    OTHERS          = 2.
IF sy-subrc <> 0.
* Implement suitable error handling here
ENDIF.





In short, Sending Mails with XLSX attachments is so easy by  using this particular FM.

Share your views on the same or any idea of improving the same object.

Comments

Also Read...