*&---------------------------------------------------------------------*
*& Report  zcsdi122_xx                                                                           *
*&                                                                                                       *
*&---------------------------------------------------------------------*
*& Example of sending external email via SAPCONNECT                            *
*& 화일이 보내지는데, 'filename'  자리에 넣어도 계속 첨부파일이 .xls 로 되어 *

*& 한참 고생했습니다. filename 및에 p_excel이라는 자리에 넣으면 제대로      *

*& 국내20070413.xls 화일이 제대로 첨부됩니다.                                          *
*&---------------------------------------------------------------------*
REPORT  zcsdi122_xx.

INCLUDE zcsdi122_xxtop.

*PARAMETER : s_date  LIKE sy-datum.
DATA : t_date LIKE sy-datum.
DATA : s_date LIKE sy-datum.
DATA : s_flag LIKE sy-subrc.

* working day를 산출

CALL FUNCTION 'BKK_ADD_WORKINGDAY'
  EXPORTING
    i_date       = t_date
    i_days       = 1
    i_calendar1  = 'Z1'
*    i_calendar2
  IMPORTING
    e_date       = s_date
    e_return     = s_flag.

START-OF-SELECTION.

  PERFORM data_retrieval.

  PERFORM build_xls_data_table.

END-OF-SELECTION.

* Populate message body text
  PERFORM populate_email_message_body.

* Send file by email as .xls speadsheet
  CONCATENATE '출고_' s_date INTO p_fname.

  p_email = 'xxxx@xxxx.co.kr'. 
  CONCATENATE  '국내' s_date INTO p_excel.

  PERFORM send_file_as_email_attachment
                               TABLES it_message
                                      it_attach
                                USING p_email
                                      p_fname      "Email Title
                                      'XLS'
                                      'filename'
                                      p_excel      "첨부파일명
                                      ' '
                                      ' '
                             CHANGING gd_error
                                      gd_reciever.

*   Instructs mail send program for SAPCONNECT to send email(rsconn01)
  PERFORM initiate_mail_execute_program.

*&---------------------------------------------------------------------*
*&      Form  DATA_RETRIEVAL
*&---------------------------------------------------------------------*
*       Retrieve data form EKPO table and populate itab it_ekko
*----------------------------------------------------------------------*
FORM data_retrieval.

  REFRESH : gt_list,lt_list.

  SELECT b~matnr
         b~werks
         b~lgort
         b~charg
         b~meins
         SUM( b~kwmeng ) AS clabs
   INTO  CORRESPONDING FIELDS OF TABLE lt_list
   FROM  vbak AS a INNER JOIN vbap AS b
     ON  b~mandt = a~mandt AND
         a~vbeln = b~vbeln
  WHERE a~mandt = sy-mandt
    AND b~lgort = 'Sxxx'
    AND a~vdatu = s_date
  GROUP BY b~matnr b~werks b~lgort b~meins b~charg.

  SELECT a~matnr         AS matnr     " material code
         a~werks         AS werks     " werks
         a~lgort         AS lgort     " storage loc
         b~meins         AS meins     " bum
         a~charg         AS charg
         a~clabs         AS clabs     " batch stock
   INTO  CORRESPONDING FIELDS OF  lt_list
   FROM  mchb AS a INNER JOIN mara AS b
     ON  b~mandt = a~mandt
    AND  b~matnr = a~matnr
  WHERE  a~mandt  =  sy-mandt
    AND  a~lgort = 'Sxxx'
  ORDER BY a~matnr a~werks a~lgort b~meins a~charg.

    COLLECT  lt_list.  CLEAR lt_list.
  ENDSELECT.

  LOOP AT lt_list.
    gt_list-matnr = lt_list-matnr.
    gt_list-werks = lt_list-werks.
    gt_list-lgort = lt_list-lgort.
    gt_list-meins = lt_list-meins.

    CLEAR : makt.
    SELECT SINGLE c~maktx d~umrez
      INTO (makt-maktx, marm-umrez)
      FROM makt AS c INNER JOIN marm  AS d
        ON c~matnr = d~matnr
    WHERE c~mandt = sy-mandt
      AND c~spras = 'E'
      AND d~meinh IN ('BOX', 'OBX' )
      AND c~matnr = lt_list-matnr.

    gt_list-maktx = makt-maktx.
    gt_list-umrez = marm-umrez.

    IF lt_list-charg = 'batch'.
      gt_list-dummy = lt_list-clabs.
    ELSEIF lt_list-charg = '' OR  lt_list-charg = ''.
      gt_list-orqty = lt_list-clabs.
    ELSE.
      gt_list-clabs = lt_list-clabs.
    ENDIF.

    COLLECT  gt_list.
    CLEAR gt_list.
  ENDLOOP.

  DELETE gt_list WHERE orqty = 0.

  LOOP AT gt_list.
    gt_list3-matnr = gt_list-matnr.
    gt_list3-werks = gt_list-werks.
    gt_list3-lgort = gt_list-lgort.
    gt_list3-meins = gt_list-meins.
    gt_list3-maktx = gt_list-maktx.
    gt_list3-umrez = gt_list-umrez.
    gt_list3-dummy = gt_list-dummy.
    gt_list3-orqty = gt_list-orqty.
    gt_list3-clabs = gt_list-clabs.
    gt_list3-boxq = 0.
    APPEND gt_list3.
  ENDLOOP.

