Skip to Content
0

Send Email Via ABAP report with Formatted Excel attachment

Aug 01, 2017 at 11:09 AM

484

avatar image

Hello

I need to send the send mail EXCEL format.

I have written logic for that and working perfect.

Bur excel sheet which is attached is not formatted.

Can any please give sample to piece of code where we can send / attach formated excel

sheet.

Please look the below code.

*&---------------------------------------------------------------------*
*& Report ZSPOOL
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT zspool.
*
TABLES : tbtco.

PARAMETERS : p_job TYPE btcjob OBLIGATORY.
*select-options : s_STDATE for TBTCO-STRTDATE default sy-DATUM to sy-DATUM.
SELECT-OPTIONS : s_stdate FOR tbtco-strtdate DEFAULT sy-datum TO sy-datum OBLIGATORY.


TYPES : BEGIN OF ty_tbtco,
jobname TYPE tbtco-jobname,
jobcount TYPE btcjobcnt,
strtdate TYPE btcxdate,
strttime TYPE btcxtime,
enddate TYPE tbtco-strtdate,
endtime TYPE tbtco-strttime,
END OF ty_tbtco.

DATA : it_tbtco TYPE TABLE OF ty_tbtco,
wa_tbtco TYPE ty_tbtco.
TYPES : BEGIN OF ty_tab,
field1 TYPE char255,
field2 TYPE char255,
field3 TYPE char255,
field4 TYPE char255,
field5 TYPE char255,
field6 TYPE char255,
field7 TYPE char255,
field8 TYPE char255,
field9 TYPE char255,
field10 TYPE char255,
field11 TYPE char255,
field12 TYPE char255,
END OF ty_tab.

DATA :wa_tbtcp TYPE tbtcp.
DATA : it_tab TYPE TABLE OF ty_tab,
wa_tab TYPE ty_tab,
wa_tabtmp TYPE ty_tab.
* Match not found, invalid

*CALL FUNCTION 'RSPO_DOWNLOAD_SPOOLJOB'
* EXPORTING
* ID = '2464'
** FNAME =
** ENCODING =
** FULLPATH =
** CHANGING
** DIRECTORY =
* EXCEPTIONS
* INVALID_DOCTYPE = 1
* DOWNLOAD_FAILED = 2
* CANNOT_RETURN = 3
* OTHERS = 4
* .
*IF SY-SUBRC <> 0.
** Implement suitable error handling here
*ENDIF.

DATA : it_buf TYPE tline OCCURS 0,
buffer_pdf LIKE tline OCCURS 0.

DATA : it_listobject TYPE abaplist OCCURS 0.



DATA :rqident TYPE tsp01-rqident,

it_buffm1 TYPE TABLE OF char255 WITH HEADER LINE,
it_buffm TYPE TABLE OF char255 WITH HEADER LINE,
result_tab TYPE abap_char1.


INITIALIZATION.
* s_STDATE-OPTION = 'EQ'.
* s_STDATE-SIGN = 'I'.
* s_STDATE-low = sy-DATUM.
* s_STDATE-high = sy-DATUM.

APPEND s_stdate.

START-OF-SELECTION.

IF p_job IS NOT INITIAL AND s_stdate IS NOT INITIAL.

SELECT jobname
jobcount
strtdate
strttime
enddate
endtime
FROM tbtco
INTO TABLE it_tbtco
WHERE jobname EQ p_job
AND strtdate IN s_stdate.
IF sy-subrc IS INITIAL.

SORT it_tbtco BY enddate endtime DESCENDING.
DELETE ADJACENT DUPLICATES FROM it_tbtco COMPARING jobname.


READ TABLE it_tbtco INTO wa_tbtco INDEX 1.
IF sy-subrc IS INITIAL.
SELECT SINGLE * FROM tbtcp
INTO wa_tbtcp
WHERE jobname = wa_tbtco-jobname
AND jobcount = wa_tbtco-jobcount.
IF sy-subrc IS INITIAL.
rqident = wa_tbtcp-listident.

ENDIF.

ENDIF.
ELSE.
MESSAGE 'No Job found for the selected criteria' TYPE 'E'.

ENDIF.
ELSE.

MESSAGE 'Please give input parametrs' TYPE 'E'.
ENDIF.


CALL FUNCTION 'RSPO_RETURN_ABAP_SPOOLJOB'
EXPORTING
rqident = rqident
first_line = 1
* LAST_LINE =
* PAGES =
TABLES
buffer = it_buffm
EXCEPTIONS
no_such_job = 1
not_abap_list = 2
job_contains_no_data = 3
selection_empty = 4
no_permission = 5
can_not_access = 6
read_error = 7
OTHERS = 8.
IF sy-subrc <> 0.
* Implement suitable error handling here
ENDIF.
**

DATA : lv_index TYPE sy-tabix.
FIND 'Detail' IN it_buffm.
DATA : it_xls_spool TYPE soli OCCURS 0.


