Sending Mail As Excel Attachment

[Former Member]

Dear ALL,

I have a requirement to send the output of ALV as excel attachment when the user clicks the button. I have written the complete code and the mail is also successfully send. But the issue is that all the ALV output is coming in 1 Column in excel attachement.

I have already searched in SDN and tried all the related posts. But could not succeed.

Here is my sample code which I have used for separating the contents:-

Here W_BELNR etc contains the Heading Used for Column.

CONCATENATE W_BELNR W_BUDAT W_BLDAT W_VBELN W_BLART W_BI_DESC W_UMSKZ W_WEVWV

W_DRAMT W_CRAMT W_CUMAMT W_ZUONR W_WRBTR W_WAERS W_AUGBL W_FLAG W_PRCTR

W_XBLNR W_SGTXT W_ZTERM W_ZTERMT W_VKAUS W_VKAUST W_BAANR

INTO W_LONGTEXT SEPARATED BY CON_TAB.

IT_OBJBIN = W_LONGTEXT.

APPEND IT_OBJBIN. CLEAR IT_OBJBIN.

LOOP AT ITAB_BSAD_TRN_NEW4 INTO W_ITAB_BSAD_TRN_NEW4.

DRAMT_NEW = W_ITAB_BSAD_TRN_NEW4-DRAMT.

CRAMT_NEW = W_ITAB_BSAD_TRN_NEW4-CRAMT.

CUMAMT_NEW = W_ITAB_BSAD_TRN_NEW4-CUMAMT.

WRBTR_NEW = W_ITAB_BSAD_TRN_NEW4-WRBTR.

CONCATENATE W_ITAB_BSAD_TRN_NEW4-BELNR W_ITAB_BSAD_TRN_NEW4-BUDAT

W_ITAB_BSAD_TRN_NEW4-BLDAT W_ITAB_BSAD_TRN_NEW4-VBELN

W_ITAB_BSAD_TRN_NEW4-BLART W_ITAB_BSAD_TRN_NEW4-BI_DESC

W_ITAB_BSAD_TRN_NEW4-UMSKZ W_ITAB_BSAD_TRN_NEW4-WEVWV

DRAMT_NEW

CRAMT_NEW

CUMAMT_NEW

W_ITAB_BSAD_TRN_NEW4-ZUONR

WRBTR_NEW

W_ITAB_BSAD_TRN_NEW4-WAERS

W_ITAB_BSAD_TRN_NEW4-AUGBL W_ITAB_BSAD_TRN_NEW4-FLAG

W_ITAB_BSAD_TRN_NEW4-PRCTR W_ITAB_BSAD_TRN_NEW4-XBLNR

W_ITAB_BSAD_TRN_NEW4-SGTXT W_ITAB_BSAD_TRN_NEW4-ZTERM

W_ITAB_BSAD_TRN_NEW4-ZTERMT W_ITAB_BSAD_TRN_NEW4-VKAUS

W_ITAB_BSAD_TRN_NEW4-VKAUST W_ITAB_BSAD_TRN_NEW4-BAANR

INTO I_DLOAD-DLOAD

SEPARATED BY CON_TAB.

IT_OBJBIN = I_DLOAD-DLOAD.

APPEND IT_OBJBIN. CLEAR IT_OBJBIN.

ENDLOOP.

CALL FUNCTION 'SO_RAW_TO_RTF'

TABLES

OBJCONT_OLD = IT_OBJBIN

OBJCONT_NEW = IT_OBJBIN.

Kindly guide me in this matter.

Thanks & Regards,

Bharti Jain

Edited by: Bharti Jain on Aug 25, 2011 7:23 AM

[Former Member]

Bharti,

Your code is not very clear to me, but You must be having some date in your internal table which you want to display in excel.

Just separate the fields in this internal table by cl_abap_char_utilities=>horizontal_tab and pass it to a string table.

Then this string table will be passed to BODY.

IT_OBJBIN = I_DLOAD-DLOAD.

instead use,

IT_OBJBIN-LINE = I_DLOAD-DLOAD.

-

---

What is con_tab exact value ?

SEPARATED BY CON_TAB replace by SEPARATED BY cl_abap_char_utilities=>horizontal_tab.