ENDFORM.                    " DATA_RETRIEVAL

*&---------------------------------------------------------------------*
*&      Form  BUILD_XLS_DATA_TABLE
*&---------------------------------------------------------------------*
*       Build data table for .xls document
*----------------------------------------------------------------------*
FORM build_xls_data_table.
*  CONSTANTS: con_cret TYPE c VALUE '0D',  "OK for non Unicode
*             con_tab TYPE c VALUE '09'.   "OK for non Unicode

*If you have Unicode check active in program attributes thnen you will
*need to declare constants as follows
*class cl_abap_char_utilities definition load.
  CONSTANTS:
      con_tab  TYPE c VALUE cl_abap_char_utilities=>horizontal_tab,
      con_cret TYPE c VALUE cl_abap_char_utilities=>cr_lf.

* Query 한 데이터를 꼭 CHAR 타입으로 변화

  LOOP AT gt_list3.
    MOVE-CORRESPONDING  gt_list3 TO lt_excel.
    APPEND lt_excel.
    CLEAR  lt_excel.
  ENDLOOP.

* 엑셀 문서를 헤더(타이틀)

  CONCATENATE '상품'   '제상품명' 'Plant'     '창고'       '단위'
              '주문량' '재고'     'BATCH재고' '박스내입수'
              '박스수' '낱개수'
         INTO it_attach SEPARATED BY con_tab.
  CONCATENATE con_cret it_attach  INTO it_attach.
  APPEND  it_attach.

* 엑셀 문서의 내용

  LOOP AT lt_excel INTO wa_charekpo.
    CONCATENATE wa_charekpo-matnr
                wa_charekpo-maktx
                wa_charekpo-werks
                wa_charekpo-lgort
                wa_charekpo-meins
                wa_charekpo-orqty
                wa_charekpo-dummy
                wa_charekpo-clabs
                wa_charekpo-umrez
                wa_charekpo-boxq
                wa_charekpo-modq
    INTO it_attach SEPARATED BY con_tab.
    CONCATENATE con_cret it_attach  INTO it_attach.
    APPEND  it_attach.
  ENDLOOP.

ENDFORM.                    " BUILD_XLS_DATA_TABLE

*&---------------------------------------------------------------------*
*&      Form  SEND_FILE_AS_EMAIL_ATTACHMENT
*&---------------------------------------------------------------------*
*       Send email
*----------------------------------------------------------------------*
FORM send_file_as_email_attachment TABLES pit_message
                                          pit_attach
                                    USING p_email
                                          p_mtitle
                                          p_format
                                          p_filename
                                          p_attdescription
                                          p_sender_address
                                          p_sender_addres_type
                                 CHANGING p_error
                                          p_reciever.

  DATA: ld_error    TYPE sy-subrc,
        ld_reciever TYPE sy-subrc,
        ld_mtitle LIKE sodocchgi1-obj_descr,
        ld_email LIKE  somlreci1-receiver,
        ld_format TYPE  so_obj_tp ,
        ld_attdescription TYPE  so_obj_nam ,
        ld_attfilename TYPE  so_obj_des ,
        ld_sender_address LIKE  soextreci1-receiver,
        ld_sender_address_type LIKE  soextreci1-adr_typ,
        ld_receiver LIKE  sy-subrc.

  ld_email   = p_email.
  ld_mtitle  = p_mtitle.
  ld_format              = p_format.
  ld_attdescription      = p_attdescription.
  ld_attfilename         = p_filename.
  ld_sender_address      = p_sender_address.
  ld_sender_address_type = p_sender_addres_type.

