topic Problem with EXCEL format while sending a mail with excel attachment in Application Development and Automation Discussions

Problem with EXCEL format while sending a mail with excel attachment

Former Member — Mon, 11 Jun 2007 06:30:35 GMT

Hi,

I'm trying to send a report through mail as an excel attchment. But though I get all the details in the attachmnet format in excel is not proper. It's in a very hapazard way. I tried putting control break statements after each record then also though there is slight betterment it's not ok. If I send the same thing as a pdf attachment it's perfect. May I know what else i need to do for excel.....? I tried compressing and then sending no luck......

I'm in version 4.6c.....

Thanks in advance....

Re: Problem with EXCEL format while sending a mail with excel attachment

Former Member — Mon, 11 Jun 2007 06:36:54 GMT

Hi,

try this CODE

FORM SEND_MAIL.

DATA: OBJPACK LIKE SOPCKLSTI1 OCCURS 2 WITH HEADER LINE.

DATA: OBJHEAD LIKE SOLISTI1 OCCURS 1 WITH HEADER LINE.

DATA: OBJBIN LIKE SOLISTI1 OCCURS 10 WITH HEADER LINE.

DATA: OBJTXT LIKE SOLISTI1 OCCURS 10 WITH HEADER LINE.

DATA: RECLIST LIKE SOMLRECI1 OCCURS 5 WITH HEADER LINE.

DATA: DOC_CHNG LIKE SODOCCHGI1.

DATA: TAB_LINES LIKE SY-TABIX.

DATA L_NUM(3).

Creation of the document to be sent
File Name

DOC_CHNG-OBJ_NAME = 'SENDFILE'.

Mail Subject

DOC_CHNG-OBJ_DESCR = 'Delivered Mail'.

Mail Contents

OBJTXT = 'Object text'.

APPEND OBJTXT.

DESCRIBE TABLE OBJTXT LINES TAB_LINES.

READ TABLE OBJTXT INDEX TAB_LINES.

DOC_CHNG-DOC_SIZE = ( TAB_LINES - 1 ) * 255 + STRLEN( OBJTXT ).

Creation of the entry for the compressed document

CLEAR OBJPACK-TRANSF_BIN.

OBJPACK-HEAD_START = 1.

OBJPACK-HEAD_NUM = 0.

OBJPACK-BODY_START = 1.

OBJPACK-BODY_NUM = TAB_LINES.

OBJPACK-DOC_TYPE = 'RAW'.

APPEND OBJPACK.

Creation of the document attachment

LOOP AT ITAB_DATA.

CONCATENATE ITAB_DATA-PRODUCTOR

ITAB_DATA-VBELN

ITAB_DATA-POSNR

ITAB_DATA-MATNR INTO OBJBIN.

APPEND OBJBIN.

ENDLOOP.

DESCRIBE TABLE OBJBIN LINES TAB_LINES.

OBJHEAD = 'ORDERS'.

APPEND OBJHEAD.

Creation of the entry for the compressed attachment

OBJPACK-TRANSF_BIN = 'X'.

OBJPACK-HEAD_START = 1.

OBJPACK-HEAD_NUM = 1.

OBJPACK-BODY_START = 1.

OBJPACK-BODY_NUM = TAB_LINES.

OBJPACK-DOC_TYPE = 'TXT'.

OBJPACK-OBJ_NAME = 'WEBSITE'.

OBJPACK-OBJ_DESCR = 'ORDERS.TXT'.

OBJPACK-DOC_SIZE = TAB_LINES * 255.

APPEND OBJPACK.

Completing the recipient list
target recipent

clear RECLIST.

RECLIST-RECEIVER = 'test@here.com'.

RECLIST-EXPRESS = 'X'.

RECLIST-REC_TYPE = 'U'.

APPEND RECLIST.

copy recipents

clear RECLIST.

RECLIST-RECEIVER = 'secondtest@here.com'.

RECLIST-EXPRESS = 'X'.

RECLIST-REC_TYPE = 'U'.

RECLIST-COPY = 'X'.

APPEND RECLIST.

Sending the document

CALL FUNCTION 'SO_NEW_DOCUMENT_ATT_SEND_API1'

EXPORTING

DOCUMENT_DATA = DOC_CHNG

TABLES

PACKING_LIST = OBJPACK

OBJECT_HEADER = OBJHEAD

CONTENTS_BIN = OBJBIN

CONTENTS_TXT = OBJTXT

