CSV file as excel attachment in mail

[Former Member]

Dear Experts,

I have a requirement where i have to send ALV output log as an Excel attachment in mail to outside SAP inbox. For this i have converted my log internal table into CSV format through function module SAP_CONVERT_TO_CSV_FORMAT using a separator ';'.

Now i want to send  this CSV internal table as Excel file attachment in mail.

Please guide me how to do this or if you have any sample code for help.

Thanks.

[rosenberg_eitan]

Hi,

Havea look here:

http://scn.sap.com/message/14309154#14309154

Also program BCS_EXAMPLE_1

Regards.

[Former Member]

Please find below code for mail sending as attachment

FORM f_send_email USING p_lint_error TYPE ty_t_error

                         p_lw_t

                         p_lw_s

                         p_lw_v

                         p_lw_h.

*&---------------------------------------------------------------------*

* This perform will send the CSV files as an attachment through mail

* to the given distribution list

*&---------------------------------------------------------------------*

*&---------------------------------------------------------------------*

*&DATA DECLARATION

*&---------------------------------------------------------------------*

* internal tables declared for sending attachments to the mail

   DATA:

     lint_objpack       TYPE STANDARD TABLE OF sopcklsti1,

     lint_objtxt        TYPE STANDARD TABLE OF solisti1,

     lint_objbin        TYPE STANDARD TABLE OF solisti1,

     lint_reclist       TYPE STANDARD TABLE OF somlreci1,

     lint_objhead       TYPE STANDARD TABLE OF solisti1.

   DATA:

*    lws_docdata       TYPE sodocchgi1,

     lws_doc_chng      TYPE sodocchgi1,

*    lw_receiverlist   TYPE ad_smtpadr,

     lws_objpack       TYPE sopcklsti1,

     lws_objtxt        TYPE solisti1,

     lws_objbin        TYPE solisti1,

     lws_reclist       TYPE somlreci1,

     lws_objhead       TYPE solisti1.

   DATA:

     lw_tab_lines      TYPE sy-tabix,

     lw_tab_lines1     TYPE sy-tabix,

     lw_recname        TYPE so_recname. " VALUE 'abc@gmail.com'. " Receiver's Mail

*    lw_n              TYPE i.

   CONSTANTS :

*   lc_ret      TYPE char2      VALUE cl_abap_char_utilities=>cr_lf,

    lc_rectype  TYPE so_escape  VALUE 'U',

*   lc_sap_user TYPE so_escape  VALUE 'B',

    lc_offer    TYPE char12 VALUE 'OFFER',

    lc_raw      TYPE so_obj_tp VALUE 'RAW', " Doc Type RAW

    lc_xls      TYPE so_obj_tp VALUE 'XLS', " Excel Doc Type

    lc_obj_xls  TYPE sopcklsti1-obj_name VALUE 'XLS'.

*   lc_comtype  TYPE so_snd_art VALUE 'INT'.

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

   CLEAR:  lws_objbin,

           lw_tab_lines,

           lws_objhead,

           lws_objpack,

           lws_reclist.

   CLEAR: lint_objbin[],

          lint_objhead[],

          lint_objpack[],

          lint_reclist[].

* Excel Data

   CONCATENATE text-t11 text-t12 INTO lws_objbin-line" Message Type & Description

   SEPARATED BY cl_abap_char_utilities=>horizontal_tab.

   APPEND lws_objbin TO lint_objbin.

   CLEAR lws_objbin.

   CONCATENATE lws_objbin cl_abap_char_utilities=>newline INTO lws_objbin.

   APPEND lws_objbin TO lint_objbin.

   CLEAR lws_objbin.

