Read excel file with images from AL11 Directory and send as Email attachment

[Yogesh_bagul]

Introduction:

There are scenarios sometime user has uploaded excel file with images in application server which needs to be read and send it through email to supplier or customer.  

In this case we can use the DATASET read the file from application server and using class CL_BCS we can send the excel file as attachment.

Problem statement:

User uploads the file to application server with images in it. That excel file we have to read and attached to email.

Solution:

Step1 : Know the file path or directory path in which the file is stored.

DATA : lv_path TYPE eps2filnam.



lv_path = 'usr/sap/folder/filewithimage.xls'.

Step2: Use OPEN DATASET keyword to open the file, use READ DATASET to read the file content and CLOSE DATASET to close the open file. Store each line item into one string variable using concatenate.

DATA : ls_read_file TYPE string,

       ls_all_data  TYPE string.



OPEN DATASET lv_path FOR INPUT IN BINARY MODE.



IF sy-subrc IS INITIAL.

  DO.

    READ DATASET lv_path INTO ls_read_file.

    IF sy-subrc IS NOT INITIAL.

       EXIT.

    ELSE.

      CONCATENATE ls_all_data ls_read_file INTO ls_all_data.

    ENDIF.

  ENDDO.

ENDIF.



CLOSE DATASET lv_path.

Step3 : Now we have all data in one string variable and ready to convert data from string to text using CL_BCS_CONVERT.

DATA : lt_file_data TYPE soli_tab.



IF ls_all_data IS NOT INITIAL.

   lt_file_data = cl_bcs_convert=>string_to_soli( iv_string = ls_all_data ).

ENDIF.

Step4 : we have all data which is converted to compatible format and ready to send as email excel attachment.

DATA : lo_document           TYPE REF TO cl_document_bcs VALUE IS INITIAL,

       lo_document_exception TYPE REF TO cx_document_bcs VALUE IS INITIAL,

       lo_send_request       TYPE REF TO cl_bcs VALUE IS INITIAL,

       lo_sender             TYPE REF TO if_sender_bcs VALUE IS INITIAL,

       lo_recipient          TYPE REF TO if_recipient_bcs VALUE IS INITIAL,

       lv_recipient          TYPE adr6-smtp_addr VALUE IS INITIAL,

       lv_send_to_all        TYPE char1 VALUE IS INITIAL.



CREATE OBJECT lo_document.



TRY.

    lo_document = cl_document_bcs=>create_document(

                    i_type         = 'RAW'

                    i_subject      = 'Email subject'

                    i_text         = 'Email body').

  CATCH cx_document_bcs INTO lo_document_exception.

    lo_document_exception->get_text( ).

ENDTRY.



IF lt_file_data IS NOT INITIAL.

  TRY.

      lo_document->add_attachment(

        EXPORTING

          i_attachment_type     = 'xls'            " Document Class for Attachment

          i_attachment_subject  = 'File name'             " Attachment Title

          i_att_content_text    = lt_file_data ).    " Content (Text-Like)

    CATCH cx_document_bcs INTO lo_document_exception.

      lo_document_exception->get_text( ).

  ENDTRY.



  "pass the docuement to send request

  lo_send_request->set_document( lo_document ).

  "Create sender

  lo_sender = cl_sapuser_bcs=>create( su-uname ).

  "set sender

  lo_send_request = cl_bcs=>create_persistent( ).

  "assign sender

  lo_send_request->set_sender( i_sender = lo_sender ).

  "Create recipient

  lv_recipient = '____@xyz.com'. " recipient address

  "assign recipient

  lo_recipient = cl_cam_address_bcs=>create_internet_address( lv_recipient ).

  "set recipient

  lo_send_request->add_recipient(

    EXPORTING

      i_recipient  = lo_recipient       " Recipient of Message

      i_express    = 'X' ).             " Send As Express Message

  "send email

   lo_send_request->send(

       i_with_error_screen = 'X'

       RECEIVING 

       result = lv_send_to_all ). 

    COMMIT WORK.