DATA : list_string_ascii TYPE TABLE OF list_string_table,
* it_LIST_DYN_ASCII type table of LIST_DYN_ASCII,
it_listasci TYPE TABLE OF char255.
* it_LISTOBJECT type table of ABAPLIST.
CALL FUNCTION 'LIST_TO_ASCI'
* EXPORTING
* LIST_INDEX = -1
* WITH_LINE_BREAK = ' '
* IMPORTING
* LIST_STRING_ASCII = it_LIST_STRING_ASCII
* LIST_DYN_ASCII = it_LIST_DYN_ASCII
TABLES
listasci = it_buffm
listobject = it_listobject
EXCEPTIONS
empty_list = 1
list_index_invalid = 2
OTHERS = 3
.
IF sy-subrc <> 0.
* Implement suitable error handling here
ENDIF.

LOOP AT it_buffm .

SEARCH it_buffm FOR 'Detail' .
IF sy-subrc IS INITIAL.
lv_index = sy-tabix.
EXIT.
ENDIF.
* FIND ALL OCCURRENCES OF SUBSTRING CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TAB IN IT_BUFFM RESULTS result_tab.
"IGNORING CASE
* write :/ IT_BUFFM.

ENDLOOP.

DATA : lv_txt TYPE string.
LOOP AT it_buffm FROM lv_index .


FIND '|' IN it_buffm.
IF sy-subrc IS INITIAL AND it_buffm IS NOT INITIAL.
SPLIT it_buffm AT '|' INTO lv_txt
wa_tab-field1
wa_tab-field2
wa_tab-field3
wa_tab-field4
wa_tab-field5
wa_tab-field6
wa_tab-field7
wa_tab-field8
wa_tab-field9
wa_tab-field10
wa_tab-field11
wa_tab-field12.

IF wa_tab-field1 CS '|'OR wa_tab-field1 CS '-'." OR
ELSE.
READ TABLE it_tab INTO wa_tabtmp WITH KEY field1 = wa_tab-field1
field2 = wa_tab-field2
field3 = wa_tab-field3
field4 = wa_tab-field4
field5 = wa_tab-field5
field6 = wa_tab-field6
field7 = wa_tab-field7
field8 = wa_tab-field8
field9 = wa_tab-field9
field10 = wa_tab-field10
field11 = wa_tab-field11.

IF sy-subrc IS NOT INITIAL.
APPEND wa_tab TO it_tab.
ENDIF.






ENDIF.

* write :/ IT_BUFFM.
ENDIF.

ENDLOOP.

*
*LOOP AT it_tab into wa_tab.
*
* write /: wa_tab-field1, wa_tab-field2, wa_tab-field3, wa_tab-field4, wa_tab-field5,
* wa_tab-field6,
* wa_tab-field7,
* wa_tab-field8,
* wa_tab-field9,
* wa_tab-field10,
* wa_tab-field11.
*
** write : /.
*
* clear wa_tab.
*
*ENDLOOP.
*CALL FUNCTION 'RSPO_RETURN_ABAP_SPOOLJOB_RAW'
* EXPORTING
* RQIDENT = RQIDENT
* FIRST_LINE = 1
** LAST_LINE =
** PAGES =
** IMPORTING
** END_OF_DATA =
* TABLES
* BUFFER = IT_BUFFM1
* EXCEPTIONS
* NO_SUCH_JOB = 1
* NOT_ABAP_LIST = 2
* JOB_CONTAINS_NO_DATA = 3
* SELECTION_EMPTY = 4
* NO_PERMISSION = 5
* CAN_NOT_ACCESS = 6
* READ_ERROR = 7
* OTHERS = 8
* .
*IF SY-SUBRC <> 0.
** Implement suitable error handling here
*ENDIF.
*LOOP AT IT_BUFFM1.
* write :/ IT_BUFFM1.
*
*ENDLOOP.

DATA: i_content TYPE soli_tab, " Mail content
i_attach TYPE soli_tab, " Attachment
i_attach1 TYPE soli_tab. " Attachment

DATA: l_send_request TYPE REF TO cl_bcs,
" E-Mail Send Request
l_document TYPE REF TO cl_document_bcs,
" E-Mail Attachment
l_recipient TYPE REF TO if_recipient_bcs,
" Distribution List
l_sender TYPE REF TO if_sender_bcs,
" Address of Sender
l_uname TYPE salrtdrcpt,
" Sender Name(SY-UNAME)
l_bcs_exception TYPE REF TO cx_document_bcs,
" BCS Exception
l_addr_exception TYPE REF TO cx_address_bcs,
" Address Exception
l_send_exception TYPE REF TO cx_send_req_bcs.

*Constants------------------------------------------------------------*
CONSTANTS: c_tab(1) TYPE c VALUE
cl_abap_char_utilities=>horizontal_tab,
" Tab Character

c_cr(1) TYPE c VALUE cl_abap_char_utilities=>cr_lf,
" Line Feed for End-Of_line

c_ext TYPE soodk-objtp VALUE 'XLSX'. " XLS Extension

DATA: l_text TYPE char255. " Text
DATA: l_lines TYPE i,
l_size TYPE sood-objlen.
" Size of Attachment