*  LOOP AT gint_error INTO gws_error. " WHERE msgty = gc_e.

   LOOP AT p_lint_error INTO gws_error.

     CONCATENATE gws_error-msgty gws_error-msg INTO lws_objbin-line

     SEPARATED BY cl_abap_char_utilities=>horizontal_tab.

     APPEND lws_objbin TO lint_objbin.

     CLEAR lws_objbin.

     CONCATENATE lws_objbin cl_abap_char_utilities=>newline INTO lws_objbin.

     APPEND lws_objbin TO lint_objbin.

     CLEAR lws_objbin.

     CLEAR gws_error.

   ENDLOOP.

   lw_tab_lines = LINES( lint_objbin ).

   IF lw_tab_lines > 1.

     DELETE  lint_objbin  INDEX lw_tab_lines.

   ENDIF.

   CLEAR lw_tab_lines.

   lw_tab_lines = LINES( lint_objbin ).

* Email subject Line

   CLEAR:lws_doc_chng,lws_objtxt.

   CLEAR lint_objtxt[].

*  REFRESH:lint_objtxt.

   lws_doc_chng-obj_name = lc_offer. " 'OFFER'.

   CONCATENATE text-t13 sy-datum " Automatic Stock transfer SE3/SE1

   INTO lws_doc_chng-obj_descr SEPARATED BY space.

* Email content

   lws_objtxt = text-t14. " 'Excel sheet with Automatic Stock transfer SE3/SE1' .

   APPEND lws_objtxt TO lint_objtxt.

   lws_objtxt = text-t15. " is enclosed as an attachment

   APPEND lws_objtxt TO lint_objtxt.

   CONCATENATE text-t16 sy-uzeit INTO lws_objtxt SEPARATED BY space. " Created time:

   APPEND lws_objtxt TO lint_objtxt.

*******

   lws_objtxt = sy-uline. " Line

   APPEND lws_objtxt TO lint_objtxt.

   lws_objtxt = text-h17. " Summary

   APPEND lws_objtxt TO lint_objtxt.

   lws_objtxt = text-h18. " Half line

   APPEND lws_objtxt TO lint_objtxt.

   CONCATENATE text-h19 p_lw_t INTO lws_objtxt. " Total Pallet's Picked

   APPEND lws_objtxt TO lint_objtxt.

   CONCATENATE text-h20 p_lw_s INTO lws_objtxt. " Succefully Processed

   APPEND lws_objtxt TO lint_objtxt.

   CONCATENATE text-h21 p_lw_v INTO lws_objtxt. " Validation Failed

   APPEND lws_objtxt TO lint_objtxt.

   CONCATENATE text-h22 p_lw_h INTO lws_objtxt. " Hu into Error

   APPEND lws_objtxt TO lint_objtxt.

*******

   CLEAR lw_tab_lines1.

   lw_tab_lines1 = LINES( lint_objtxt ).

*  DESCRIBE TABLE lint_objtxt LINES lw_tab_lines1.

   READ TABLE lint_objtxt INTO lws_objtxt INDEX lw_tab_lines1.

   lws_doc_chng-doc_size = ( lw_tab_lines1 - 1 ) * 255 + STRLEN( lws_objtxt ).

*  creating the entry for the compressed document

   CLEAR lws_objpack-transf_bin.

   lws_objpack-head_start = 1.

   lws_objpack-head_num   = 0.

   lws_objpack-body_start = 1.

   lws_objpack-body_num   = lw_tab_lines1.

   lws_objpack-doc_type   = lc_raw. " RAW

   APPEND lws_objpack TO lint_objpack.

   CONCATENATE text-t09 text-t10 INTO lws_objhead.

   APPEND lws_objhead TO lint_objhead.

   lws_objpack-transf_bin = gc_x.

   lws_objpack-head_start = 1.

   lws_objpack-head_num   = 1.

   lws_objpack-body_start = 1.

   lws_objpack-body_num   = lw_tab_lines.

   lws_objpack-doc_type   = lc_xls. " XLS

   lws_objpack-obj_name   = lc_obj_xls. " XLS

   lws_objpack-obj_descr = text-t09. " 'Automatic Stock Transfer'

   lws_objpack-doc_size   = lw_tab_lines * 255.

   APPEND lws_objpack TO lint_objpack.