-

---

Let me know if you are not able to correct it.

BR,

Diwakar

Edited by: Diwakar Aggarwal on Aug 25, 2011 1:36 PM

[Former Member]

Thanks For answering me ,

I have tried but it is appearing the same way.

Yes , In my internal table there is Date Field.

Some code Snippets.

**********I_DLOAD********

DATA :BEGIN OF I_DLOAD OCCURS 0 ,

DLOAD(5000) ,

END OF I_DLOAD .

**********Con_tab*************

CONSTANTS:CON_TAB TYPE C VALUE CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TAB,

CON_CRET TYPE C VALUE CL_ABAP_CHAR_UTILITIES=>CR_LF.

Now I have changed

-

---

SEPARATED BY CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TAB.

IT_OBJBIN-LINE = I_DLOAD-DLOAD.

APPEND IT_OBJBIN.

Please guide where I am not correct.

Thanks In advance,

Regards,

Bharti Jain

[former_member585060]

Hi,

Check the below code sample code,

DATA :  v_xls_iattach(2000) OCCURS 0 WITH HEADER LINE.   

CONSTANTS:
    c_xls_con_tab  TYPE c VALUE cl_abap_char_utilities=>horizontal_tab,
    c_xls_con_cret TYPE c VALUE cl_abap_char_utilities=>cr_lf.


PERFORM build_xls_data  USING i_heading[].   

PERFORM build_xls_data  USING i_output[].   " Pass here the internal table whose data need to be in excel


FORM build_xls_data  USING    p_fs     TYPE ANY TABLE.

  DATA: lv_text(50) TYPE c.

  DATA: lv_line     TYPE REF TO data.
  FIELD-SYMBOLS: <ft> TYPE ANY TABLE,
                 <fs> TYPE ANY,
                 <fl> TYPE ANY.

  ASSIGN p_fs TO <ft>.
  CREATE DATA lv_line LIKE LINE OF p_fs.
  ASSIGN lv_line->* TO <fl>.

  IF NOT p_fs[] IS INITIAL .
    LOOP AT <ft> INTO <fl>.
      CLEAR lv_text.
      DO.
        ASSIGN COMPONENT sy-index OF STRUCTURE <fl> TO <fs>.
        IF sy-subrc <> 0.
          EXIT.
        ENDIF.
        WRITE <fs> TO lv_text.

        CONCATENATE v_xls_iattach lv_text c_xls_con_tab
        INTO v_xls_iattach.

      ENDDO.
      CONCATENATE v_xls_iattach c_xls_con_cret INTO v_xls_iattach.
      APPEND  v_xls_iattach.

      CLEAR v_xls_iattach.
    ENDLOOP.
  ENDIF.
ENDFORM.

v_xls_iattach will be the excel attachment.

For column headings prepare an internal table with one field and append all the column headings into that, again use the same

perform BUILD_XLS_DATA first, so that columns are populated first.

With the above code there is no need of the FM 'SO_RAWTO_RTF'.

How you are sending email, are you using FM 'SO_DOCUMENT_SEND_API1'?

Regards

Bala Krishna

Edited by: Bala Krishna on Aug 25, 2011 12:13 PM

[Former Member]

Thanks All,

Still it is coming in 1 columnn only.

Please guide me.

Regards,

Bharti Jain

[former_member585060]

Hi,

Have you tried with my code?

Keep a breakpoint in the program after the execl internal table is filled, check the internal table content by clicking on the tables tab, download the excel internal table content into excel sheet from the debugger and see how the data in the excel sheet.

Regards

Bala Krishna

[Former Member]

Thanks For Replying,

I have already seen in the debugger and I am using your code which you have suggested.

There it is coming in this pattern in V_XLS_IATTACH.

###2007#OP BAL#002# . .# . .#01.11.2007####08#15##H## eg for 1st line

After that I am using IT_OBJBIN [] = V_XLS_IATTACH [].

The function module used is 'SO_NEW_DOCUMENT_ATT_SEND_API1'

Please guide.

Thanks In Advance,

Regards,

Bharti Jain

