01-27-2008 4:30 AM
I have a requirement to send a formmated excel file (excel with header in bold) as a attachment in mail. I am able to send a plain excel with no frills but not with the header in bold. Can somebody please tell me if ABAP allows this and how?
01-27-2008 7:01 AM
You can do it from abap
You have two or three ways:
1. you can create a macro that formats the sheet in the way you
need, then download it as as a text file and after that, using ole2, you
can import this macro and execute it.
2. you can use SAP DOI to control the behaviour of the excel sheet
from your program. This way requires that you create a custom screen
with a container and that you use some of the standard interfaces
provided by sap (search in se24 for cl*
3. you can ask send your data into a tab delimited file, run excel
with this file as a parameter and ask your user to format it the way
he/she wants. Then the user can send the e-mail from excel (not the best
solution, but ...)
As I know you'll ask,
1. -> you can find examples in this group and in the net of how to
import a macro and execute it controlling excel with ole2. What we've
done to solve this issue is the following:
We have a custom FM that accepts the data table, the titles table and
some other parameters that reference the place where the FM was called
(repid, level, and other stuff). Our FM downloads the tables (spliting
it into several files if the table size is larger than 65000 rows, valid
for excel 2003 and less). After that, the FM retrieves all the macro
associated tho the reference (repid, level, and ...), which can be zero,
one or more. If there are more than 1, they are downloaded as separated
files. We have an specific macro that is always used (read_data) which
opens the data table and title table, merges them and imports them into
an excel instance all this stuff controlled by OLE2.
Once we have downloaded everything, we use OLE2 to control the behaviour
of excel, importing the macros, executing the macros that are selected
to be executed (this is a flag in our ddic tables). All this stuff is
done in a single step. It allows us for example to download data, create
a dynamic table, one or several graphs, show data in different orders,
... )
Search the net (using google or whatever you feel comfortable with ) for
call method of excel 'Modules' = module. Or something like this.
2.
-> I've learnt everything I've done from the list of examples
provided with sap, along with some tips found on the net and a lot of
research, trial and error. The examples in sap can be found using se38
/ se80 (in 4.6c) with the search pattern SAPEXCEL and/or SAPDOI
Cheers,
Satish.
01-27-2008 4:41 AM
HI Chakradhar,
You can do it from abap
You have two or three ways:
1. you can create a macro that formats the sheet in the way you
need, then download it as as a text file and after that, using ole2, you
can import this macro and execute it.
2. you can use SAP DOI to control the behaviour of the excel sheet
from your program. This way requires that you create a custom screen
with a container and that you use some of the standard interfaces
provided by sap (search in se24 for cl*
3. you can ask send your data into a tab delimited file, run excel
with this file as a parameter and ask your user to format it the way
he/she wants. Then the user can send the e-mail from excel (not the best
solution, but ...)
As I know you'll ask,
1. -> you can find examples in this group and in the net of how to
import a macro and execute it controlling excel with ole2. What we've
done to solve this issue is the following:
We have a custom FM that accepts the data table, the titles table and
some other parameters that reference the place where the FM was called
(repid, level, and other stuff). Our FM downloads the tables (spliting
it into several files if the table size is larger than 65000 rows, valid
for excel 2003 and less). After that, the FM retrieves all the macro
associated tho the reference (repid, level, and ...), which can be zero,
one or more. If there are more than 1, they are downloaded as separated
files. We have an specific macro that is always used (read_data) which
opens the data table and title table, merges them and imports them into
an excel instance all this stuff controlled by OLE2.
Once we have downloaded everything, we use OLE2 to control the behaviour
of excel, importing the macros, executing the macros that are selected
to be executed (this is a flag in our ddic tables). All this stuff is
done in a single step. It allows us for example to download data, create
a dynamic table, one or several graphs, show data in different orders,
... )
Search the net (using google or whatever you feel comfortable with ) for
call method of excel 'Modules' = module. Or something like this.
2.
-> I've learnt everything I've done from the list of examples
provided with sap, along with some tips found on the net and a lot of
research, trial and error. The examples in sap can be found using se38
/ se80 (in 4.6c) with the search pattern SAPEXCEL and/or SAPDOI
Regards,
Chandru
01-27-2008 4:43 AM
01-27-2008 4:45 AM
Hi Chakradhar, this is the code to send excel sheet as an attatchment to a mail.
parameters: p_email type somlreci1-receiver
default 'sense@gmail.com'.
types: begin of sol,
text(35),
end of sol.
data: begin of it001 occurs 0,
bukrs type t001-bukrs,
butxt type t001-butxt,
end of it001.
data: imessage type standard table of solisti1 with header line,
iattach type standard table of sol with header line,
ipacking_list like sopcklsti1 occurs 0 with header line,
ireceivers like somlreci1 occurs 0 with header line,
iattachment like solisti1 occurs 0 with header line.
start-of-selection.
select bukrs butxt into table it001 from t001.
Populate table with details to be entered into .xls file
perform build_xls_data .
Populate message body text
clear imessage.
refresh imessage.
imessage = 'Please find attached excel file'.
append imessage.
Send file by email as .xls spreadsheet
perform send_email_with_xls tables imessage
iattach
using p_email
'Example Excel Attachment'
'XLS'
'TestFileName'
'CompanyCodes'.
if sy-subrc = 0.
message i000(zsai).
endif.
************************************************************************
Form BUILD_XLS_DATA
************************************************************************
form build_xls_data .
constants: con_cret(2) type C value '0D', "OK for non Unicode
con_tab(2) type C value '09'. "OK for non Unicode
*If you have Unicode check active in program attributes then 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.
concatenate 'BUKRS' 'BUTXT'
into iattach separated by con_tab.
concatenate con_cret iattach into iattach.
append iattach.
loop at it001.
concatenate it001-bukrs it001-butxt
into iattach separated by con_tab.
concatenate con_cret iattach into iattach.
iattach = iattach(30).
append iattach.
endloop.
endform.
************************************************************************
Form SEND_EMAIL_WITH_XLS
************************************************************************
Send file by email as .xls spreadsheet
perform send_email_with_xls tables imessage
iattach
using p_email
'Example Excel Attachment'
'XLS'
'TestFileName'
'CompanyCodes'.
form send_email_with_xls tables pit_message
pit_attach
using p_email
p_mtitle
p_format
p_filename
p_attdescription.
data: xdocdata like sodocchgi1,
xcnt type i.
Fill the document data.
xdocdata-doc_size = 1.
Populate the subject/generic message attributes
xdocdata-obj_langu = sy-langu.
xdocdata-obj_name = 'SAPRPT'.
xdocdata-obj_descr = p_mtitle .
Fill the document data and get size of attachment
clear xdocdata.
read table iattach index xcnt.
xdocdata-doc_size =
( xcnt - 1 ) * 255 + strlen( iattach ).
xdocdata-obj_langu = sy-langu.
xdocdata-obj_name = 'SAPRPT'.
xdocdata-obj_descr = p_mtitle.
clear iattachment.
refresh iattachment.
iattachment] = pit_attach[.
Describe the body of the message
clear ipacking_list.
refresh ipacking_list.
ipacking_list-transf_bin = space.
ipacking_list-head_start = 1.
ipacking_list-head_num = 0.
ipacking_list-body_start = 1.
describe table imessage lines ipacking_list-body_num.
ipacking_list-doc_type = 'RAW'.
append ipacking_list.
Create attachment notification
ipacking_list-transf_bin = 'X'.
ipacking_list-head_start = 1.
ipacking_list-head_num = 1.
ipacking_list-body_start = 1.
describe table iattachment lines ipacking_list-body_num.
ipacking_list-doc_type = p_format.
ipacking_list-obj_descr = p_attdescription.
ipacking_list-obj_name = p_filename.
ipacking_list-doc_size = ipacking_list-body_num * 255.
append ipacking_list.
Add the recipients email address
clear ireceivers. refresh ireceivers.
ireceivers-receiver = p_email.
ireceivers-rec_type = 'U'.
ireceivers-com_type = 'INT'.
ireceivers-notif_del = 'X'.
ireceivers-notif_ndel = 'X'.
append ireceivers.
call function 'SO_DOCUMENT_SEND_API1'
exporting
document_data = xdocdata
put_in_outbox = 'X'
commit_work = 'X'
tables
packing_list = ipacking_list
contents_bin = iattachment
contents_txt = imessage
receivers = ireceivers
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.
IF SY-SUBRC = 0.
SUBMIT rsconn01 WITH mode EQ 'INT' AND RETURN.
COMMIT WORK .
ENDIF.
endform.
kindly reward if found helpful.
cheers.
Hema.
01-27-2008 4:47 AM
Hi,
Here is my code. Hope it helps.
&----
*& Report ZWBSAP_EMAIL *
*& *
&----
*& *
*& *
&----
*Send an email via SAP Workplace
*Select files from SAP server
*Note:
Uses custom table zlookup to pick up a group of email addresses to send to
if more than one is needed.
Please note some hard coding.
REPORT zsap_email MESSAGE-ID z001.
TABLES: rlgrap, btcxpm, zlookup.
DATA:
v_zcode(3) TYPE c,
svrname LIKE zlookup-zdesc,
wa_error(80) TYPE c,
global_filemask_all(80).
DATA: t_soud LIKE soud.
DATA: p_infolder LIKE sofdk.
DATA: p_outfolder LIKE sofdk.
DATA: object_hd_display LIKE sood2.
DATA: object_id LIKE soodk.
DATA: document LIKE sood4.
DATA: header_data LIKE sood2.
DATA: link_folder_id LIKE soodk.
DATA: folder_selections LIKE sofds.
DATA: folder_list LIKE soxli OCCURS 0 WITH HEADER LINE.
DATA: receivers LIKE soos1 OCCURS 0 WITH HEADER LINE.
DATA: objcont LIKE soli OCCURS 0 WITH HEADER LINE.
DATA: objhead LIKE soli OCCURS 0 WITH HEADER LINE.
DATA: object_content LIKE solisti1 OCCURS 0 WITH HEADER LINE.
DATA: object_hd_change LIKE sood1.
DATA: BEGIN OF files OCCURS 0,
line(200) TYPE c,
END OF files.
DATA: listobject LIKE abaplist OCCURS 1 WITH HEADER LINE.
*Email to addresses
SELECTION-SCREEN: BEGIN OF BLOCK bl1 WITH FRAME TITLE text-bl1.
PARAMETERS: p_mailto(240) TYPE c.
SELECTION-SCREEN SKIP 1.
SELECTION-SCREEN COMMENT 10(2) text-001.
*Use zlookup-ztype as group to define multiple email to addresses
SELECT-OPTIONS: s_mailto FOR zlookup-ztype.
SELECTION-SCREEN: END OF BLOCK bl1.
*Subject
SELECTION-SCREEN: BEGIN OF BLOCK bl2 WITH FRAME TITLE text-bl2.
PARAMETERS: p_subj LIKE document-objdes.
SELECTION-SCREEN: END OF BLOCK bl2.
*Body
SELECTION-SCREEN: BEGIN OF BLOCK bl3 WITH FRAME TITLE text-bl3.
PARAMETERS: p_body1 LIKE solisti1-line LOWER CASE.
PARAMETERS: p_body2 LIKE solisti1-line LOWER CASE.
PARAMETERS: p_body3 LIKE solisti1-line LOWER CASE.
PARAMETERS: p_body4 LIKE solisti1-line LOWER CASE.
PARAMETERS: p_body5 LIKE solisti1-line LOWER CASE.
PARAMETERS: p_body6 LIKE solisti1-line LOWER CASE.
SELECTION-SCREEN: END OF BLOCK bl3.
*Attachments
SELECTION-SCREEN: BEGIN OF BLOCK bl4 WITH FRAME TITLE text-bl4.
PARAMETERS: p_fname1 LIKE files-line.
PARAMETERS: p_fname2 LIKE files-line.
PARAMETERS: p_fname3 LIKE files-line.
PARAMETERS: p_fname4 LIKE files-line.
PARAMETERS: p_fname5 LIKE files-line.
PARAMETERS: p_fname6 LIKE files-line.
SELECTION-SCREEN: END OF BLOCK bl4.
AT SELECTION-SCREEN.
PERFORM chk_selection.
IF NOT wa_error IS INITIAL.
MESSAGE e001 WITH wa_error.
ENDIF.
START-OF-SELECTION.
END-OF-SELECTION.
PERFORM send_email.
----
FORM chk_selection *
----
........ *
----
FORM chk_selection.
CLEAR wa_error.
*Loop only once ???
*If more checkings to be done.
*Think about it and if not clear.
*Let it sink in for a couple of minutes.
*Still doesn't make sence? Go home and sleep on it.
DO 1 TIMES.
*Check Email to
IF p_mailto IS INITIAL
AND s_mailto IS INITIAL.
wa_error = 'Invalid or no EMAIL TO selection'.
EXIT.
ENDIF.
ENDDO.
ENDFORM.
&----
*& Form send_email
&----
text
----
--> p1 text
<-- p2 text
----
FORM send_email.
PERFORM get_folder_info.
PERFORM fill_body.
PERFORM ins_new_object.
PERFORM crt_attachments.
PERFORM fill_receivers_info.
PERFORM snd_email.
ENDFORM. " send_email
----
FORM Get_folder_info *
----
........ *
----
FORM get_folder_info.
CLEAR t_soud.
*Small so should be quick.
SELECT * FROM soud
INTO t_soud
WHERE sapnam = sy-uname.
p_infolder-foltp = 'FOL'.
p_infolder-folyr = t_soud-inbyr. "inbox
p_infolder-folno = t_soud-inbno.
p_outfolder-foltp = 'FOL'.
p_outfolder-folyr = t_soud-outyr. "outbox
p_outfolder-folno = t_soud-outno.
ENDSELECT.
ENDFORM.
----
FORM fill_body *
----
........ *
----
FORM fill_body.
object_content-line = p_body1.
APPEND object_content.
object_content-line = p_body2.
APPEND object_content.
object_content-line = p_body3.
APPEND object_content.
object_content-line = p_body4.
APPEND object_content.
object_content-line = p_body5.
APPEND object_content.
object_content-line = p_body6.
APPEND object_content.
ENDFORM.
----
FORM ins_new_object *
----
........ *
----
FORM ins_new_object.
object_hd_change-objnam = 'Notes'.
object_hd_change-objdes = p_subj.
CALL FUNCTION 'SO_OBJECT_INSERT'
EXPORTING
folder_id = p_outfolder
object_hd_change = object_hd_change
object_type = 'RAW'
owner = sy-uname
IMPORTING
object_hd_display = object_hd_display
object_id = object_id
TABLES
objcont = object_content
objhead = objhead
EXCEPTIONS
active_user_not_exist = 1
communication_failure = 2
component_not_available = 3
dl_name_exist = 4
folder_not_exist = 5
folder_no_authorization = 6
object_type_not_exist = 7
operation_no_authorization = 8
owner_not_exist = 9
parameter_error = 10
substitute_not_active = 11
substitute_not_defined = 12
system_failure = 13
x_error = 14
OTHERS = 15.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
ENDFORM.
----
FORM crt_attachments *
----
........ *
----
--> P_SUBJ *
----
FORM crt_attachments.
document-foltp = p_outfolder-foltp.
document-folyr = p_outfolder-folyr.
document-folno = p_outfolder-folno.
document-objtp = object_id-objtp.
document-objyr = object_id-objyr.
document-objno = object_id-objno.
document-objnam = 'Notes'.
document-objdes = p_subj.
document-okcode = 'CHNG'.
document-file_ext = 'TXT'.
link_folder_id = object_id.
header_data-objla = 'EN'.
header_data-objnam = document-objnam.
header_data-objdes = document-objdes.
header_data-objpri = '5'.
header_data-objsns = 'O'.
header_data-file_ext = 'TXT'.
REFRESH files.
CLEAR files.
IF NOT p_fname1 IS INITIAL.
files-line = p_fname1.
APPEND files.
ENDIF.
IF NOT p_fname2 IS INITIAL.
files-line = p_fname2.
APPEND files.
ENDIF.
IF NOT p_fname3 IS INITIAL.
files-line = p_fname3.
APPEND files.
ENDIF.
IF NOT p_fname4 IS INITIAL.
files-line = p_fname4.
APPEND files.
ENDIF.
IF NOT p_fname5 IS INITIAL.
files-line = p_fname5.
APPEND files.
ENDIF.
IF NOT p_fname6 IS INITIAL.
files-line = p_fname6.
APPEND files.
ENDIF.
IF NOT files[] IS INITIAL.
CALL FUNCTION 'SO_DOCUMENT_REPOSITORY_MANAGER'
EXPORTING
method = 'ATTCREATEFROMPC'
TABLES
files = files
CHANGING
document = document
header_data = header_data.
IF sy-subrc = 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
ENDIF.
ENDFORM.
----
FORM fill_receivers_info *
----
........ *
----
FORM fill_receivers_info.
DATA: BEGIN OF reclist OCCURS 0,
rec LIKE receivers-recextnam,
END OF reclist.
*Build reclist
REFRESH reclist.
IF NOT p_mailto IS INITIAL.
reclist-rec = p_mailto.
APPEND reclist.
ENDIF.
IF NOT s_mailto IS INITIAL.
SELECT zdesc
INTO reclist-rec
FROM zlookup
WHERE ztype IN s_mailto.
APPEND reclist.
ENDSELECT.
ENDIF.
REFRESH receivers.
LOOP AT reclist.
folder_selections-folnam = 'INBOX'.
CALL FUNCTION 'SO_FOLDER_LIST_READ'
EXPORTING
folder_selections = folder_selections
owner = sy-uname
region = 'P'
TABLES
folder_list = folder_list
EXCEPTIONS
component_not_available = 1
operation_no_authorization = 2
owner_not_exist = 3
parameter_error = 4
x_error = 5
OTHERS = 6.
IF sy-subrc = 0.
CLEAR receivers.
receivers-rcdat = sy-datum.
receivers-rctim = sy-uzeit.
receivers-recnam = reclist-rec.
receivers-recno = folder_list-parno.
receivers-rectp = folder_list-partp.
receivers-recyr = folder_list-paryr.
receivers-sndtp = folder_list-partp.
receivers-sndyr = folder_list-paryr.
receivers-sndno = folder_list-parno.
receivers-sndnam = sy-uname.
receivers-sndpri = '1'.
receivers-msgid = 'SO'.
receivers-msgno = '619'.
receivers-deliver = 'X'.
receivers-not_deli = 'X'.
receivers-read = 'X'.
receivers-mailstatus = 'E'.
*receivers-ADR_NAME
receivers-resend = ' '.
receivers-sortfield = ' '. "'USER WF_BATCH INDIGO BATCH'.
receivers-sortclass = '5'.
APPEND receivers.
ELSE.
CLEAR receivers.
receivers-sel = 'X'.
receivers-recesc = 'U'.
receivers-recnam = 'U-'.
receivers-recextnam = reclist-rec.
receivers-deliver = 'X'.
receivers-not_deli = 'X'.
receivers-read = 'X'.
receivers-mailstatus = 'E'.
receivers-adr_name = reclist-rec.
receivers-sortfield = reclist-rec.
receivers-sortclass = '5'.
APPEND receivers.
ENDIF.
ENDLOOP.
ENDFORM.
----
FORM snd_email *
----
........ *
----
FORM snd_email.
CALL FUNCTION 'SO_OBJECT_SEND'
EXPORTING
folder_id = p_outfolder
object_id = object_id
outbox_flag = 'X'
owner = sy-uname
check_send_authority = 'X'
originator_type = 'J'
link_folder_id = link_folder_id
TABLES
objcont = object_content
receivers = receivers
EXCEPTIONS
active_user_not_exist = 1
communication_failure = 2
component_not_available = 3
folder_not_exist = 4
folder_no_authorization = 5
forwarder_not_exist = 6
note_not_exist = 7
object_not_exist = 8
object_not_sent = 9
object_no_authorization = 10
object_type_not_exist = 11
operation_no_authorization = 12
owner_not_exist = 13
parameter_error = 14
substitute_not_active = 15
substitute_not_defined = 16
system_failure = 17
too_much_receivers = 18
user_not_exist = 19
originator_not_exist = 20
x_error = 21
OTHERS = 22.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
ENDFORM.
Regards,
Chandru
01-27-2008 7:01 AM
You can do it from abap
You have two or three ways:
1. you can create a macro that formats the sheet in the way you
need, then download it as as a text file and after that, using ole2, you
can import this macro and execute it.
2. you can use SAP DOI to control the behaviour of the excel sheet
from your program. This way requires that you create a custom screen
with a container and that you use some of the standard interfaces
provided by sap (search in se24 for cl*
3. you can ask send your data into a tab delimited file, run excel
with this file as a parameter and ask your user to format it the way
he/she wants. Then the user can send the e-mail from excel (not the best
solution, but ...)
As I know you'll ask,
1. -> you can find examples in this group and in the net of how to
import a macro and execute it controlling excel with ole2. What we've
done to solve this issue is the following:
We have a custom FM that accepts the data table, the titles table and
some other parameters that reference the place where the FM was called
(repid, level, and other stuff). Our FM downloads the tables (spliting
it into several files if the table size is larger than 65000 rows, valid
for excel 2003 and less). After that, the FM retrieves all the macro
associated tho the reference (repid, level, and ...), which can be zero,
one or more. If there are more than 1, they are downloaded as separated
files. We have an specific macro that is always used (read_data) which
opens the data table and title table, merges them and imports them into
an excel instance all this stuff controlled by OLE2.
Once we have downloaded everything, we use OLE2 to control the behaviour
of excel, importing the macros, executing the macros that are selected
to be executed (this is a flag in our ddic tables). All this stuff is
done in a single step. It allows us for example to download data, create
a dynamic table, one or several graphs, show data in different orders,
... )
Search the net (using google or whatever you feel comfortable with ) for
call method of excel 'Modules' = module. Or something like this.
2.
-> I've learnt everything I've done from the list of examples
provided with sap, along with some tips found on the net and a lot of
research, trial and error. The examples in sap can be found using se38
/ se80 (in 4.6c) with the search pattern SAPEXCEL and/or SAPDOI
Cheers,
Satish.
01-30-2008 12:30 PM
Hi.....
I've have a requirement where i'm suppose to send the sap output through mail in the excel format......I've got it done and its working fine.......but the new requirement is i need to get a pie chart of from some choosen columns in the excel sheet and get a pie chart of this in the second sheet....all this should also be mailed and the receipent should have two sheets with the sap output in excel in one sheet(which i have it presently) and pie chart in the next sheet.........
I've managed to get the pie chart done with sap logic using Graph_matrix_2d FM....as a sap output but how can i send it through mail....that too in the second sheet......Has any body worked on this...........or else for your clue we can get this stuff done with Excel Macros......but i'm new to this...............More over as know we cant covert this graphic view in the excel to XML format......thats the main problem other wise we can write a XML code in XSLT program in Abap and get our requirement............I possible can we get this done in the same excel sheet.............how please let me know..............Its urgent...................