* Rsceipents List

*      LOOP AT t_email WHERE vstel = p_vstel-low.

   lw_recname = p_mail.

   TRANSLATE lw_recname TO LOWER CASE.

   lws_reclist-receiver = lw_recname. " abc@gmail.com'. "t_email-email.

   lws_reclist-rec_type = lc_rectype. " 'U'.

*  TRANSLATE lws_reclist-receiver TO LOWER CASE.

   APPEND lws_reclist TO lint_reclist.

   CLEAR: lws_reclist.

*      ENDLOOP.

* Send Mail with Attachment

   CALL FUNCTION 'SO_NEW_DOCUMENT_ATT_SEND_API1'

     EXPORTING

       document_data              = lws_doc_chng

       put_in_outbox              = gc_x

       commit_work                = gc_x

     TABLES

       packing_list               = lint_objpack

       object_header              = lint_objhead

       contents_bin               = lint_objbin

       contents_txt               = lint_objtxt

       receivers                  = lint_reclist

     EXCEPTIONS

       too_many_receivers         = 1

       document_not_sent          = 2

       operation_no_authorization = 4

       OTHERS                     = 99.

   IF sy-subrc NE 0.

     MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

               WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

   ENDIF.

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

ENDFORM.                    " F_SEND_EMAIL

[Former Member]

Hi Srinivas,

Thanks for the reply, but i am not able to understand through your code that where i have to add my CSV content in excel format. I have got one internal table which has on field , that contas all the data separated by ' ; ' . Now how the data will be converted into excel to send as attachment.

Can you please ellaborate more. My code is like this.

CALL FUNCTION 'SAP_CONVERT_TO_CSV_FORMAT'
EXPORTING
I_FIELD_SEPERATOR = ';'
TABLES
I_TAB_SAP_DATA = it_mail_log
CHANGING
I_TAB_CONVERTED_DATA = it1_mail_log
EXCEPTIONS
CONVERSION_FAILED = 1
OTHERS = 2.
IF SY-SUBRC <> 0.
MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO WITH SY-MSGV1
SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF

.

Thanks

[former_member225498]

Hi Prachi,

See this example :

REPORT BCS_EXAMPLE_7.

* This report provides an example for sending an Excel
* attachment in Unicode Systems

CONSTANTS:
   GC_TAB  TYPE C VALUE CL_BCS_CONVERT=>GC_TAB,
   GC_CRLF TYPE C VALUE CL_BCS_CONVERT=>GC_CRLF.

PARAMETERS:
   MAILTO TYPE AD_SMTPADR
    DEFAULT 'john.doe@crazy-company.com'.                    "#EC *

DATA SEND_REQUEST   TYPE REF TO CL_BCS.
DATA DOCUMENT       TYPE REF TO CL_DOCUMENT_BCS.
DATA RECIPIENT      TYPE REF TO IF_RECIPIENT_BCS.
DATA BCS_EXCEPTION  TYPE REF TO CX_BCS.

DATA MAIN_TEXT      TYPE BCSY_TEXT.
DATA BINARY_CONTENT TYPE SOLIX_TAB.
DATA SIZE           TYPE SO_OBJ_LEN.
DATA SENT_TO_ALL    TYPE OS_BOOLEAN.

START-OF-SELECTION.
   PERFORM CREATE_CONTENT.
   PERFORM SEND.

*&---------------------------------------------------------------------*
*&      Form  send
*&---------------------------------------------------------------------*
FORM SEND.

   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 'Hello world!' TO MAIN_TEXT.                   "#EC NOTEXT
       DOCUMENT = CL_DOCUMENT_BCS=>CREATE_DOCUMENT(
         I_TYPE    = 'RAW'
         I_TEXT    = MAIN_TEXT
         I_SUBJECT = 'Test Created By BCS_EXAMPLE_7' ).      "#EC NOTEXT