ENDIF.

Step5 : Now check SOST tcode where email suppose to be triggered with having one attachment of excel file with images as it is available in AL11 to it.

 

Conclusion:

I hope this blog post will help you and get the idea how to read the excel file with images in it from application server (AL11) and same file use as attachment to your email.

 

Please like and share feedback or thoughts in comment.

[Sandra_Rossi]

As you say your Excel file contains images, I'm sure that it's a file in binary format ("Excel files" can be in various formats, like text format when it's CSV or tab-delimited values).

So, because it's a file in binary format, you're correct in opening the file in binary mode.

But then, you should NOT continue your program with text variables (i.e. STRING, SOLI_TAB), you should continue with binary variables (i.e. XSTRING, SOLIX_TAB).

You should read the file into XSTRING variable. Also, you can simplify as you don't need to iterate on "lines" because it's binary mode (there's no line):

DATA : ls_all_data  TYPE xstring.



OPEN DATASET lv_path FOR INPUT IN BINARY MODE.

IF sy-subrc = 0.

  READ DATASET lv_path INTO ls_all_data.

  CLOSE DATASET lv_path.

ENDIF.

To convert:

DATA : lt_file_data TYPE solix_tab.



lt_file_data = cl_bcs_convert=>xstring_to_solix( ls_all_data ).

and you should attach with parameters dedicated to binary format and pass the exact length (in number of bytes) of the file to BCS, otherwise Excel would fail opening the file sometimes.

      lo_document->add_attachment(

        EXPORTING

          i_attachment_type     = 'xls'                   " Document Class for Attachment

          i_attachment_subject  = 'File name'             " Attachment Title

          I_ATTACHMENT_SIZE     = xstrlen( ls_all_data )

          I_ATT_CONTENT_HEX     = lt_file_data ).         " Content (binary-Like)

That said, lots of snippets exist in the Web, and there are also SAP Demo programs inside your ABAP system.

[Yogesh_bagul]

Hi sandra.rossi

 

Good see you gone through details blog. BUT

If you use binary variable, there might more chances to get garbage values in excel attachment, as in READ DATA set we are already reading file in binary mode. I appreciate as your logic will be suppose to applicable when you will READ DATA set in TEXT MODE with non-Unicode convertible .

 

Thanks & Regards,

Yogesh Bagul

[Sandra_Rossi]

Well, what you say is exactly the opposite of basics of computer programming, text encoding versus not text ("binary"), so I'm not sure to be able to convince you. If you open a file in text mode, store the data in a character variable (open dataset will drive the text-decoding), and if you open in binary mode, store the data in a binary variable. For more information, read the doc about OPEN DATASET and books about text encoding (and decoding). When you have some bugs reported, I advise you trying my proposal. Good luck.

[Yogesh_bagul]

Hello sandra.rossi

 

Your comments are welcome, i will go through your suggestion and will try.

 

Thank You

[Jelena_Perfiljeva]

As Sandra mentioned, there is no conversion needed. You read the file in binary format and you attach that binary data to the email as is. This would work for PDF, Excel, JPEG, any file that is stored in a binary (as opposed to plain text) format. I'm actually quite confused why this post is specific to an Excel file with images. There doesn't seem to be anything in the code exactly for that...

The statement that you get "garbage" values when using binary format is just plain incorrect. It works fine when the code is written correctly. Otherwise many SAP customers would have quite a problem.

Also as Sandra noted, there are plenty of snippets on this subject (reading a file and sending it by email). This post is from the top of Google search: https://blogs.sap.com/2020/01/06/object-oriented-way-of-sending-an-email-with-pdf-as-an-attachment/  It is about PDF but, again, binary format works for many scenarios, there isn't any specific need to write separate blog posts for each file type. It's all the same code. I mean if you want to do it, nothing stopping you but then at least make sure what you're sharing is accurate.

It is important to understand the binary vs. plain text concept, you might want to read up on that.