MOVE 'ko8G DATA' TO l_text.
APPEND l_text TO i_content.

* PARAMETERS: p_mail TYPE ad_smtpadr NO-DISPLAY . ""

* p_mail = 'xyz@abc.com'.""

* Creates persistent send request
TRY.
l_send_request = cl_bcs=>create_persistent( ).

* Creating Document
l_document = cl_document_bcs=>create_document(
i_type = 'RAW'
i_text = i_content[]
i_subject = 'mps K88G' ).

* Preparing contents of attachment with Change Log
PERFORM prepare_attachment.

DESCRIBE TABLE it_tab LINES l_lines.
* Size to multiplied by 2 for UNICODE enabled systems
l_size = l_lines * 2 * 255.

* Adding Attachment
CALL METHOD l_document->add_attachment
EXPORTING
i_attachment_type = 'CSV'
i_attachment_size = l_size
i_attachment_subject = 'ko8G'
i_att_content_text = i_attach[].


* Add document to send request
* CALL METHOD l_send_request->set_document( l_document ).--
l_send_request->set_document( l_document ).
* Get Sender Object
l_uname = sy-uname.
DATA:lr_sender TYPE REF TO if_sender_bcs,
lr_send TYPE REF TO cl_bcs.
* Preparing the sender object
* lr_sender = cl_cam_address_bcs=>create_internet_address( p_mail ).

* l_sender = cl_sapuser_bcs=>create( l_uname ).
** Setting the sender
* CALL METHOD l_send_request->set_sender
* EXPORTING
* i_sender = lr_sender.

* E-Mail
* TRANSLATE p_mail TO LOWER CASE.

* L_RECIPIENT = cl_cam_address_bcs=>create_internet_address( p_mail ).""


* DATA: ob_distributionlist_bcs TYPE REF TO cl_distributionlist_bcs .

* tRY.
*
* l_recipient = cl_distributionlist_bcs=>getu_persistent(
*
* i_dliname = 'DIST'
*
** i_private = ' ' ). "abap_true ) .
* i_private = abap_true ). "abap_true ) .
*
* CATCH cx_address_bcs .
*
* MESSAGE s882(so) WITH 'DIST' DISPLAY LIKE 'E'. " Distribution list <&> does not exist
*
* RETURN .
*
* ENDTRY.
*

*
*
* CALL METHOD l_send_request->add_recipient
* EXPORTING
* i_recipient = l_recipient
** i_recipient = ob_distributionlist_bcs.
* i_express = 'U'.
** i_copy = ' '
** i_blind_copy = ' '
** i_no_forward = ' '.

l_send_request->add_recipient( l_recipient ).
*Trigger E-Mail immediately
* l_send_request->set_send_immediately( 'X' ). --

* CALL METHOD l_send_request->send( ).--

l_send_request->send( i_with_error_screen = 'X' ).

COMMIT WORK.

WRITE : 'Mail sent and spool' , wa_tbtcp-listident.
CATCH cx_document_bcs INTO l_bcs_exception.
CATCH cx_send_req_bcs INTO l_send_exception.
CATCH cx_address_bcs INTO l_addr_exception.

ENDTRY.

*&---------------------------------------------------------------------
*
*& Form PREPARE_ATTACHMENT
*&---------------------------------------------------------------------
*
FORM prepare_attachment.

FIELD-SYMBOLS: <lfs_table>, " Internal table structure
<lfs_con>. " Field Content
DATA: l_text TYPE char1024. " Text content for mail attachment
DATA: l_con(50) TYPE c. " Field Content in character format

* Columns to be tab delimeted
LOOP AT it_tab ASSIGNING <lfs_table>.
DO.
ASSIGN COMPONENT sy-index OF STRUCTURE <lfs_table>
TO <lfs_con>.
IF sy-subrc NE 0.
CONCATENATE c_cr l_text INTO l_text.
APPEND l_text TO i_attach.
EXIT.
ELSE.
CLEAR: l_con.
MOVE <lfs_con> TO l_con.
CONDENSE l_con.
IF sy-index = 1.
CLEAR: l_text.
MOVE l_con TO l_text.
ELSE.
CONCATENATE l_text l_con INTO l_text
SEPARATED BY c_tab.
ENDIF.
ENDIF.
ENDDO.
ENDLOOP.
ENDFORM. " PREPARE_ATTACHMENT

10 |10000 characters needed characters left characters exceeded

Please format code so it is readable.

1
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Raymond Giuseppi
Aug 01, 2017 at 11:40 AM
1

There are many discussions and document on how to generate an Excel sheet in Abap (true XLSX or XML) which one(s) did you already try?

Show 2 Share
10 |10000 characters needed characters left characters exceeded

I want send XLS type

0

As this one is Microsoft proprietary you MUST execute some Excel with OLE2 (So often using SAPGUI to execute an Excel session)

NB: If you use suffix 'XLS' with a different actual type of data (xml, csv) a popup will raise at opening, in some case it could prevent open.

0