Abap send email with excel attachment

i just finished on of my SAP report requested by my user. The report need to run automatically once a month and send an email with EXCEL attachment to keep the result.

image credit to : wiki.sdn.sap.com

if you are using SAP ECC6, search for this example BCS_EXAMPLE_7 : Send Emeail with Excel attachment. from this example its will send email to the email receiver with excel attachment.  however the problems is, you cannot modify excel properties (such as column width, border, column color etc).

Don’t worry there always the solution, i just google around i found this solution: using excel XML to control the excel properties. What i have done was, using current code in BCS_EXAMPLE_7 and and bind the internal table contain into the excel’s XML format!!

from original BCS_EXAMPLE_7 search for form send, you will find this code :

Form create_content.

concatenate lv_string
ls_t100-arbgb gc_tab
ls_t100-msgnr gc_tab
ls_t100-sprsl gc_tab
ls_t100-text gc_crlf
into lv_string.

try.

cl_bcs_convert=>string_to_solix(
exporting
iv_string = lv_string
iv_codepage = ’4103′ ”suitable for MS Excel, leave empty
iv_add_bom = ’X' ”for other doc types
importing
et_solix = binary_content
ev_size = size ).
catch cx_bcs.
message e445(so).
endtry.

and append the internal table into lv_string using the Excel’s XML  hierarchy.

your new LV_STRING could be coded as:

concatenate lv_string
‘<?xml version=”1.0″?>’
‘<?mso-application progid=”Excel.Sheet”?>’
<Workbook …. ‘  “XML header, please refer or link example as attached
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: ” this line should be write with XML full header format

‘<Style ss:ID=”s3″>’ ” Make your Style ID

‘<Borders>’
‘<Border ss:Position=”Bottom” ss:LineStyle=”Continuous” ss:Weight=”1″/>’
‘<Border ss:Position=”Left” ss:LineStyle=”Continuous” ss:Weight=”1″/>’
‘<Border ss:Position=”Right” ss:LineStyle=”Continuous” ss:Weight=”1″/>’
‘<Border ss:Position=”Top” ss:LineStyle=”Continuous” ss:Weight=”1″/>’
</Borders>’
‘ </Style>

‘<Worksheet ss:Name=”Sheet1″>’
‘<Table ss:ExpandedColumnCount=”4″ ss:ExpandedRowCount=”9″ x:FullColumns=”1″
x:FullRows=”1″>’
‘<Column ss:AutoFitWidth=”0″ ss:Width=”135″/>’
‘<Column ss:Index=”3″ ss:StyleID=”" ss:AutoFitWidth=”0″ ss:Width=”66.75″/> ‘
‘<Row>’
‘<Cell ss:StyleID=”s3″><Data ss:Type=”String”>Row 4</Data></Cell>’
‘<Cell ss:StyleID=”s3″><Data ss:Type=”Number”>4000</Data></Cell>’
‘<Cell ss:StyleID=”s6″/>’
‘<Cell ss:StyleID=”s3″/>’
‘</Row>’
‘</Table>  <DataValidation xmlns=”urn:schemas-microsoft-com:office:excel”>’
‘<Range>R2C2:R1000C2</Range>’
‘<Type>List</Type>’
‘<CellRangeList/>’
‘<Value>”Red, Black, Green”</Value>’
‘</DataValidation>’
‘</Worksheet></Workbook> ‘

you also can refer to this example for your reference.