RECEIVERS = RECLIST

EXCEPTIONS

TOO_MANY_RECEIVERS = 1

DOCUMENT_NOT_SENT = 2

OPERATION_NO_AUTHORIZATION = 4

OTHERS = 99.

ENDFORM. " SEND_MAIL .

<b>*Reward points</b>

Regards

Re: Problem with EXCEL format while sending a mail with excel attachment

Former Member — Mon, 11 Jun 2007 06:42:47 GMT

Hi,

Please look into the code below.

this code is for attaching the sending the data as an excel attachment trhu a mail.

FORM send_as_mail_attachment .

LOCAL DATA DECLARATION

DATA: l_cnt TYPE i,

l_mail_id TYPE sadrud,

lc_format TYPE so_obj_tp,

lc_objname TYPE sodocchgi1-obj_name .

CONSTANTS: lc_sensitivty TYPE sodocchgi1-sensitivty VALUE '0',

lc_express TYPE somlreci1-express VALUE 'X',

lc_rec_type TYPE somlreci1-rec_type VALUE 'U',

lc_com_type TYPE somlreci1-com_type VALUE 'INT',

lc_obj_name TYPE sopcklsti1-obj_name VALUE 'ATTACHMENT',

lc_obj_descr TYPE sopcklsti1-obj_descr VALUE 'ATTACHMENT',

lc_raw(3) TYPE c VALUE 'RAW',

lc_00 VALUE '0',

lc_01 VALUE '1',

lc_02 VALUE '2',

lc_03 VALUE '3',

lc_04 VALUE '4',

lc_07 VALUE '7',

lc_06 VALUE '6',

lc_08 VALUE '8'.

CLEAR WORK AREAS

CLEAR : wa_doc_data,

wa_packing_list,

wa_receivers.

REFRESH INTERNAL TABLES

REFRESH : i_packing_list.

Fill the document data

lc_objname = text-002.

Populate the subject/generic message attributes - DOCUMENT DATA

wa_doc_data-obj_langu = sy-langu.

wa_doc_data-obj_name = lc_objname.

wa_doc_data-obj_descr = text-004 .

wa_doc_data-sensitivty = lc_sensitivty .

wa_doc_data-no_change = c_x .

wa_doc_data-priority = lc_01.

wa_doc_data-obj_prio = lc_01.

Get size of attachment

DESCRIBE TABLE i_objtxt LINES l_cnt.

READ TABLE i_objtxt INTO wa_objtxt INDEX l_cnt.

wa_doc_data-doc_size = ( l_cnt - 1 ) * 255 + STRLEN( wa_objtxt ).

CLEAR wa_packing_list-transf_bin.

DESCRIBE TABLE i_objtxt LINES wa_packing_list-body_num.

wa_packing_list-head_start = lc_01.

wa_packing_list-head_num = lc_01.

wa_packing_list-body_start = lc_01.

wa_packing_list-body_num = lc_07.

wa_packing_list-doc_type = lc_raw.

APPEND wa_packing_list TO i_packing_list.

DESCRIBE TABLE i_objtxt LINES l_cnt.

wa_packing_list-transf_bin = ' '.

wa_packing_list-head_start = lc_00.

wa_packing_list-head_num = lc_00.

wa_packing_list-body_start = lc_08.

wa_packing_list-body_num = l_cnt.

wa_packing_list-doc_type = c_xls.

wa_packing_list-obj_name = 'Attachment'.

wa_packing_list-obj_descr = text-004 .

wa_packing_list-doc_size = l_cnt * 255.

APPEND wa_packing_list TO i_packing_list.

LOOP AT i_receivers INTO wa_receivers.

IF wa_receivers-receiver IS INITIAL.

DELETE i_receivers INDEX sy-tabix.

ENDIF.

ENDLOOP.

CALL FUNCTION 'SO_NEW_DOCUMENT_ATT_SEND_API1'

EXPORTING

document_data = wa_doc_data

put_in_outbox = c_x

commit_work = c_x

IMPORTING

sent_to_all = w_sent_to_all

TABLES

packing_list = i_packing_list

contents_txt = i_objtxt

receivers = i_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.

CASE sy-subrc.

WHEN 0.

WHEN 1. RAISE too_many_receivers.

WHEN 2. RAISE document_not_sent .

WHEN 3. RAISE document_type_not_exist.

WHEN 4. RAISE operation_no_authorization.

WHEN 5. RAISE parameter_error.

WHEN 7. RAISE enqueue_error .