*     add the spread sheet as attachment to document object
       DOCUMENT->ADD_ATTACHMENT(
         I_ATTACHMENT_TYPE    = 'xls'                        "#EC NOTEXT
         I_ATTACHMENT_SUBJECT = 'ExampleSpreadSheet'         "#EC NOTEXT
         I_ATTACHMENT_SIZE    = SIZE
         I_ATT_CONTENT_HEX    = BINARY_CONTENT ).

*     add document object to send request
       SEND_REQUEST->SET_DOCUMENT( DOCUMENT ).

*     --------- add recipient (e-mail address) -----------------------
*     create recipient object
       RECIPIENT = CL_CAM_ADDRESS_BCS=>CREATE_INTERNET_ADDRESS( MAILTO ).

*     add recipient object to send request
       SEND_REQUEST->ADD_RECIPIENT( RECIPIENT ).

*     ---------- 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 ----------------------------------
*   replace this rudimentary exception handling with your own one !!!
     CATCH CX_BCS INTO BCS_EXCEPTION.
       MESSAGE I865(SO) WITH BCS_EXCEPTION->ERROR_TYPE.
   ENDTRY.

ENDFORM.                    "send

*&---------------------------------------------------------------------*
*&      Form  create_content
*&---------------------------------------------------------------------*
* Create Example Content
* 1) Write example text into a string
* 2) convert this string to solix_tab
*----------------------------------------------------------------------*
FORM CREATE_CONTENT.

   DATA LV_STRING TYPE STRING.
   DATA LS_T100 TYPE T100.

* --------------------------------------------------------------
* as example content we use some system messages out of t100
* get them for all installed languages from db
* and write one line for each language into the spread sheet

* columns are separated by TAB and each line ends with CRLF

   CONCATENATE 'This Is Just Example Text!'                  "#EC NOTEXT
               GC_CRLF GC_CRLF
               INTO LV_STRING.

* header line
   CONCATENATE LV_STRING
               'MSGID'    GC_TAB
               'MSGNO'    GC_TAB
               'Language' GC_TAB                             "#EC NOTEXT
               'Text'     GC_CRLF                            "#EC NOTEXT
               INTO LV_STRING.

* data lines
   SELECT * FROM T100 INTO LS_T100
     WHERE ARBGB = 'SO' AND MSGNR = '182'.

     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.

   ENDSELECT.

   SELECT * FROM T100 INTO LS_T100
     WHERE ARBGB = 'SO' AND MSGNR = '316'.

     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.

   ENDSELECT.

* --------------------------------------------------------------
* convert the text string into UTF-16LE binary data including
* byte-order-mark. Mircosoft Excel prefers these settings
* all this is done by new class cl_bcs_convert (see note 1151257)

   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.

ENDFORM.                    "create_content

*---------------------------------------------------------------
* NOTES:
*---------------------------------------------------------------
* UTF-16LE including the BOM (Byte order mark)
* is preferred by Microsoft Excel. If you want to create
* other binary content you may choose another codepage (e.g.
* '4110' (UTF-8) which is standard for e-mails).
* Find SAP codepage names in the drop down list
* for the codepage setting of node SMTP in transaction SCOT.
* Or: leave iv_codepage and iv_add_bom empty. Then the target
* codepage is set according to SAPconnect settings
*
* Important:
* SAP neither guarantees that the attachment created
* by this report can be opened by all Excel Versions nor
* that it can be opened by any 3rd party software at all

[chngkhengkim]

Hi Prachi,

Some sample code below:

*** Content

*** I'm not using the FM to convert my internal table to csv format but manually do it.
   LOOP AT gtt_final ASSIGNING <gfs_final>.

