Sending XLS via email with cl_bcs
If you search, as I did, for examples of how to implement the most basic method for converting an internal table to an Excel file and emailing it, you might find yourself scratching your head. There’s a lot they assume in some of those examples. The oft-cited note with the sample code everyone references (1151258) leaves a few details out. Unless you are a hardcore ABAPer with years of every-day experience under your belt, you might struggle. Or you might spend hours poring over the SAP documentation, which, again, assumes much and leaves out some details that will cause the clueless (yours truly) to stumble. This post aims to clarify some of these points.
- cl_bcs_convert=>cl_bcs_solix() is the method you’ll be using to convert your internal table to an Excel file, though that’s not exactly true. First, you’ll need to convert your internal table to a string, delimited by tabs and carriage returns at the end of each line. (Watch those non-character data types!) Speaking of which, data types are important, so you can’t use just any old tab and carriage return. Use the BCS constants. See below for an example.
CONSTANTS: gc_tab TYPE c VALUE cl_bcs_convert=>gc_tab, gc_crlf TYPE c VALUE cl_bcs_convert=>gc_crlf.
- The cl_bcs_solix() method itself is fairly straightforward, but again, data types are important. Study the interface and declare your variables correctly. Remember, what you get is a binary XLS file, not a file name or path.
cl_bcs_convert=>string_to_solix( EXPORTING iv_string = my_spreadsheetdata " your delimited string iv_codepage = '4103' " for MS Excel iv_add_bom = 'X' IMPORTING et_solix = my_excel_file " the binary, XLS file ev_size = size ).
- Creating the email is relatively easy too, once you understand what is going on with the object model. I like to think of it using a basic envelope-letter metaphor. See comments below:
DATA: message TYPE REF TO cl_bcs, " envelope document TYPE REF TO cl_document_bcs, " letter mailto TYPE ad_smtpadr VALUE 'firstname.lastname@example.org', mailfrom TYPE ad_smtpadr VALUE 'email@example.com', message = cl_bcs=>create_persistent( ). " message = the email itself APPEND 'Hello World!' TO body_text. " body_text is an internal table * think of message as the envelope, and document as the letter. document = cl_document_bcs=>create_document( i_type = 'RAW' i_text = body_text " again, this is just a table containing your message body i_subject = subject_line ). " subject_line is just text, too * now you are going to attach your spreadsheet to the letter document->add_attachment( i_attachment_type = 'xls' i_attachment_subject = 'ProjectInfoSpreadSheet' " your file name i_attachment_size = size " output of the string_to_solix method i_att_content_hex = my_excel_file ). " output of the string_to_solix method * next, put the letter in the envelope message->set_document( document ). * write the addresses on the envelope recipient = cl_cam_address_bcs=>create_internet_address( mailto ). sender = cl_cam_address_bcs=>create_internet_address( mailfrom ). message->add_recipient( recipient ). message->set_sender( sender ). * finally, send the envelope on its way sent_to_all = message->send( i_with_error_screen = 'X'). COMMIT WORK.
Hopefully, this example gets you started. I am just learning these objects, so there’s probably tons I am leaving out. My goal was to simply get you started with sending a basic spreadsheet through email. As I learn more, I will post again. Until then, I’d appreciate your comments and questions.
See also, the sample program BCS_EXAMPLE_7 in SE38/SE80
Quite interesting post, Ed... Gotta do some more research in the topic starting at your post.
Hi Ed, thanks for sharing.
But when opening generated file I get a warning "File is in a different format". Any suggestions how to avoid this?
No idea. I haven't been able to figure that out. I just live with it. And most of my users take the spreadsheets, not as a finished product, but as a starting point to do other analysis/manipulation.
There are tools and tutorials on SDN now that talk about creating XLSX files out of ABAP. I haven't tried them, but you might do a search for them.
A helpful one !! Keep it up.:)
I tried in the same way. But I'm not receiving the mails in my Inbox. What could be the possible issue?
Pls check SOST transaction code to see whether emails are triggering or not. Also coordinate with basis on whether configurations are in place or not.
Thanks. The mails were stuck in SOST t-code. BASIS team has locked in the test systems.