Edited by: Bharti Jain on Aug 25, 2011 11:16 AM

[former_member585060]

Hi,

In the debugger after clicking on the table tab, there will a Button with Excel symbol with which we can save the inetrnal table content into excel, download from there and check the alignment.

For the FM 'SO_DOCUMENT_SEND_API1', for the tables parameter for packing_list, fill as below and pass it to the FM.

Data : v_xls_ipacking_list LIKE sopcklsti1 OCCURS 0 WITH HEADER LINE.

* Describe the body of the message
  CLEAR v_xls_ipacking_list.  REFRESH v_xls_ipacking_list.
  v_xls_ipacking_list-transf_bin = space.
  v_xls_ipacking_list-head_start = 1.
  v_xls_ipacking_list-head_num = 0.
  v_xls_ipacking_list-body_start = 1.
  v_xls_ipacking_list-doc_type = 'RAW'.
  APPEND v_xls_ipacking_list.

* Create attachment notification
  v_xls_ipacking_list-transf_bin = 'X'.
  v_xls_ipacking_list-head_start = 1.
  v_xls_ipacking_list-head_num   = 1.
  v_xls_ipacking_list-body_start = 1.

  DESCRIBE TABLE v_xls_iattachment LINES v_xls_ipacking_list-body_num.
  v_xls_ipacking_list-doc_type   =  p_xls_format.
  v_xls_ipacking_list-obj_descr  =  p_xls_filename.
  v_xls_ipacking_list-doc_size   =
                           v_xls_ipacking_list-body_num * p_xls_size.
  APPEND v_xls_ipacking_list.

Regards

Bala Krishna

[Former Member]

Bharti,

This piece of code should help you, just understand that we are filling a string with table data having tab spaces and then using that string form to display data.

Gt_error is any internal table with data having multiple fields.

 data:           ls_error_str TYPE string.

  FIELD-SYMBOLS:  <lfs_body> TYPE solisti1,
                                <lfs_field> TYPE ANY.

  LOOP AT gt_error ASSIGNING <gfs_error>.
    DO.
      ASSIGN COMPONENT sy-index OF STRUCTURE <gfs_error> TO <lfs_field>.
      IF sy-subrc <> 0.
        EXIT.
      ENDIF.

      IF <lfs_field> IS ASSIGNED.

        IF sy-index = 1.
          ls_error_str = <lfs_field>.
          CONTINUE.
        ENDIF.

        CONCATENATE ls_error_str <lfs_field> INTO ls_error_str SEPARATED BY cl_abap_char_utilities=>horizontal_tab..
      ENDIF.
    ENDDO.

    APPEND INITIAL LINE TO lt_body ASSIGNING <lfs_body>.
    IF <lfs_body> IS ASSIGNED.
      <lfs_body>-line = ls_error_str.
    ENDIF.

    CLEAR: ls_error_str, lv_len.
  ENDLOOP.

  CALL FUNCTION 'SO_RAW_TO_RTF'
    TABLES
      objcont_old = lt_body
      objcont_new = lt_body.


  DESCRIBE TABLE lt_body LINES lv_count.

  APPEND INITIAL LINE TO lt_packlist ASSIGNING <lfs_packlist>.
  IF <lfs_packlist> IS ASSIGNED.
    <lfs_packlist>-doc_type   = 'XLS'.
    <lfs_packlist>-body_start = 1.
    <lfs_packlist>-body_num   = lv_count.
    <lfs_packlist>-transf_bin = 'X'.
    <lfs_packlist>-head_start = 1.
    <lfs_packlist>-doc_size   = lv_count * 255.
    <lfs_packlist>-obj_descr = 'Error.xls''.
  ENDIF.

  CALL FUNCTION 'SO_NEW_DOCUMENT_ATT_SEND_API1'
................
   TABLES
      packing_list                     = lt_packlist
      contents_bin                   = lt_body
..............................

BR,

Diwakar

[Former Member]

Hi,

Use this code ..this will solve ur problem,

CONSTANTS:

con_tab TYPE c VALUE cl_abap_char_utilities=>horizontal_tab,

con_cret TYPE c VALUE cl_abap_char_utilities=>cr_lf.

