Hi
I did not test, but I've changed your GEL to parse the "body message" in a variable (in red color). if it works, adjusted the text body as needed
<gel:script xmlns:core="jelly:core" xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
xmlns:file="jelly:com.niku.union.gel.FileTagLibrary"
xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
xmlns:j="jelly:core"
xmlns:email="jelly:email"
xmlns:sql="jelly:sql" xmlns:util="jelly:util" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<!-- Wrap our script in a loop so we can use break to exit cleanly -->
<core:set var="scriptBreak" value="[1]" />
<core:forEach var="dummy" items="${scriptBreak}">
<core:set var="rowEmails" value="abc@xyz.com" />
<core:forEach items="${rowEmails}" trim="true" var="rowEmail">
<gel:out><core:expr value="${rowEmail}" /> </gel:out>
<core:set value="${rowEmail};${vRoleEmail}" var="vRoleEmail" />
</core:forEach>
<gel:out>
<core:expr value="${vRoleEmail}" />
</gel:out>
<gel:setDataSource dbId="Niku"/>
<!-- GET APPLICATION URL FROM PROPERTIES.XML FILE -->
<core:invokeStatic className="java.lang.System" method="getenv" var="nikuHome">
<core:arg value="NIKU_HOME"/>
</core:invokeStatic>
<gel:parse file="${nikuHome}/config/properties.xml" var="propertiesXML"/>
<core:set var="rowEmails" value="abc@xyz.com" />
<core:forEach items="${rowEmails}" trim="true" var="rowEmail">
</core:forEach>
<sql:query escapeText="0" var="result">
<![CDATA[
SELECT
Distinct RSM.MGR_NAME
, RSM.MGR_EMAIL
, RSM.MGR_ID
From (
SELECT Distinct SRMR.ID RESOURCE_ID
, SRMR.UNIQUE_NAME RESOURCE_CODE
, SRMR.FULL_NAME RESOURCE_NAME
, SRMR.EMAIL RESOURCE_EMAIL
, MGRRES.ID MGR_ID
, MGRRES.FULL_NAME MGR_NAME
, MGRRES.EMAIL MGR_EMAIL
, CASE SRMR.IS_ACTIVE WHEN 1 THEN 'Yes' ELSE 'No' END IS_ACTIVE
, SRMR.IS_ACTIVE IS_ACTIVE_FLAG
, U.LAST_LOGGED_IN_DATE LAST_LOGIN_DATE
, SRMR.PERSON_TYPE PERSON_TYPE
, (select name from cmn_lookups_v where id = srmr.person_type and language_code = 'en' and lookup_type = 'SRM_RESOURCE_TYPE') EMPL_TYPE
--, CASE A.PRINCIPAL_TYPE WHEN 'USER' THEN 'USER' WHEN 'GROUP' THEN 'GROUP' ELSE 'OBS_UNIT' END PRINCIPAL_TYPE_ID
--, CASE A.PRINCIPAL_TYPE WHEN 'USER' THEN 'User' WHEN 'GROUP' THEN 'Group' ELSE 'OBS Unit' END PRINCIPAL_TYPE
, A.PRINCIPAL_NAME PRINCIPAL_NAME
, R.ID RIGHT_ID
, R.GROUP_CODE RIGHT_CODE
, RN.NAME RIGHT_NAME
, R.LIC_RIGHT_TYPE LIC_RIGHT_TYPE_ID
, RT.NAME LIC_RIGHT_TYPE
, NVL(R.IS_AUTOMATIC, 0) IS_AUTOMATIC
FROM SRM_RESOURCES SRMR
INNER JOIN PRJ_RESOURCES PRJR ON SRMR.ID = PRJR.PRID AND PRJR.PRISROLE = 0
LEFT JOIN SRM_RESOURCES MGRRES ON MGRRES.USER_ID = SRMR.MANAGER_ID
INNER JOIN CMN_SEC_USERS U ON U.ID = SRMR.USER_ID
INNER JOIN (SELECT SRMR.USER_ID, SRMR.USER_ID PRINCIPAL_ID, 'USER' PRINCIPAL_TYPE, 'USER' PRINCIPAL_NAME
FROM SRM_RESOURCES SRMR
WHERE SRMR.USER_ID IS NOT NULL
UNION ALL
SELECT UG.USER_ID, UG.GROUP_ID PRINCIPAL_ID, 'GROUP' PRINCIPAL_TYPE, GN.NAME PRINCIPAL_NAME
FROM CMN_SEC_USER_GROUPS UG
INNER JOIN CMN_SEC_GROUPS G ON UG.GROUP_ID = G.ID AND G.GROUP_ROLE_TYPE = 'GROUP'
INNER JOIN CMN_CAPTIONS_NLS GN ON G.ID = GN.PK_ID AND GN.TABLE_NAME = 'CMN_SEC_GROUPS' AND GN.LANGUAGE_CODE = 'en'
WHERE G.IS_ACTIVE = 1
UNION ALL
SELECT DISTINCT SRMR.USER_ID, OBSU.ID PRINCIPAL_ID, OBSM.UNIT_MODE PRINCIPAL_TYPE, OBST.NAME || ': ' || OBSU.NAME PRINCIPAL_NAME
FROM SRM_RESOURCES SRMR
INNER JOIN PRJ_OBS_ASSOCIATIONS OBSA ON SRMR.ID = RECORD_ID AND OBSA.TABLE_NAME = 'SRM_RESOURCES'
INNER JOIN PRJ_OBS_UNITS OBSU ON OBSA.UNIT_ID = OBSU.ID
INNER JOIN PRJ_OBS_TYPES OBST ON OBSU.TYPE_ID = OBST.ID AND OBST.IS_SECURITY = 1
INNER JOIN OBS_UNITS_FLAT_BY_MODE OBSM ON OBSA.UNIT_ID = OBSM.LINKED_UNIT_ID) A ON SRMR.USER_ID = A.USER_ID
INNER JOIN (-- USER GLOBAL AND OBS RIGHTS, GROUP OBS RIGHTS, OBS NODE GLOBAL, OBS, AND INSTANCE RIGHTS
SELECT AR.PRINCIPAL_ID, AR.PRINCIPAL_TYPE, AR.RIGHT_ID
, CASE AR.INSTANCE_TYPE WHEN 'SYSTEM' THEN 'Global Right' WHEN 'INSTANCE' THEN 'Instance Right' ELSE 'OBS Right' END RIGHT_TYPE
, '' rightType, NULL object_instance_id
, CASE WHEN AR.INSTANCE_TYPE NOT IN ('SYSTEM', 'INSTANCE') THEN OBST.NAME || ': ' || OBSU.NAME END INSTANCE_NAME
, OT.NAME INSTANCE_MODE
FROM CMN_SEC_ASSGND_RIGHT AR
LEFT JOIN PRJ_OBS_UNITS OBSU ON AR.INSTANCE_ID = OBSU.ID
LEFT JOIN PRJ_OBS_TYPES OBST ON OBSU.TYPE_ID = OBST.ID
LEFT JOIN CMN_LOOKUPS_V OT ON AR.INSTANCE_TYPE = OT.LOOKUP_CODE
AND OT.LOOKUP_TYPE = 'OBS_UNIT_ASSOCIATION' AND OT.LANGUAGE_CODE = 'en'
WHERE 1=1
UNION ALL
-- GROUP GLOBAL RIGHTS
SELECT GH.PARENT_GROUP_ID PRINCIPAL_ID, G.GROUP_ROLE_TYPE PRINCIPAL_TYPE, GH.GROUP_ID RIGHT_ID, 'Global Right' RIGHT_TYPE
, '' rightType, NULL object_instance_id, NULL INSTANCE_NAME, NULL INSTANCE_MODE
FROM CMN_SEC_GROUP_FLAT_HIERS GH
INNER JOIN CMN_SEC_GROUPS G ON GH.PARENT_GROUP_ID = G.ID AND G.GROUP_ROLE_TYPE = 'GROUP' AND G.IS_ACTIVE = 1
WHERE 1=1
UNION ALL
-- USER INSTANCE RIGHTS, GROUP INSTANCE_RIGHTS
SELECT DISTINCT OP.PRINCIPAL_ID, OP.PRINCIPAL_TYPE, OP.RIGHT_ID, 'Instance Right' RIGHT_TYPE,
grpv.right_type rightType, op.object_instance_id,
CASE
WHEN grpv.right_type IN ('SRM_PROJECTS','INV_IDEA') THEN (SELECT i.code FROM inv_investments i WHERE i.id = op.object_instance_id)
WHEN grpv.right_type = 'SRM_RESOURCES' THEN (SELECT r.unique_name FROM srm_resources r WHERE r.id = op.object_instance_id)
WHEN grpv.right_type = 'CMN_PORTLETS' THEN (SELECT cp.portlet_code FROM CMN_PORTLETS cp WHERE cp.id = op.object_instance_id)
WHEN grpv.right_type = 'CMN_SCH_JOB_DEFINITIONS' THEN (SELECT sjd.job_code FROM CMN_SCH_JOB_DEFINITIONS sjd WHERE sjd.id = op.object_instance_id)
WHEN grpv.right_type = 'CMN_PAGES' THEN (SELECT pg.page_code FROM CMN_PAGES pg WHERE pg.id = op.object_instance_id)
WHEN grpv.right_type = 'BPM_DEF_PROCESSES' THEN (SELECT dp.process_code from BPM_DEF_PROCESSES dp WHERE dp.id = op.object_instance_id)
WHEN grpv.right_type = 'cop_prj_statusrpt' THEN (SELECT sr.code from odf_ca_cop_prj_statusrpt sr WHERE sr.id = op.object_instance_id)
WHEN grpv.right_type = 'DPT_DEPARTMENT' THEN (SELECT description from DEPARTMENTS WHERE id = op.object_instance_id)
WHEN grpv.right_type = 'DPT_LOCATION' THEN (SELECT locationdescription from LOCATIONS WHERE id = op.object_instance_id)
END INSTANCE_NAME,
NULL INSTANCE_MODE
FROM CMN_SEC_ASSGND_OBJ_PERM OP
,cmn_sec_groups_v grpv
WHERE op.right_id = grpv.id
AND grpv.language_code = 'en'
AND grpv.group_name NOT LIKE '%(Auto)%') AR ON A.PRINCIPAL_ID = AR.PRINCIPAL_ID AND A.PRINCIPAL_TYPE = AR.PRINCIPAL_TYPE
INNER JOIN CMN_SEC_GROUPS R ON AR.RIGHT_ID = R.ID AND R.GROUP_ROLE_TYPE = 'ROLE'
INNER JOIN CMN_CAPTIONS_NLS RN ON R.ID = RN.PK_ID AND RN.TABLE_NAME = 'CMN_SEC_GROUPS' AND RN.LANGUAGE_CODE = 'en'
LEFT JOIN CMN_LOOKUPS_V RT ON R.LIC_RIGHT_TYPE = RT.LOOKUP_CODE AND RT.LOOKUP_TYPE = 'CMN_LIC_RIGHT_TYPE' AND RT.LANGUAGE_CODE = 'en'
where SRMR.IS_ACTIVE = 1
and SRMR.UNIQUE_NAME != 'admin' and SRMR.UNIQUE_NAME != 'xc_admin'
and R.GROUP_CODE = 'ResourceApproveTime'
GROUP BY
SRMR.ID
, SRMR.UNIQUE_NAME
, SRMR.FULL_NAME
, SRMR.EMAIL
, MGRRES.FULL_NAME
, MGRRES.EMAIL
, SRMR.IS_ACTIVE
, U.LAST_LOGGED_IN_DATE
, SRMR.PERSON_TYPE
, A.PRINCIPAL_NAME
, R.ID
, R.GROUP_CODE
, RN.NAME
, R.LIC_RIGHT_TYPE
, RT.NAME
, R.IS_AUTOMATIC
, MGRRES.ID
) RSM
]]>
</sql:query>
<core:forEach items="${result.rowsByIndex}" trim="true" var="row">
<core:set value="${row[0]}" var="rmgrName"/>
<core:set value="${row[1]}" var="rmgrEmail"/>
<core:set value="${row[2]}" var="rmgrId"/>
<core:set var="message">
<![CDATA[
This is a test. Please ignore.
]]>
</core:set>
<gel:log trim="false"> Resource: ${rmgrName} Actual Email Recipients: "xyz@test.com"</gel:log>
<email:email server="localhost" from="test@test.com" CC="${rowEmails}" subject="Test - Please Ignore" to="xyz@test.com" message="${message}"/>
<core:catch var="g_exception">
<sql:query escapeText="0" var="result2">
<![CDATA[
SELECT
SQ.MGR_NAME
,SQ.MGR_EMAIL
,SQ.RESOURCE_NAME
,SQ.RESOURCE_EMAIL
FROM (
SELECT Distinct SRMR.ID RESOURCE_ID
, SRMR.UNIQUE_NAME RESOURCE_CODE
, SRMR.FULL_NAME RESOURCE_NAME
, SRMR.EMAIL RESOURCE_EMAIL
, MGRRES.ID mgr_ID
, MGRRES.FULL_NAME MGR_NAME
, MGRRES.EMAIL MGR_EMAIL
, CASE SRMR.IS_ACTIVE WHEN 1 THEN 'Yes' ELSE 'No' END IS_ACTIVE
, SRMR.IS_ACTIVE IS_ACTIVE_FLAG
, U.LAST_LOGGED_IN_DATE LAST_LOGIN_DATE
, SRMR.PERSON_TYPE PERSON_TYPE
, (select name from cmn_lookups_v where id = srmr.person_type and language_code = 'en' and lookup_type = 'SRM_RESOURCE_TYPE') EMPL_TYPE
--, CASE A.PRINCIPAL_TYPE WHEN 'USER' THEN 'USER' WHEN 'GROUP' THEN 'GROUP' ELSE 'OBS_UNIT' END PRINCIPAL_TYPE_ID
--, CASE A.PRINCIPAL_TYPE WHEN 'USER' THEN 'User' WHEN 'GROUP' THEN 'Group' ELSE 'OBS Unit' END PRINCIPAL_TYPE
, A.PRINCIPAL_NAME PRINCIPAL_NAME
, R.ID RIGHT_ID
, R.GROUP_CODE RIGHT_CODE
, RN.NAME RIGHT_NAME
, R.LIC_RIGHT_TYPE LIC_RIGHT_TYPE_ID
, RT.NAME LIC_RIGHT_TYPE
, NVL(R.IS_AUTOMATIC, 0) IS_AUTOMATIC
FROM SRM_RESOURCES SRMR
INNER JOIN PRJ_RESOURCES PRJR ON SRMR.ID = PRJR.PRID AND PRJR.PRISROLE = 0
LEFT JOIN SRM_RESOURCES MGRRES ON MGRRES.USER_ID = SRMR.MANAGER_ID
INNER JOIN CMN_SEC_USERS U ON U.ID = SRMR.USER_ID
INNER JOIN (SELECT SRMR.USER_ID, SRMR.USER_ID PRINCIPAL_ID, 'USER' PRINCIPAL_TYPE, 'USER' PRINCIPAL_NAME
FROM SRM_RESOURCES SRMR
WHERE SRMR.USER_ID IS NOT NULL
UNION ALL
SELECT UG.USER_ID, UG.GROUP_ID PRINCIPAL_ID, 'GROUP' PRINCIPAL_TYPE, GN.NAME PRINCIPAL_NAME
FROM CMN_SEC_USER_GROUPS UG
INNER JOIN CMN_SEC_GROUPS G ON UG.GROUP_ID = G.ID AND G.GROUP_ROLE_TYPE = 'GROUP'
INNER JOIN CMN_CAPTIONS_NLS GN ON G.ID = GN.PK_ID AND GN.TABLE_NAME = 'CMN_SEC_GROUPS' AND GN.LANGUAGE_CODE = 'en'
WHERE G.IS_ACTIVE = 1
UNION ALL
SELECT DISTINCT SRMR.USER_ID, OBSU.ID PRINCIPAL_ID, OBSM.UNIT_MODE PRINCIPAL_TYPE, OBST.NAME || ': ' || OBSU.NAME PRINCIPAL_NAME
FROM SRM_RESOURCES SRMR
INNER JOIN PRJ_OBS_ASSOCIATIONS OBSA ON SRMR.ID = RECORD_ID AND OBSA.TABLE_NAME = 'SRM_RESOURCES'
INNER JOIN PRJ_OBS_UNITS OBSU ON OBSA.UNIT_ID = OBSU.ID
INNER JOIN PRJ_OBS_TYPES OBST ON OBSU.TYPE_ID = OBST.ID AND OBST.IS_SECURITY = 1
INNER JOIN OBS_UNITS_FLAT_BY_MODE OBSM ON OBSA.UNIT_ID = OBSM.LINKED_UNIT_ID) A ON SRMR.USER_ID = A.USER_ID
INNER JOIN (-- USER GLOBAL AND OBS RIGHTS, GROUP OBS RIGHTS, OBS NODE GLOBAL, OBS, AND INSTANCE RIGHTS
SELECT AR.PRINCIPAL_ID, AR.PRINCIPAL_TYPE, AR.RIGHT_ID
, CASE AR.INSTANCE_TYPE WHEN 'SYSTEM' THEN 'Global Right' WHEN 'INSTANCE' THEN 'Instance Right' ELSE 'OBS Right' END RIGHT_TYPE
, '' rightType, NULL object_instance_id
, CASE WHEN AR.INSTANCE_TYPE NOT IN ('SYSTEM', 'INSTANCE') THEN OBST.NAME || ': ' || OBSU.NAME END INSTANCE_NAME
, OT.NAME INSTANCE_MODE
FROM CMN_SEC_ASSGND_RIGHT AR
LEFT JOIN PRJ_OBS_UNITS OBSU ON AR.INSTANCE_ID = OBSU.ID
LEFT JOIN PRJ_OBS_TYPES OBST ON OBSU.TYPE_ID = OBST.ID
LEFT JOIN CMN_LOOKUPS_V OT ON AR.INSTANCE_TYPE = OT.LOOKUP_CODE
AND OT.LOOKUP_TYPE = 'OBS_UNIT_ASSOCIATION' AND OT.LANGUAGE_CODE = 'en'
WHERE 1=1
UNION ALL
-- GROUP GLOBAL RIGHTS
SELECT GH.PARENT_GROUP_ID PRINCIPAL_ID, G.GROUP_ROLE_TYPE PRINCIPAL_TYPE, GH.GROUP_ID RIGHT_ID, 'Global Right' RIGHT_TYPE
, '' rightType, NULL object_instance_id, NULL INSTANCE_NAME, NULL INSTANCE_MODE
FROM CMN_SEC_GROUP_FLAT_HIERS GH
INNER JOIN CMN_SEC_GROUPS G ON GH.PARENT_GROUP_ID = G.ID AND G.GROUP_ROLE_TYPE = 'GROUP' AND G.IS_ACTIVE = 1
WHERE 1=1
UNION ALL
-- USER INSTANCE RIGHTS, GROUP INSTANCE_RIGHTS
SELECT DISTINCT OP.PRINCIPAL_ID, OP.PRINCIPAL_TYPE, OP.RIGHT_ID, 'Instance Right' RIGHT_TYPE,
grpv.right_type rightType, op.object_instance_id,
CASE
WHEN grpv.right_type IN ('SRM_PROJECTS','INV_IDEA') THEN (SELECT i.code FROM inv_investments i WHERE i.id = op.object_instance_id)
WHEN grpv.right_type = 'SRM_RESOURCES' THEN (SELECT r.unique_name FROM srm_resources r WHERE r.id = op.object_instance_id)
WHEN grpv.right_type = 'CMN_PORTLETS' THEN (SELECT cp.portlet_code FROM CMN_PORTLETS cp WHERE cp.id = op.object_instance_id)
WHEN grpv.right_type = 'CMN_SCH_JOB_DEFINITIONS' THEN (SELECT sjd.job_code FROM CMN_SCH_JOB_DEFINITIONS sjd WHERE sjd.id = op.object_instance_id)
WHEN grpv.right_type = 'CMN_PAGES' THEN (SELECT pg.page_code FROM CMN_PAGES pg WHERE pg.id = op.object_instance_id)
WHEN grpv.right_type = 'BPM_DEF_PROCESSES' THEN (SELECT dp.process_code from BPM_DEF_PROCESSES dp WHERE dp.id = op.object_instance_id)
WHEN grpv.right_type = 'cop_prj_statusrpt' THEN (SELECT sr.code from odf_ca_cop_prj_statusrpt sr WHERE sr.id = op.object_instance_id)
WHEN grpv.right_type = 'DPT_DEPARTMENT' THEN (SELECT description from DEPARTMENTS WHERE id = op.object_instance_id)
WHEN grpv.right_type = 'DPT_LOCATION' THEN (SELECT locationdescription from LOCATIONS WHERE id = op.object_instance_id)
END INSTANCE_NAME,
NULL INSTANCE_MODE
FROM CMN_SEC_ASSGND_OBJ_PERM OP
,cmn_sec_groups_v grpv
WHERE op.right_id = grpv.id
AND grpv.language_code = 'en'
AND grpv.group_name NOT LIKE '%(Auto)%') AR ON A.PRINCIPAL_ID = AR.PRINCIPAL_ID AND A.PRINCIPAL_TYPE = AR.PRINCIPAL_TYPE
INNER JOIN CMN_SEC_GROUPS R ON AR.RIGHT_ID = R.ID AND R.GROUP_ROLE_TYPE = 'ROLE'
INNER JOIN CMN_CAPTIONS_NLS RN ON R.ID = RN.PK_ID AND RN.TABLE_NAME = 'CMN_SEC_GROUPS' AND RN.LANGUAGE_CODE = 'en'
LEFT JOIN CMN_LOOKUPS_V RT ON R.LIC_RIGHT_TYPE = RT.LOOKUP_CODE AND RT.LOOKUP_TYPE = 'CMN_LIC_RIGHT_TYPE' AND RT.LANGUAGE_CODE = 'en'
where SRMR.IS_ACTIVE = 1
and SRMR.UNIQUE_NAME != 'admin' and SRMR.UNIQUE_NAME != 'xc_admin'
and R.GROUP_CODE = 'ResourceApproveTime'
and MGRRES.ID = ?
GROUP BY
SRMR.ID
, SRMR.UNIQUE_NAME
, SRMR.FULL_NAME
, SRMR.EMAIL
, MGRRES.FULL_NAME
, MGRRES.EMAIL
, SRMR.IS_ACTIVE
, U.LAST_LOGGED_IN_DATE
, SRMR.PERSON_TYPE
, A.PRINCIPAL_NAME
, R.ID
, R.GROUP_CODE
, RN.NAME
, R.LIC_RIGHT_TYPE
, RT.NAME
, R.IS_AUTOMATIC
, MGRRES.ID
) SQ
order by SQ.MGR_NAME
]]>
<sql:param value="${rmgrId}"/>
</sql:query>
</core:catch>
<core:if test="${g_exception != null}">
<gel:log level="INFO">General Exception:</gel:log>
<gel:log level="ERROR">${g_exception}</gel:log>
</core:if>
<table border="1">
<tr>
<th bgcolor="#D3D3D3">Resource Email </th>
<th bgcolor="#D3D3D3">Resource Name </th>
</tr>
</core:forEach>
<core:forEach items="${result2.rowsByIndex}" trim="true" var="row2">
<core:set value="${row2[1]}" var="mgrName"/>
<core:set value="${row2[2]}" var="mgrEmail"/>
<core:set value="${row2[3]}" var="resName"/>
<core:set value="${row2[4]}" var="resEmail"/>
<core:invokeStatic className="java.net.URLEncoder" method="encode" var="resName">
<core:arg value="${resName}"/>
<core:arg value="UTF-8"/>
</core:invokeStatic>
<tr>
<td><center>${resName} </center></td> </center></td>
<td><center>${resEmail} </center></td>
</tr>
</table>
<BR>
<BR>
-------------------------------------------------------------------
<BR>
This is an automated message. Please do not reply.
</core:forEach>
</core:forEach>
</gel:script>