WHEN OTHERS. RAISE x_error.

ENDCASE.

ENDFORM. " send_as_mail_attachment

while fiiling the table contents_txt, at the end of each record,

add wa_objtxt-line+254(1) = c_newline, whre c_newline TYPE c VALUE cl_abap_char_utilities=>newline.

Regards,

Sree.

PS: Rewards points if Useful.

Re: Problem with EXCEL format while sending a mail with excel attachment

Former Member — Mon, 11 Jun 2007 07:21:35 GMT

Hi,

The last line u have written looks like is the solution to my problem but it looks like it's not there in 4.6c. Any other solution.......? It doesn't work for me....

Re: Problem with EXCEL format while sending a mail with excel attachment

Former Member — Mon, 11 Jun 2007 07:25:41 GMT

Are you moving an internal table as an excel format????

Regards,

Sriram.

Re: Problem with EXCEL format while sending a mail with excel attachment

Former Member — Mon, 11 Jun 2007 07:26:26 GMT

Yes

Re: Problem with EXCEL format while sending a mail with excel attachment

Former Member — Mon, 11 Jun 2007 07:37:31 GMT

Hi,

Just check out this..

TYPES: BEGIN OF t_ekpo,

ebeln TYPE ekpo-ebeln,

ebelp TYPE ekpo-ebelp,

aedat TYPE ekpo-aedat,

matnr TYPE ekpo-matnr,

END OF t_ekpo.

DATA: it_ekpo TYPE STANDARD TABLE OF t_ekpo INITIAL SIZE 0,

wa_ekpo TYPE t_ekpo.

TYPES: BEGIN OF t_charekpo,

ebeln(10) TYPE c,

ebelp(5) TYPE c,

aedat(8) TYPE c,

matnr(18) TYPE c,

END OF t_charekpo.

DATA: wa_charekpo TYPE t_charekpo.

DATA: it_message TYPE STANDARD TABLE OF solisti1 INITIAL SIZE 0

WITH HEADER LINE.

DATA: it_attach TYPE STANDARD TABLE OF solisti1 INITIAL SIZE 0

WITH HEADER LINE.

DATA: t_packing_list LIKE sopcklsti1 OCCURS 0 WITH HEADER LINE,

t_contents LIKE solisti1 OCCURS 0 WITH HEADER LINE,

t_receivers LIKE somlreci1 OCCURS 0 WITH HEADER LINE,

t_attachment LIKE solisti1 OCCURS 0 WITH HEADER LINE,

t_object_header LIKE solisti1 OCCURS 0 WITH HEADER LINE,

w_cnt TYPE i,

w_sent_all(1) TYPE c,

w_doc_data LIKE sodocchgi1,

gd_error TYPE sy-subrc,

gd_reciever TYPE sy-subrc.

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

*START_OF_SELECTION

START-OF-SELECTION.

Retrieve sample data from table ekpo

PERFORM data_retrieval.

Populate table with detaisl to be entered into .xls file

PERFORM build_xls_data_table.

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

*END-OF-SELECTION

END-OF-SELECTION.

Populate message body text

perform populate_email_message_body.

Send file by email as .xls speadsheet

PERFORM send_file_as_email_attachment

tables it_message

it_attach

using p_email

'Example .xls documnet attachment'

'XLS'

'filename'

' '

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.

SELECT ebeln ebelp aedat matnr

UP TO 10 ROWS

FROM ekpo

INTO TABLE it_ekpo.

ENDFORM. " DATA_RETRIEVAL

&----

*& Form BUILD_XLS_DATA_TABLE

&----

Build data table for .xls document

----

FORM build_xls_data_table.

CONSTANTS: con_cret TYPE x VALUE '0D', "OK for non Unicode
con_tab TYPE x 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.

CONCATENATE 'EBELN' 'EBELP' 'AEDAT' 'MATNR'

INTO it_attach SEPARATED BY space.

APPEND it_attach.

LOOP AT it_ekpo INTO wa_charekpo.

CONCATENATE wa_charekpo-ebeln wa_charekpo-ebelp

wa_charekpo-aedat wa_charekpo-matnr

INTO it_attach SEPARATED BY space.

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.

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.

&----

*& 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 = 'This mail is scheduled automatically and excel file is

attached for your reference'.

APPEND it_message.

endform. " POPULATE_EMAIL_MESSAGE_BODY

Regards,

Sriram.

PS: Reward Points if it is useful.