DATA: it_attach TYPE STANDARD TABLE OF solisti1 INITIAL SIZE 0

WITH HEADER LINE.

DATA : l_string(700) TYPE c.

CLEAR l_string.

CONCATENATE ............................(your data fields)

INTO l_string SEPARATED BY con_tab.

WHILE l_string <> space.

CALL FUNCTION 'TEXT_SPLIT'

EXPORTING

length = 255

text = l_string

IMPORTING

line = it_attach

rest = l_string.

IF l_string = space.

CONCATENATE it_attach con_cret INTO it_attach.

APPEND it_attach.

CLEAR it_attach.

ELSE.

APPEND it_attach.

CLEAR it_attach.

ENDIF.

ENDWHILE.

and finally pass this it_attach to FM 'SO_DOCUMENT_SEND_API1'.

Regards,

Venu

[Former Member]

Thanks All for Replying.

I have tried all the code but still no success. I might have committed some mistake.

When I am trying to add the heading for the columns , in the internal table I can see like this.

DOC NO#POST DT# and so on.

All this is coming in one column.

Also as told by Bala Krishna Mam, I have downloaded the excel file and in that the fields are coming in individual columns as I require.

Please guide.

Regards,

Bharti Jain

Edited by: Bharti Jain on Aug 25, 2011 12:23 PM

[syed_ibrahim5]

hi,

please check if the data is coming in a single cell or a single line.

If the Data is coming in the single line , the you need to add this line.

CONCATENATE W_BELNR W_BUDAT W_BLDAT W_VBELN W_BLART W_BI_DESC W_UMSKZ W_WEVWV
W_DRAMT W_CRAMT W_CUMAMT W_ZUONR W_WRBTR W_WAERS W_AUGBL W_FLAG W_PRCTR
W_XBLNR W_SGTXT W_ZTERM W_ZTERMT W_VKAUS W_VKAUST W_BAANR
INTO W_LONGTEXT SEPARATED BY CON_TAB.
IT_ATTACH = W_LONGTEXT.
CONCATENATE IT_ATTACH CL_ABAP_CHAR_UTILITIES=>CR_LF INTO IT_OBJBIN:"add this .
APPEND IT_OBJBIN. CLEAR IT_OBJBIN.

With Regards,

Syed Ibrahim .G

[Former Member]

Hi,

I have a project on the Code Exchange: ABAP e-mail made easy... you can check it. Basically it consists of a class which uses the BCF framework to perform various mail-related actions, such as sending an excel attachment or PDF file.

https://cw.sdn.sap.com/cw/groups/abap-e-mail-made-e

As long as the ALV data is available as an internal table in your program you should be able to use this class.

Regards,

Trond

[former_member585060]

Hi,

"DOC NO#POST DT# " the # symbol represents next cell in excel sheet.

Paste the code related to function module 'SO_DOCUMENT_SEND_API1' and the code related to polulating of different parameters of the above FM.

Regards

Bala Krishna

[Former Member]

This is the code which I have written.

CONCATENATE 'Display Message' ' ' INTO IT_OBJTXT

SEPARATED BY SPACE.

APPEND IT_OBJTXT.

CLEAR IT_OBJTXT.

IT_RECLIST-RECEIVER = WS_EMAIL.

IT_RECLIST-REC_TYPE = 'U'.

IT_RECLIST-EXPRESS = ' '.

IT_RECLIST-COM_TYPE = 'INT'.

APPEND IT_RECLIST.

CLEAR: W_TEXT.

DOC_CHNG-OBJ_DESCR = 'Customer Balance'.

DOC_CHNG-OBJ_NAME = 'INBOUND'.

DESCRIBE TABLE IT_OBJTXT LINES TAB_LINES.

READ TABLE IT_OBJTXT INDEX TAB_LINES.

DOC_CHNG-DOC_SIZE = ( TAB_LINES - 1 ) * 255 + STRLEN( IT_OBJTXT ).

CLEAR: W_LONGTEXT.

W_LONGTEXT = 'GRASIM INDUSTRIES LIMITED.'.

IT_OBJBIN = W_LONGTEXT.

APPEND IT_OBJBIN. CLEAR IT_OBJBIN.

