Additional Blogs by Members
cancel
Showing results for 
Search instead for 
Did you mean: 
Former Member

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

4 Comments