Multiple sheets in excel can be achieved through OLE, but if the file has to be sent in background we cannot access the presentation server to pick the file. This blog shows how to send an email in background with multiple worksheet excel attachment (Data -> XML -> Binary -> Email).
REPORT zemail_bg NO STANDARD PAGE HEADING.
DATA: i_header TYPE STANDARD TABLE OF vbak,
i_detail TYPE STANDARD TABLE OF vbap,
i_xml TYPE STANDARD TABLE OF solisti1,
i_binary_content TYPE solix_tab,
wa_header TYPE vbak,
wa_detail TYPE vbap,
wa_xml TYPE solisti1,
gc_crlf TYPE c VALUE cl_bcs_convert=>gc_crlf,
v_string TYPE string,
v_size TYPE so_obj_len,
send_request TYPE REF TO cl_bcs,
document TYPE REF TO cl_document_bcs,
recipient TYPE REF TO if_recipient_bcs,
bcs_exception TYPE REF TO cx_bcs,
main_text TYPE bcsy_text,
sent_to_all TYPE os_boolean,
mailto TYPE ad_smtpadr.
CONSTANTS:
c_header1 TYPE char63 VALUE '<?xml version="1.0" encoding="utf-8"?>',
c_header01 TYPE char63 VALUE '<?mso-application progid="Excel.Sheet"?>',
c_header2 TYPE char63 VALUE '<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"',
c_header3 TYPE char63 VALUE 'xmlns:x="urn:schemas-microsoft-com:office:excel"',
c_header4 TYPE char63 VALUE 'xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"',
c_header5 TYPE char63 VALUE 'xmlns:html="http://www.w3.org/TR/REC-html40">',
c_stylesbe TYPE char63 VALUE '<Styles>',
c_stylesed TYPE char63 VALUE '</Styles>',
c_stylebe TYPE char63 VALUE '<Style ss:ID="s1">',
c_styleed TYPE char63 VALUE '</Style>',
c_font TYPE char63 VALUE '<Font x:Family="Swiss" ss:Bold="1" />',
c_wrkshtbe TYPE char20 VALUE '<Worksheet ss:Name="',
c_wrkshttg TYPE char05 VALUE '">',
c_wrkshted TYPE char20 VALUE '</Worksheet>',
c_tablebe TYPE char10 VALUE '<Table>',
c_tableed TYPE char10 VALUE '</Table>',
c_rowbe TYPE char10 VALUE '<Row>',
c_rowed TYPE char10 VALUE '</Row>',
c_cellbe TYPE char10 VALUE '<Cell>',
c_celled TYPE char10 VALUE '</Cell>',
c_databe TYPE char30 VALUE '<ss:Data ss:Type="String">',
c_dataed TYPE char30 VALUE '</ss:Data>',
c_wrkbked TYPE char30 VALUE '</Workbook>',
c_styleid TYPE char30 VALUE '<Style ss:ID="s65">',
c_hdcol TYPE char50 VALUE '<Interior ss:Color="#BFBFBF" ss:Pattern="Solid"/>',
c_hdr TYPE char50 VALUE '<Cell ss:StyleID="s65"><Data ss:Type="String">',
c_dtcell TYPE char30 VALUE '</Data></Cell>'.
*-- Fetch data
SELECT * FROM vbak INTO TABLE i_header UP TO 50 ROWS.
IF sy-subrc IS INITIAL.
SELECT * FROM vbap INTO TABLE i_detail FOR ALL ENTRIES IN i_header WHERE vbeln = i_header-vbeln.
ENDIF.
*-- Writing the XML Header tags into a global internal table
MOVE c_header1 TO wa_xml-line.
APPEND wa_xml TO i_xml.
MOVE c_header01 TO wa_xml-line.
APPEND wa_xml TO i_xml.
MOVE c_header2 TO wa_xml-line.
APPEND wa_xml TO i_xml.
MOVE c_header3 TO wa_xml-line.
APPEND wa_xml TO i_xml.
MOVE c_header4 TO wa_xml-line.
APPEND wa_xml TO i_xml.
MOVE c_header5 TO wa_xml-line.
APPEND wa_xml TO i_xml.
*-- Creating a global style in which we mention about font
* that will be followed across the workbook.
MOVE c_stylesbe TO wa_xml-line.
APPEND wa_xml TO i_xml.
MOVE c_stylebe TO wa_xml-line.
APPEND wa_xml TO i_xml.
MOVE c_font TO wa_xml-line.
APPEND wa_xml TO i_xml.
MOVE c_styleed TO wa_xml-line.
APPEND wa_xml TO i_xml.
wa_xml-line = c_styleid.
APPEND wa_xml TO i_xml.
wa_xml-line = c_hdcol.
APPEND wa_xml TO i_xml.
wa_xml-line = c_styleed.
APPEND wa_xml TO i_xml.
MOVE c_stylesed TO wa_xml-line.
APPEND wa_xml TO i_xml.
*-- Preparing 'Header' sheet
CONCATENATE c_wrkshtbe 'Header' c_wrkshttg
INTO wa_xml-line
SEPARATED BY ' '.
APPEND wa_xml TO i_xml.
MOVE c_tablebe TO wa_xml-line.
APPEND wa_xml TO i_xml.
wa_xml-line = '<Column ss:Width="62"/>'.
APPEND wa_xml TO i_xml.
wa_xml-line = '<Column ss:Width="81"/>'.
APPEND wa_xml TO i_xml.
LOOP AT i_header INTO wa_header.
IF sy-tabix = 1.
MOVE c_rowbe TO wa_xml-line.
APPEND wa_xml TO i_xml.
CONCATENATE c_hdr 'Sales Document' c_dtcell INTO wa_xml-line.
APPEND wa_xml TO i_xml.
CONCATENATE c_hdr 'Create Date' c_dtcell INTO wa_xml-line.
APPEND wa_xml TO i_xml.
MOVE c_rowed TO wa_xml-line.
APPEND wa_xml TO i_xml.
ENDIF.
*-- Writing the XML code to start a row in the table inserted above
* to the particular sheet.
MOVE c_rowbe TO wa_xml-line.
APPEND wa_xml TO i_xml.
*-- Writing the XML code for transferring the data cell by cell
* in the row created above .
CONCATENATE c_cellbe c_databe wa_header-vbeln c_dataed c_celled
INTO wa_xml-line
SEPARATED BY ' '.
APPEND wa_xml TO i_xml.
CONCATENATE c_cellbe c_databe wa_header-kunnr c_dataed c_celled
INTO wa_xml-line
SEPARATED BY ' '.
APPEND wa_xml TO i_xml.
*-- Writing the XML code for ending a row after
* writing data for all the columns.
MOVE c_rowed TO wa_xml-line.
APPEND wa_xml TO i_xml.
ENDLOOP.
*-- Close the table and the sheet.
MOVE c_tableed TO wa_xml-line.
APPEND wa_xml TO i_xml.
MOVE c_wrkshted TO wa_xml-line.
APPEND wa_xml TO i_xml.
*-- Preparing 'Detail data' work sheet
CONCATENATE c_wrkshtbe 'Detail' c_wrkshttg
INTO wa_xml-line
SEPARATED BY ' '.
APPEND wa_xml TO i_xml.
*-- Writing the XML code for inserting a table.
MOVE c_tablebe TO wa_xml-line.
APPEND wa_xml TO i_xml.
wa_xml-line = '<Column ss:Width="62"/>'.
APPEND wa_xml TO i_xml.
wa_xml-line = '<Column ss:Width="81"/>'.
APPEND wa_xml TO i_xml.
*-- Populate 'Detail' sheet data
LOOP AT i_detail INTO wa_detail.
IF sy-tabix = 1.
MOVE c_rowbe TO wa_xml-line.
APPEND wa_xml TO i_xml.
CONCATENATE c_hdr 'Sales Document' c_dtcell INTO wa_xml-line.
APPEND wa_xml TO i_xml.
CONCATENATE c_hdr 'Item Number' c_dtcell INTO wa_xml-line.
APPEND wa_xml TO i_xml.
MOVE c_rowed TO wa_xml-line.
APPEND wa_xml TO i_xml.
ENDIF.
*-- Writing the XML code to start a row in the table inserted above
* to the particular sheet
MOVE c_rowbe TO wa_xml-line.
APPEND wa_xml TO i_xml.
*-- Writing the XML code for transferring the data cell by cell
* in the row created above
CONCATENATE c_cellbe c_databe wa_detail-vbeln c_dataed c_celled
INTO wa_xml-line
SEPARATED BY ' '.
APPEND wa_xml TO i_xml.
CONCATENATE c_cellbe c_databe wa_detail-posnr c_dataed c_celled
INTO wa_xml-line
SEPARATED BY ' '.
APPEND wa_xml TO i_xml.
*-- Writing the XML code for ending a row after
* writing data for all the columns.
MOVE c_rowed TO wa_xml-line.
APPEND wa_xml TO i_xml.
ENDLOOP.
*-- After transferring all the data to the rows of the table
* inserted in the sheet we need to close the table and the sheet.
MOVE c_tableed TO wa_xml-line.
APPEND wa_xml TO i_xml.
MOVE c_wrkshted TO wa_xml-line.
APPEND wa_xml TO i_xml.
*-- After transferring all the data in to the XML form
* we need to end the XML workbook created.
MOVE c_wrkbked TO wa_xml-line.
APPEND wa_xml TO i_xml.
*-- Convert the XML data into Binary data
LOOP AT i_xml INTO wa_xml.
CONCATENATE v_string wa_xml gc_crlf INTO v_string.
ENDLOOP.
TRY.
cl_bcs_convert=>string_to_solix(
EXPORTING
iv_string = v_string
iv_codepage = '4103' "suitable for MS Excel, leave empty
iv_add_bom = 'X' "for other doc types
IMPORTING
et_solix = i_binary_content
ev_size = v_size ).
CATCH cx_bcs.
MESSAGE e445(so).
ENDTRY.
*-- Send email
TRY.
*-- create persistent send request
send_request = cl_bcs=>create_persistent( ).
*-- create and set document with attachment
* create document object from internal table with text
APPEND 'PFA, The report output' TO main_text.
document = cl_document_bcs=>create_document(
i_type = 'RAW'
i_text = main_text
i_subject = 'Test' ).
*-- Add the spread sheet as attachment to document object
document->add_attachment(
i_attachment_type = 'xls'
i_attachment_subject = 'Output'
i_attachment_size = v_size
i_att_content_hex = i_binary_content ).
*-- Add document object to send request
send_request->set_document( document ).
*-- Add recipient (e-mail address)
* create recipient object
mailto = 'ENTER YOUR MAILID HERE'.
recipient = cl_cam_address_bcs=>create_internet_address( mailto ).
*-- Add recipient object to send request
send_request->add_recipient( recipient ).
*-- Set send immediately flag
send_request->set_send_immediately( 'X' ).
*-- send document
sent_to_all = send_request->send( i_with_error_screen = 'X' ).
COMMIT WORK.
IF sent_to_all IS INITIAL.
MESSAGE i500(sbcoms) WITH mailto.
ELSE.
MESSAGE s022(so).
ENDIF.
*-- exception handling
CATCH cx_bcs INTO bcs_exception.
MESSAGE i865(so) WITH bcs_exception->error_type.
ENDTRY.
Run the program in background..
