SAP-Sending Mail With XLSX Attachments Using CL_DOCUMENT_BCS.
Before sending an email, all the required configuration for SMTP and mail server in the SAP server needs to be in place.
Email sending is an existing method of getting the sap job log or report details. People who receive an email in their inbox will do different operations based on the content in it. Email may contain the description alone or with the attached files in it and this is based on the business need.
Sending external email in SAP with attachments like XML, DOC, XLS are the common methodology and now SAP users would like to receive the attachments with XLSX, DOCX - formats as this is the format from MS office 2007. We are going to see in details for how to create XLSX (Generally for four digit file extensions) file attachment and this is the slightly different way than how we do for usual binary XLS attachments in sap external email sending.
Why slightly different approach for XLSX attachment from XLS ?
- XLSX for versions since 2007 MS office, but, XLS is from 2003.
- XLSX is only readable by versions 2007 and later XLS is a proprietary binary format while XLSX is based on Office Open XML format.
- XLSX is not able to support macros while XLS is. We have XLSM extension to support macros from office 2007
- XLS is based on Binary Interchange File Format, the information is directly stored to a binary format. XLSX is based on the Office Open XML format, a file format that was derived from XML. The information in an XLSX file is stored in a text file that uses XML to define all its parameters.
SAMPLE REQUIREMENT:
Let’s take an example of a business scenario where user needs the report of list of materials which are deletion indicator set at particular plant level. This report is needed to be sent to the responsible person who do analysis and validate its status. Instead of displaying in ALV report or keeping the application server file. User needs this report to be available in his /her email as a XLSX excel attachment report.
DETAIL STEPS:
Below steps will details the procedure you need to follow in your program to send the email with XLSX attachment from ABAP program. Sample program with complete code is attached in additional information section. Based on your business need you can modify the contents in the attached file.
STEP 1: Declarations Required.
We have to use CL_BCS class based email sending technique, so the first step in this development is to create the instance declarations for required classes / interfaces as below:
CL_BCS is the class which sends the email notification to the group of people assigned as recipient, below code will return the reference of CL_BCS class.
To create the email body CL_DOCUMENT_BCS is used. Below is the way this object reference is created in the program.
STEP 2: Prepare The Final Data To Be Sent In Attachment.
This is the step on which you will be preparing the final table of data. Based on the customer requirement, we need to populate an internal table for attachment file. In this sample program, I have created a structure with Material, Plant-specific material status, purchasing group and profit center.
In the sample code, I have created a select query to read the relevant entries from MARC based on this sample requirement.
We need to prepare the field catalog for internal table we are using. Populate the field catalog as specified in the sample program with the structure of LVC_S_FCAT.
Example of one field provided below:
This field catalog is used to create the header in output XLSX file.
Create the data object reference for the internal table I_LINE as below:
Create the utility class reference as below:
STEP 3: Get XML Version & Prepare The XSTRING Value For Internal Table Entries.
Below is the way to get the current systems XML version and get the XML flavor to be used.
Below step will convert the output attachment data in to XSTRING format based on the XML version and flavors passed.
STEP 4: Create File Name & Attachment Object Reference.
Create the file name with XLSX extension (Generally 4 digit extensions), also below logic will provide the user to enter the fee text option while opening the file from email and save in local system.
Create the attachment document reference and assign it to send request object.
Step 5: Set Sender & Recipient Details.
Sender object creation is this is necessary only if you want to set the sender different from actual user (SY-UNAME). Otherwise sender is set automatically with actual user.
Recipient can be added in the below way,
STEP 6: Final Step To Send The Email.
Below instance method will send the email with the attachment and returns the success flag as well. This is the final step in sending the email, next step is the error handling option for any exception occurred.
We need the commit work to be given explicitly for email sending.
STEP 7: Exception Handling
General BCS exception class can be used for this purpose.
Below is the way to catch the exception and display the error occurred
SAMPLE PROGRAM CODE:
REPORT zl_xlsx_emailsend.
*This report will show the sample of how to send XLSX attachments to email address from sap.
* Types declaration.
TYPES: BEGIN OF ty_line,
matnr TYPE matnr,"Material Number
mmsta TYPE mmsta,"Plant-Specific Material Status
ekgrp TYPE ekgrp,"Purchasing Group
prctr TYPE prctr,"Profit Center
END OF ty_line.
*Data declaration
DATA:
l_version TYPE string, " Version
l_flavour TYPE fpm_file_name, " Flavour
l_sent_to_all TYPE os_boolean,
l_contents_line TYPE xstring, "vac53733 10642
l_i TYPE sood-objlen,
l_file_type TYPE salv_bs_constant,
l_count TYPE so_obj_len," string length of email body
l_count1 TYPE i,
w_line TYPE ty_line. "work area for data table
* Business Communication Service object
DATA: send_request TYPE REF TO cl_bcs.
* Wrapper Class for Office Documents
DATA: document TYPE REF TO cl_document_bcs.
* Object Represents an SAP User
DATA: sender TYPE REF TO cl_sapuser_bcs.
* Interface of Recipient Object in BCS
DATA: recipient TYPE REF TO if_recipient_bcs.
*BCS: General Exceptions
DATA: bcs_exception TYPE REF TO cx_bcs.
* Utility Class
DATA: lo_result_data TYPE REF TO cl_salv_ex_result_data_table.
* Data
DATA: lo_data TYPE REF TO data.
*Internal table declarations.
DATA: i_fldcat TYPE STANDARD TABLE OF lvc_s_fcat
INITIAL SIZE 0, "Field catalog
i_text TYPE bcsy_text, "report internal table
i_binary_content TYPE solix_tab,
i_att_head TYPE soli_tab,
i_obj_bin TYPE solix_tab, "Binary Internal Table
l_text_line TYPE soli,
l_filename TYPE string.
DATA: i_line TYPE STANDARD TABLE OF ty_line INITIAL SIZE 0.
* Field symbol declaration.
FIELD-SYMBOLS: <fs_fieldcat> TYPE lvc_s_fcat. "Field catalog
* Selection screen parameter
PARAMETERS: p_werks TYPE marc-werks OBLIGATORY.
INITIALIZATION.
p_werks = 'XXXX'. "initialize default plant
START-OF-SELECTION.
PERFORM main.
*---------------------------------------------------------------------*
* FORM main *
*---------------------------------------------------------------------*
FORM main.
* Data selection to be sent.
SELECT matnr "Material Number
mmsta "Plant-Specific Material Status
ekgrp "Purchasing Group
prctr "Profit Center
FROM marc
INTO TABLE i_line
WHERE werks = p_werks AND
lvorm = 'X'.
* Preare the fieldcat for output structure.
APPEND INITIAL LINE TO i_fldcat
ASSIGNING <fs_fieldcat>.
<fs_fieldcat>-no_out = ''. "Hide
<fs_fieldcat>-fieldname = 'MATNR'. "Fieldname
<fs_fieldcat>-outputlen = 18. "Output length
<fs_fieldcat>-dd_outlen = 18. "Input length
<fs_fieldcat>-reptext = 'Material Number'. "Column text
<fs_fieldcat>-edit = 'X'. "Edit
APPEND INITIAL LINE TO i_fldcat
ASSIGNING <fs_fieldcat>.
<fs_fieldcat>-no_out = ''. "Hide
<fs_fieldcat>-fieldname = 'MMSTA'. "Fieldname
<fs_fieldcat>-outputlen = 2. "Output length
<fs_fieldcat>-dd_outlen = 2. "Input length
<fs_fieldcat>-reptext = 'Plant-Specific mat.status'. "Column text
<fs_fieldcat>-edit = 'X'. "Edit
APPEND INITIAL LINE TO i_fldcat
ASSIGNING <fs_fieldcat>.
<fs_fieldcat>-no_out = ''. "Hide
<fs_fieldcat>-fieldname = 'EKGRP'. "Fieldname
<fs_fieldcat>-outputlen = 3. "Output length
<fs_fieldcat>-dd_outlen = 3. "Input length
<fs_fieldcat>-reptext = 'Purch.group'. "Column text
<fs_fieldcat>-edit = 'X'. "Edit
APPEND INITIAL LINE TO i_fldcat
ASSIGNING <fs_fieldcat>.
<fs_fieldcat>-no_out = ''. "Hide
<fs_fieldcat>-fieldname = 'PRCTR'. "Fieldname
<fs_fieldcat>-outputlen = 10. "Output length
<fs_fieldcat>-dd_outlen = 10. "Input length
<fs_fieldcat>-reptext = 'Profit center'. "Column text
<fs_fieldcat>-edit = 'X'. "Edit
TRY.
* -------- create persistent send request ------------------------
send_request = cl_bcs=>create_persistent( ).
APPEND 'List of materials having deletion flag at plant'(a01) TO i_text.
APPEND p_werks TO i_text.
l_count = 255.
* -------- create and set document with attachment ---------------
document = cl_document_bcs=>create_document(
i_type = 'RAW' " Type of data
i_text = i_text " Email body text
i_length = l_count " String length of email body
i_subject = 'Materials deleted in Plant' ).
* Create data object reference.
GET REFERENCE OF i_line INTO lo_data.
CLEAR: lo_result_data.
lo_result_data =
cl_salv_ex_util=>factory_result_data_table(
r_data = lo_data " internal table data reference
t_fieldcatalog = i_fldcat"field cat
).
* get the version from Abstract Super Class for All Transformations
CLEAR: l_version.
l_version = cl_salv_bs_a_xml_base=>get_version( ).
* Get the file Type
* value 10 for XLSX file type
CLEAR: l_file_type.
l_file_type = if_salv_bs_xml=>c_type_xlsx.
* Get the flavour export
* Flavor for Complete ALV XML
CLEAR: l_flavour.
l_flavour = if_salv_bs_c_tt=>c_tt_xml_flavour_export.
* Transformation of data to XSTRING
CLEAR: l_contents_line.
CALL METHOD cl_salv_bs_tt_util=>if_salv_bs_tt_util~transform
EXPORTING
xml_type = l_file_type
xml_version = l_version
r_result_data = lo_result_data
xml_flavour = l_flavour
gui_type = if_salv_bs_xml=>c_gui_type_gui
IMPORTING
xml = l_contents_line.
* Converting the table contents from xstring to binary
CLEAR i_obj_bin.
CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
EXPORTING
buffer = l_contents_line
IMPORTING
output_length = l_count1
TABLES
binary_tab = i_obj_bin.
APPEND LINES OF i_obj_bin TO i_binary_content.
* Four character file extension '.XLSX' is set
l_filename = 'Materialsdeleted.XLSX'. " provide the extension in file name
* You can set a file name with a freely selectable file name extension
* separately from the document description. For this, use the header table
* of the document or the attachment with the key word '&SO_FILENAME='.
CONCATENATE '&SO_FILENAME='
l_filename
INTO
l_text_line.
APPEND l_text_line TO i_att_head.
*Calculate the xstring length
l_i = xstrlen( l_contents_line ).
* Create the attachment reference.
CALL METHOD document->add_attachment
EXPORTING
i_attachment_type = 'BIN' " type of data
i_attachment_subject = 'File with deleted materials'
i_attachment_size = l_i " XTRING length of l_contents_line
i_att_content_hex = i_binary_content
i_attachment_header = i_att_head. "file details
* add document to send request
CALL METHOD send_request->set_document( document ).
* --------- set sender -------------------------------------------
* note: this is necessary only if you want to set the sender
* different from actual user (SY-UNAME). Otherwise sender is
* set automatically with actual user.
"Provide the SAP logon user name to sent the sender (this example user sy-uname)
* You can use uer name as ('VAK10567', etc)
sender = cl_sapuser_bcs=>create( sy-uname ).
CALL METHOD send_request->set_sender
EXPORTING
i_sender = sender.
* --------- add recipient (e-mail address) -----------------------
* create recipient - please replace e-mail address !!!
* you can create multiple receipient by following this way or
* you can provide undisclosed recipients list
recipient = cl_cam_address_bcs=>create_internet_address(
'xxx@yyy.com' ).
* add recipient with its respective attributes to send request
CALL METHOD send_request->add_recipient
EXPORTING
i_recipient = recipient
i_express = 'X'.
* ---------- send document ---------------------------------------
CALL METHOD send_request->send(
EXPORTING
i_with_error_screen = 'X'
RECEIVING
result = l_sent_to_all ).
IF l_sent_to_all = 'X'.
WRITE 'Document Sent Successfully'(098).
ENDIF.
COMMIT WORK.
* -----------------------------------------------------------
* * Exception Handling
* -----------------------------------------------------------
* * Replace this very rudimentary exception handling
* * With your own one !!!
* -----------------------------------------------------------
CATCH cx_bcs INTO bcs_exception.
WRITE: 'Error occured'(001).
WRITE: 'Error type'(002), bcs_exception->error_type.
EXIT.
ENDTRY.
ENDFORM. "main
*This report will show the sample of how to send XLSX attachments to email address from sap.
* Types declaration.
TYPES: BEGIN OF ty_line,
matnr TYPE matnr,"Material Number
mmsta TYPE mmsta,"Plant-Specific Material Status
ekgrp TYPE ekgrp,"Purchasing Group
prctr TYPE prctr,"Profit Center
END OF ty_line.
*Data declaration
DATA:
l_version TYPE string, " Version
l_flavour TYPE fpm_file_name, " Flavour
l_sent_to_all TYPE os_boolean,
l_contents_line TYPE xstring, "vac53733 10642
l_i TYPE sood-objlen,
l_file_type TYPE salv_bs_constant,
l_count TYPE so_obj_len," string length of email body
l_count1 TYPE i,
w_line TYPE ty_line. "work area for data table
* Business Communication Service object
DATA: send_request TYPE REF TO cl_bcs.
* Wrapper Class for Office Documents
DATA: document TYPE REF TO cl_document_bcs.
* Object Represents an SAP User
DATA: sender TYPE REF TO cl_sapuser_bcs.
* Interface of Recipient Object in BCS
DATA: recipient TYPE REF TO if_recipient_bcs.
*BCS: General Exceptions
DATA: bcs_exception TYPE REF TO cx_bcs.
* Utility Class
DATA: lo_result_data TYPE REF TO cl_salv_ex_result_data_table.
* Data
DATA: lo_data TYPE REF TO data.
*Internal table declarations.
DATA: i_fldcat TYPE STANDARD TABLE OF lvc_s_fcat
INITIAL SIZE 0, "Field catalog
i_text TYPE bcsy_text, "report internal table
i_binary_content TYPE solix_tab,
i_att_head TYPE soli_tab,
i_obj_bin TYPE solix_tab, "Binary Internal Table
l_text_line TYPE soli,
l_filename TYPE string.
DATA: i_line TYPE STANDARD TABLE OF ty_line INITIAL SIZE 0.
* Field symbol declaration.
FIELD-SYMBOLS: <fs_fieldcat> TYPE lvc_s_fcat. "Field catalog
* Selection screen parameter
PARAMETERS: p_werks TYPE marc-werks OBLIGATORY.
INITIALIZATION.
p_werks = 'XXXX'. "initialize default plant
START-OF-SELECTION.
PERFORM main.
*---------------------------------------------------------------------*
* FORM main *
*---------------------------------------------------------------------*
FORM main.
* Data selection to be sent.
SELECT matnr "Material Number
mmsta "Plant-Specific Material Status
ekgrp "Purchasing Group
prctr "Profit Center
FROM marc
INTO TABLE i_line
WHERE werks = p_werks AND
lvorm = 'X'.
* Preare the fieldcat for output structure.
APPEND INITIAL LINE TO i_fldcat
ASSIGNING <fs_fieldcat>.
<fs_fieldcat>-no_out = ''. "Hide
<fs_fieldcat>-fieldname = 'MATNR'. "Fieldname
<fs_fieldcat>-outputlen = 18. "Output length
<fs_fieldcat>-dd_outlen = 18. "Input length
<fs_fieldcat>-reptext = 'Material Number'. "Column text
<fs_fieldcat>-edit = 'X'. "Edit
APPEND INITIAL LINE TO i_fldcat
ASSIGNING <fs_fieldcat>.
<fs_fieldcat>-no_out = ''. "Hide
<fs_fieldcat>-fieldname = 'MMSTA'. "Fieldname
<fs_fieldcat>-outputlen = 2. "Output length
<fs_fieldcat>-dd_outlen = 2. "Input length
<fs_fieldcat>-reptext = 'Plant-Specific mat.status'. "Column text
<fs_fieldcat>-edit = 'X'. "Edit
APPEND INITIAL LINE TO i_fldcat
ASSIGNING <fs_fieldcat>.
<fs_fieldcat>-no_out = ''. "Hide
<fs_fieldcat>-fieldname = 'EKGRP'. "Fieldname
<fs_fieldcat>-outputlen = 3. "Output length
<fs_fieldcat>-dd_outlen = 3. "Input length
<fs_fieldcat>-reptext = 'Purch.group'. "Column text
<fs_fieldcat>-edit = 'X'. "Edit
APPEND INITIAL LINE TO i_fldcat
ASSIGNING <fs_fieldcat>.
<fs_fieldcat>-no_out = ''. "Hide
<fs_fieldcat>-fieldname = 'PRCTR'. "Fieldname
<fs_fieldcat>-outputlen = 10. "Output length
<fs_fieldcat>-dd_outlen = 10. "Input length
<fs_fieldcat>-reptext = 'Profit center'. "Column text
<fs_fieldcat>-edit = 'X'. "Edit
TRY.
* -------- create persistent send request ------------------------
send_request = cl_bcs=>create_persistent( ).
APPEND 'List of materials having deletion flag at plant'(a01) TO i_text.
APPEND p_werks TO i_text.
l_count = 255.
* -------- create and set document with attachment ---------------
document = cl_document_bcs=>create_document(
i_type = 'RAW' " Type of data
i_text = i_text " Email body text
i_length = l_count " String length of email body
i_subject = 'Materials deleted in Plant' ).
* Create data object reference.
GET REFERENCE OF i_line INTO lo_data.
CLEAR: lo_result_data.
lo_result_data =
cl_salv_ex_util=>factory_result_data_table(
r_data = lo_data " internal table data reference
t_fieldcatalog = i_fldcat"field cat
).
* get the version from Abstract Super Class for All Transformations
CLEAR: l_version.
l_version = cl_salv_bs_a_xml_base=>get_version( ).
* Get the file Type
* value 10 for XLSX file type
CLEAR: l_file_type.
l_file_type = if_salv_bs_xml=>c_type_xlsx.
* Get the flavour export
* Flavor for Complete ALV XML
CLEAR: l_flavour.
l_flavour = if_salv_bs_c_tt=>c_tt_xml_flavour_export.
* Transformation of data to XSTRING
CLEAR: l_contents_line.
CALL METHOD cl_salv_bs_tt_util=>if_salv_bs_tt_util~transform
EXPORTING
xml_type = l_file_type
xml_version = l_version
r_result_data = lo_result_data
xml_flavour = l_flavour
gui_type = if_salv_bs_xml=>c_gui_type_gui
IMPORTING
xml = l_contents_line.
* Converting the table contents from xstring to binary
CLEAR i_obj_bin.
CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
EXPORTING
buffer = l_contents_line
IMPORTING
output_length = l_count1
TABLES
binary_tab = i_obj_bin.
APPEND LINES OF i_obj_bin TO i_binary_content.
* Four character file extension '.XLSX' is set
l_filename = 'Materialsdeleted.XLSX'. " provide the extension in file name
* You can set a file name with a freely selectable file name extension
* separately from the document description. For this, use the header table
* of the document or the attachment with the key word '&SO_FILENAME='.
CONCATENATE '&SO_FILENAME='
l_filename
INTO
l_text_line.
APPEND l_text_line TO i_att_head.
*Calculate the xstring length
l_i = xstrlen( l_contents_line ).
* Create the attachment reference.
CALL METHOD document->add_attachment
EXPORTING
i_attachment_type = 'BIN' " type of data
i_attachment_subject = 'File with deleted materials'
i_attachment_size = l_i " XTRING length of l_contents_line
i_att_content_hex = i_binary_content
i_attachment_header = i_att_head. "file details
* add document to send request
CALL METHOD send_request->set_document( document ).
* --------- set sender -------------------------------------------
* note: this is necessary only if you want to set the sender
* different from actual user (SY-UNAME). Otherwise sender is
* set automatically with actual user.
"Provide the SAP logon user name to sent the sender (this example user sy-uname)
* You can use uer name as ('VAK10567', etc)
sender = cl_sapuser_bcs=>create( sy-uname ).
CALL METHOD send_request->set_sender
EXPORTING
i_sender = sender.
* --------- add recipient (e-mail address) -----------------------
* create recipient - please replace e-mail address !!!
* you can create multiple receipient by following this way or
* you can provide undisclosed recipients list
recipient = cl_cam_address_bcs=>create_internet_address(
'xxx@yyy.com' ).
* add recipient with its respective attributes to send request
CALL METHOD send_request->add_recipient
EXPORTING
i_recipient = recipient
i_express = 'X'.
* ---------- send document ---------------------------------------
CALL METHOD send_request->send(
EXPORTING
i_with_error_screen = 'X'
RECEIVING
result = l_sent_to_all ).
IF l_sent_to_all = 'X'.
WRITE 'Document Sent Successfully'(098).
ENDIF.
COMMIT WORK.
* -----------------------------------------------------------
* * Exception Handling
* -----------------------------------------------------------
* * Replace this very rudimentary exception handling
* * With your own one !!!
* -----------------------------------------------------------
CATCH cx_bcs INTO bcs_exception.
WRITE: 'Error occured'(001).
WRITE: 'Error type'(002), bcs_exception->error_type.
EXIT.
ENDTRY.
ENDFORM. "main