*****Here looping at internal table

Loop at itab into wtab

CONCATENATE WTAB-BELNR WTAB-BUDAT etc.......

INTO I_DLOAD-DLOAD

SEPARATED BY CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TAB.

CONCATENATE CON_CRET I_DLOAD-DLOAD INTO I_DLOAD-DLOAD.

IT_OBJBIN-LINE = I_DLOAD-DLOAD.

APPEND IT_OBJBIN. CLEAR IT_OBJBIN.

ENDLOOP.

*****************

DESCRIBE TABLE IT_OBJTXT LINES TAB_LINES.

CLEAR IT_OBJPACK-TRANSF_BIN.

IT_OBJPACK-HEAD_START = 1.

IT_OBJPACK-HEAD_NUM = 0.

IT_OBJPACK-BODY_START = 1.

IT_OBJPACK-BODY_NUM = TAB_LINES.

IT_OBJPACK-DOC_TYPE = 'RAW'.

APPEND IT_OBJPACK.

DESCRIBE TABLE IT_OBJBIN LINES TAB_LINES.

IT_OBJPACK-HEAD_START = 1.

IT_OBJPACK-HEAD_NUM = 0.

IT_OBJPACK-BODY_START = 1.

IT_OBJPACK-BODY_NUM = TAB_LINES.

IT_OBJPACK-TRANSF_BIN = 'X'."'C'.

IT_OBJPACK-DOC_TYPE = 'CSV'.

IT_OBJPACK-OBJ_DESCR = W_FILENAME.

IT_OBJPACK-OBJ_NAME = 'BILLING'.

• IT_OBJPACK-DOC_SIZE = TAB_LINES * 255.

IT_OBJPACK-DOC_SIZE = 100000.

APPEND IT_OBJPACK.

CALL FUNCTION 'SO_NEW_DOCUMENT_ATT_SEND_API1'

EXPORTING

DOCUMENT_DATA = DOC_CHNG

• PUT_IN_OUTBOX = c_x

COMMIT_WORK = 'X'

TABLES

PACKING_LIST = IT_OBJPACK

• object_header = it_objhead

CONTENTS_BIN = IT_OBJBIN

CONTENTS_TXT = IT_OBJTXT

RECEIVERS = IT_RECLIST

EXCEPTIONS

TOO_MANY_RECEIVERS = 1

DOCUMENT_NOT_SENT = 2

OPERATION_NO_AUTHORIZATION = 4

OTHERS = 99.

IF SY-SUBRC <> 0.

• WRITE: / 'failure in sending mail'.

MESSAGE 'Mail Could Not Be Sent To This Customer.' TYPE 'S'.

ELSE.

MESSAGE 'Mail Successfully Sent To This Customer.' TYPE 'S'.

ENDIF.

Regards,

Bharti Jain

[former_member585060]

Hi Bharti,

In your code replace IT_OBJPACK-DOC_TYPE from 'CSV' to 'XLS'.

DESCRIBE TABLE IT_OBJBIN LINES TAB_LINES.
IT_OBJPACK-HEAD_START = 1.
IT_OBJPACK-HEAD_NUM = 0.
IT_OBJPACK-BODY_START = 1.
IT_OBJPACK-BODY_NUM = TAB_LINES.
IT_OBJPACK-TRANSF_BIN = 'X'."'C'.
IT_OBJPACK-DOC_TYPE = 'CSV'.    " Change this to XLS
IT_OBJPACK-OBJ_DESCR = W_FILENAME.
IT_OBJPACK-OBJ_NAME = 'BILLING'.
* IT_OBJPACK-DOC_SIZE = TAB_LINES * 255.
IT_OBJPACK-DOC_SIZE = 100000.
APPEND IT_OBJPACK.

Regards

Bala Krishna

[Clemenss]

Hi Barthi,

1. if you post any code lines, use the above &lt;&GT; code button to preserve formatting

2. Forget that lousy SO_SEND functions

3. use CL_BCS as shown by vishal kapoor in the thread [Problem in Sending mail with attachment (Excel sheet) |;

4. Search before post as this has been asked thousands of time

Regards

Clemens