Skip to Content
avatar image
Former Member

Decorating EXCEL Sheet which need to be send as an attachment in EMAIL

My report program need to send the data in an internal table through EMAIL as an EXCEL Sheet attachment...

I am able to send the data perfectly....

But i need to add some look good environment (as making the header bold and with a different colour) for the EXCEL Sheet...

Please advice me on how i could do this ?

Thanks a lot,

Raj....

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • avatar image
    Former Member
    Oct 20, 2008 at 06:20 PM

    Hello Raja,

    I can help you how to trigger a program which sends the records to EXCEL sheet automatically. Features that you were asking like Bold and colorful all i have shown in the excel sheet.

    Just copy paste the code and execute you will see Excel sheet automatically triggered.

    Modify the code accordingly to your requirement. Any issues revert me back i will help you.

    REPORT  ZREDDY_VBAP                               .
    
    * sending ABAP data to an
    * EXCEL sheet using OLE automation.
    include ole2incl.
    * handles for OLE objects
    data: h_excel type ole2_object,        " Excel object
          h_mapl type ole2_object,         " list of workbooks
          h_map type ole2_object,          " workbook
          h_zl type ole2_object,           " cell
          h_f type ole2_object,            " font
          h_c type ole2_object.            " color
    
    data: filename like rlgrap-filename.
    
    tables: vbap.
    data  h type i.
    * table of flights
    data: it_vbap like vbap occurs 10 with header line.
    
    *&---------------------------------------------------------------------*
    
    *&   Event START-OF-SELECTION
    *&---------------------------------------------------------------------*
    
    start-of-selection.
    * read flights
      select * from vbap into table it_vbap.
    * display header
      uline (61).
      write: /     sy-vline no-gap,
              (6)  'ITEM'(001) color col_heading no-gap, sy-vline no-gap,
              (18)  'MATERIAL'(002) color col_heading no-gap, sy-vline
    no-gap
    ,
              (30) 'DESCRIPTION'(003) color col_heading no-gap, sy-vline
    no-gap,
              (35) 'ORDER_QUANTITY'(004) color col_heading no-gap, sy-vline
    no-gap,
              (8)  'S_UNIT'(005) color col_heading no-gap, sy-vline no-gap.
      uline /(61).
    * display flights
      loop at it_vbap.
        write: / sy-vline no-gap,
                 it_vbap-posnr color col_key no-gap, sy-vline no-gap,
                 it_vbap-matwa color col_normal no-gap, sy-vline no-gap,
                 it_vbap-arktx color col_normal no-gap, sy-vline no-gap,
                 it_vbap-awahr color col_normal no-gap, sy-vline no-gap,
                 it_vbap-vrkme color col_normal no-gap, sy-vline no-gap.
      endloop.
      uline /(61).
    * tell user what is going on
      call function 'SAPGUI_PROGRESS_INDICATOR'
         exporting
    *           PERCENTAGE = 0
               text       = text-007
           exceptions
                others     = 1.
    * start Excel
      create object h_excel 'EXCEL.APPLICATION'.
    *  PERFORM ERR_HDL.
    
      set property of h_excel  'Visible' = 1.
    *  CALL METHOD OF H_EXCEL 'FILESAVEAS' EXPORTING #1 = 'c:\sales.xls'
      .
    
    *  PERFORM ERR_HDL.
    * tell user what is going on
      call function 'SAPGUI_PROGRESS_INDICATOR'
         exporting
    *           PERCENTAGE = 0
               text       = text-008
           exceptions
                others     = 1.
    * get list of workbooks, initially empty
      call method of h_excel 'Workbooks' = h_mapl.
      perform err_hdl.
    * add a new workbook
      call method of h_mapl 'Add' = h_map.
      perform err_hdl.
    * tell user what is going on
      call function 'SAPGUI_PROGRESS_INDICATOR'
         exporting
    *           PERCENTAGE = 0
               text       = text-009
           exceptions
                others     = 1.
    * output column headings to active Excel sheet
      perform fill_cell using 1 1 1 200 'ITEM'(001).
      perform fill_cell using 1 2 1 200 'MATERIAL'(002).
      perform fill_cell using 1 3 1 200 'DESCRIPTION'(003).
      perform fill_cell using 1 4 1 200 'ORDER_QUANTITY'(004).
      perform fill_cell using 1 5 1 200 'S_UNIT'(005).
      loop at it_VBAP.
    * copy flights to active EXCEL sheet
        h = sy-tabix + 1.
        if it_VBAP-POSNR cs '000010'.
          perform fill_cell using h 1 0 000255000 it_VBAP-POSNR.
        elseif it_VBAP-POSNR cs '000020'.
          perform fill_cell using h 1 0 168000000 it_VBAP-POSNR.
        elseif it_VBAP-POSNR cs '000030'.
          perform fill_cell using h 1 0 168168000 it_VBAP-POSNR.
        elseif it_VBAP-POSNR cs '000040'.
          perform fill_cell using h 1 0 111111111 it_VBAP-POSNR.
        elseif it_VBAP-POSNR cs 'SQ'.
          perform fill_cell using h 1 0 100100100 it_VBAP-POSNR.
        else.
          perform fill_cell using h 1 0 000145000 it_VBAP-POSNR.
        endif.
    
        if it_VBAP-MATWA lt 'P-*'.
          perform fill_cell using h 2 0 255000255 it_VBAP-MATWA.
        elseif it_VBAP-MATWA lt 'M-*'.
          perform fill_cell using h 2 0 770990885 it_VBAP-MATWA.
          elseif it_VBAP-MATWA lt 'L-*'.
          perform fill_cell using h 2 0 077099048 it_VBAP-MATWA.
        else.
          perform fill_cell using h 2 0 246156138 it_VBAP-MATWA.
        endif.
    
        if it_vbap-arktx cp 'S*'.
          perform fill_cell using h 3 0 155155155 it_vbap-arktx.
        elseif it_vbap-arktx cp 'F*'.
          perform fill_cell using h 3 0 189111222 it_vbap-arktx.
          elseif it_vbap-arktx cp 'J*'.
          perform fill_cell using h 3 0 289111222 it_vbap-arktx.
    
        else.
          perform fill_cell using h 3 0 111230222 it_vbap-arktx.
        endif.
    
        if it_vbap-awahr cp 'S*'.
          perform fill_cell using h 4 0 200200200 it_vbap-awahr.
        elseif it_vbap-awahr cp 'N*'.
          perform fill_cell using h 4 0 000111222 it_vbap-awahr.
        else.
          perform fill_cell using h 4 0 130230230 it_vbap-awahr.
        endif.
    
        if it_vbap-vrkme lt '020000'.
          perform fill_cell using h 5 0 145145145 it_vbap-vrkme.
        elseif it_vbap-vrkme lt '120000' .
          perform fill_cell using h 5 0 015215205 it_vbap-vrkme.
        elseif it_vbap-vrkme lt '180000' .
          perform fill_cell using h 5 0 000215205 it_vbap-vrkme.
        else.
          perform fill_cell using h 5 0 115115105 it_vbap-vrkme.
        endif.
      endloop.
    
    
    * EXCEL FILENAME
      concatenate sy-repid '_' sy-datum+6(2) '_' sy-datum+4(2) '_'
                  sy-datum(4) '_' sy-uzeit '.XLS' into filename.
    
      call method of h_map 'SAVEAS' exporting #1 = filename.
    
    
      free object h_excel.
      perform err_hdl.
    *---------------------------------------------------------------------*
    *       FORM FILL_CELL                                                *
    *---------------------------------------------------------------------*
    *       sets cell at coordinates i,j to value val boldtype bold       *
    *---------------------------------------------------------------------*
    form fill_cell using i j bold col val.
      call method of h_excel 'Cells' = h_zl
        exporting
          #1 = i
          #2 = j.
      perform err_hdl.
      set property of h_zl 'Value' = val .
      perform err_hdl.
      get property of h_zl 'Font' = h_f.
      perform err_hdl.
      set property of h_f 'Bold' = bold .
      perform err_hdl.
      set property of h_f 'Color' = col.
      perform err_hdl.
    endform.                    "FILL_CELL
    
    *&---------------------------------------------------------------------*
    
    *&      Form  ERR_HDL
    *&---------------------------------------------------------------------*
    
    *       outputs OLE error if any                                       *
    
    *----------------------------------------------------------------------*
    
    *  -->  p1        text
    *  <--  p2        text
    *----------------------------------------------------------------------*
    
    form err_hdl.
      if sy-subrc <> 0.
        write: / 'OLE-Automation Error:'(010), sy-subrc.
        stop.
      endif.
    endform.                    " ERR_HDL
    

    Cheers!!

    Balu

    Edited by: Balu CH on Oct 21, 2008 10:54 PM

    Code Formatted by: Alvaro Tejada Galindo on Jan 7, 2009 2:20 PM

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 20, 2008 at 06:20 PM
    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 20, 2008 at 06:26 PM

    Double-posting (and any other forum rules violations) are unfair on those who take the effort to help you by answering or giving you advise.

    When people see this, they will stop answering your questions...

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 20, 2008 at 06:43 PM

    Hi Raj,

    Got the excel sheet with your requirement or any issues.

    Cheers!!

    Balu

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      hI bALU

      i am sorry, i did not try with your code.. My report will be scheduled as a brackground job and so creating a file on local system can't help me to send that as a mail attachment..

      Thanks a lot for ur inputs.

      Hi All

      Could some one please out help me with a solution for the requirement?

  • avatar image
    Former Member
    Oct 21, 2008 at 08:09 PM

    1. Open up a sample excel with the format you want.

    2. File -> Save as HTML.

    3. Open that HTML

    4. Do a view source. Replace data with variables

    5. Copy that source and go to SAP SO10 transaction. Paste that source.

    6. In your program do a read text to get that source.

    7. Now replace variables with data from your program.

    8. Send that email as .txt attachement

    9. Give instruction in your email body for them to right clicka and save as .XLS

    10. If you do this right the excel will be formatted.

    Add comment
    10|10000 characters needed characters exceeded