CONCATENATE <gfs_final>-belnr  <gfs_final>-xblnr
                   <gfs_final>-recev  <gfs_final>-payor
                   <gfs_final>-waers  lv_wrbtr
                   <gfs_final>-bldat  <gfs_final>-faedt
                   <gfs_final>-blart  <gfs_final>-ltext
                   <gfs_final>-zterm  <gfs_final>-vtext
              INTO gwa_bin_attachment
      SEPARATED BY lc_comma.

       " Append a line feed after each of the content line
       CONCATENATE  lc_lf gwa_bin_attachment
       INTO         gwa_bin_attachment.

       APPEND gwa_bin_attachment TO gtt_bin_attachment.

ENDLOOP

*** Send email

************************************************************************
* Form f_send_email                                                    *
************************************************************************
FORM f_send_email.

   "Subject of the email
   gwa_document_data-obj_descr
   = 'Intercompany Receivables Netting report'.

   "Body of email
   PERFORM f_body_text_of_email
     USING :
     'Please find the attached Intercompany Receivables Netting report'.

   "Write Packing List for body
   DESCRIBE TABLE gtt_content LINES lv_tab_lines.
   gwa_packing_list-head_start = 1.
   gwa_packing_list-head_num   = 0.
   gwa_packing_list-body_start = 1.
   gwa_packing_list-body_num   = lv_tab_lines.
   gwa_packing_list-doc_type   = 'RAW'.
   APPEND gwa_packing_list TO gtt_packing_list.
   CLEAR  gwa_packing_list.

   "Write Packing List for Atachment
   DESCRIBE TABLE gtt_bin_attachment LINES gwa_packing_list-body_num.
   gwa_packing_list-transf_bin = 'X'.
   gwa_packing_list-head_start = 1.
   gwa_packing_list-head_num   = 1.
   gwa_packing_list-body_start = 1.
   gwa_packing_list-doc_type   = 'CSV'.
   gwa_packing_list-obj_descr  = p_efname.
   gwa_packing_list-obj_name   = p_efname.
   gwa_packing_list-doc_size   = gwa_packing_list-body_num * 255.
   APPEND gwa_packing_list TO gtt_packing_list.
   CLEAR  gwa_packing_list.

   "Fill the document data
   gwa_document_data-obj_langu = sy-langu.
   READ TABLE gtt_bin_attachment
   INTO gwa_bin_attachment INDEX lv_tab_lines.
   gwa_document_data-doc_size = ( lv_tab_lines - 1 ) * 255
                                + strlen( gwa_bin_attachment ).

   "Receivers List.
   LOOP AT s_eaddr.
     gwa_receivers-express  = 'X'.
     gwa_receivers-rec_type = 'U'.     " Internet address
     gwa_receivers-receiver = s_eaddr-low.
     gwa_receivers-com_type = 'INT'.   " Transmission Method
     APPEND gwa_receivers TO gtt_receivers .
     CLEAR: gwa_receivers.
   ENDLOOP.

   "Function module to send mail to Recipients
   CALL FUNCTION 'SO_NEW_DOCUMENT_ATT_SEND_API1'
     EXPORTING
       document_data              = gwa_document_data
       put_in_outbox              = 'X'
       commit_work                = 'X'
     TABLES
       packing_list               = gtt_packing_list
       object_header              = objhead
       contents_bin               = gtt_bin_attachment
       contents_txt               = gtt_content
*     contents_hex               = binary_content
       receivers                  = gtt_receivers
     EXCEPTIONS
       too_many_receivers         = 1
       document_not_sent          = 2
       document_type_not_exist    = 3
       operation_no_authorization = 4
       parameter_error            = 5
       x_error                    = 6
       enqueue_error              = 7
       OTHERS                     = 8.

   IF sy-subrc EQ 0.
     SKIP 2.
     WRITE: /1 'Email has been Successfully Sent to recipients".
   ELSE.
     WAIT UP TO 2 SECONDS.
     "This program starts the SAPconnect send process.
     SUBMIT rsconn01 WITH mode = 'INT'
     WITH output = 'X'
     AND RETURN.
   ENDIF.

ENDFORM.                    "f_send_email

- ice -