* Fill the document data.
  w_doc_data-doc_size = 1.

* Populate the subject/generic message attributes
  w_doc_data-obj_langu = sy-langu.
  w_doc_data-obj_name  = 'SAPRPT'.
  w_doc_data-obj_descr = ld_mtitle .
  w_doc_data-sensitivty = 'F'.

* Fill the document data and get size of attachment
  CLEAR w_doc_data.
  READ TABLE it_attach INDEX w_cnt.
  w_doc_data-doc_size =
     ( w_cnt - 1 ) * 255 + STRLEN( it_attach ).
  w_doc_data-obj_langu  = sy-langu.
  w_doc_data-obj_name   = 'SAPRPT'.
  w_doc_data-obj_descr  = ld_mtitle.
  w_doc_data-sensitivty = 'F'.
  CLEAR t_attachment.
  REFRESH t_attachment.
  t_attachment[] = pit_attach[].

** Describe the body of the message
  CLEAR t_packing_list.
  REFRESH t_packing_list.
  t_packing_list-transf_bin = space.
  t_packing_list-head_start = 1.
  t_packing_list-head_num = 0.
  t_packing_list-body_start = 1.
  DESCRIBE TABLE it_message LINES t_packing_list-body_num.
  t_packing_list-doc_type = 'RAW'.
  APPEND t_packing_list.

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

  DESCRIBE TABLE t_attachment LINES t_packing_list-body_num.
  t_packing_list-doc_type   =  ld_format.
  t_packing_list-obj_descr  =  ld_attdescription.
  t_packing_list-obj_name   =  ld_attfilename.
  t_packing_list-doc_size   =  t_packing_list-body_num * 255.
  APPEND t_packing_list.

* Add the recipients email address
  CLEAR t_receivers.
  REFRESH t_receivers.
  t_receivers-receiver = ld_email.
  t_receivers-rec_type = 'U'.
  t_receivers-com_type = 'INT'.
  t_receivers-notif_del = 'X'.
  t_receivers-notif_ndel = 'X'.
  APPEND t_receivers.

*{Comment : 이메일 사이트 추가
  t_receivers-receiver = 'yyyyy@yyyyy.co.kr'.
  t_receivers-rec_type = 'U'.
  t_receivers-com_type = 'INT'.
  t_receivers-notif_del = 'X'.
  t_receivers-notif_ndel = 'X'.
  APPEND t_receivers.
*}

  CALL FUNCTION 'SO_DOCUMENT_SEND_API1'
    EXPORTING
      document_data              = w_doc_data
      put_in_outbox              = 'X'
      sender_address             = ld_sender_address
      sender_address_type        = ld_sender_address_type
      commit_work                = 'X'
    IMPORTING
      sent_to_all                = w_sent_all
    TABLES
      packing_list               = t_packing_list
      contents_bin               = t_attachment
      contents_txt               = it_message
      receivers                  = t_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.

* Populate zerror return code
  ld_error = sy-subrc.

* Populate zreceiver return code
  LOOP AT t_receivers.
    ld_receiver = t_receivers-retrn_code.
  ENDLOOP.

ENDFORM.                    "send_file_as_email_attachment

*&---------------------------------------------------------------------*
*&      Form  INITIATE_MAIL_EXECUTE_PROGRAM
*&---------------------------------------------------------------------*
*       Instructs mail send program for SAPCONNECT to send email.
*----------------------------------------------------------------------*
FORM initiate_mail_execute_program.

  WAIT UP TO 2 SECONDS.

  SUBMIT rsconn01 WITH mode = 'INT'
                WITH output = ' '  "'X'
                AND RETURN.

ENDFORM.                    " INITIATE_MAIL_EXECUTE_PROGRAM

*&---------------------------------------------------------------------*
*&      Form  POPULATE_EMAIL_MESSAGE_BODY
*&---------------------------------------------------------------------*
*        Populate message body text
*----------------------------------------------------------------------*
FORM populate_email_message_body.

  REFRESH it_message.

  it_message = '오늘도 즐거운 하루 되세요!!!!'.
  APPEND it_message.

ENDFORM.                    " POPULATE_EMAIL_MESSAGE_BODY