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.
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.
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.