A BIG thanks to all here in the forums, especially these two threads:
How to code an HTML formatted email using GEL script
GEL: Email to Group
An extra-special "Thanks!" to gcubed for a little something called a GEL Launcher.
<gel:script xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:core="jelly:core"
xmlns:util="jelly:util"
xmlns:email="jelly:email"
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:sql="jelly:sql"
xmlns:jxml="jelly:xml"
xmlns:xog="http://www.niku.com/xog"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<core:set var="debug" value="${true}"/>
<gel:parameter var="xogUsername" default="not set"/>
<gel:parameter var="xogPassword" secure="true" default="not set"/>
<gel:parameter var="fromEmailAddress" default="noreply@******.com"/>
<gel:parameter var="emailGroup" default="OSUMC Demand Managers - IT"/>
<gel:setDataSource dbId="niku"/>
<sql:query var="data" escapeText="0">
<![CDATA[SELECT
I.NAME idea_name
, I.ID idea_id
, I.CODE idea_code
, (SELECT CONVERT(char(10), DATEADD(mm, DATEDIFF(mm, 0, I.SCHEDULE_START) + 6, 0), 126))+'T00:00:00' idea_new_start_for_xog
, (SELECT CONVERT(char(10), DATEADD(mm, DATEDIFF(mm, 0, I.SCHEDULE_START) + 6, 0), 101)) idea_new_start_for_email
, (SELECT CONVERT(char(10), DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, I.SCHEDULE_FINISH) + 7, 0)), 126))+'T00:00:00' idea_new_finish_for_xog
, (SELECT CONVERT(char(10), DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, I.SCHEDULE_FINISH) + 7, 0)), 101)) idea_new_finish_for_email
, CONVERT(char(10), FLOOR(I.LABOR_ETCSUM/3600)) AS idea_allocation_hours
FROM INV_INVESTMENTS I
WHERE I.ODF_OBJECT_CODE = 'Idea'
AND I.IS_ACTIVE = 1
AND I.STAGE_CODE = 'osumc_intake'
AND I.SCHEDULE_START < (SELECT DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 2, 0)))]]>
</sql:query>
<core:invokeStatic className="com.niku.union.config.ConfigurationManager" method="getInstance" var="config"/>
<core:set var="v_IsLocal">0</core:set>
<core:set value="${config.getProperties().getWebServer().getWebServerInstance(0).getId()}" var="WebServerid"/>
<core:if test="${WebServerid == 'app'}">
<core:set value="${config.getProperties().getWebServer().getWebServerInstance(0).getAddress()}" var="AddressIp"/>
<core:set value="${config.getProperties().getWebServer().getWebServerInstance(0).getSslEntryUrl()}" var="EntryURL"/>
<core:set value="${config.getProperties().getWebServer().getWebServerInstance(0).getSslPort()}" var="Port"/>
<core:if test="${EntryURL == 'http://localhost' }">
<gel:log level="info" message="NULL ADDRESS IS LOCAL... "/>
<core:set var="v_IsLocal">1</core:set>
<core:set var="AddressIp">${EntryURL}</core:set>
</core:if>
</core:if>
<core:if test="${WebServerid == 'nsa'}">
<core:set value="${config.getProperties().getWebServer().getWebServerInstance(1).getAddress()}" var="AddressIp"/>
<core:set value="${config.getProperties().getWebServer().getWebServerInstance(1).getEntryUrl()}" var="EntryURL"/>
<core:set value="${config.getProperties().getWebServer().getWebServerInstance(1).getPort()}" var="Port"/>
<core:if test="${EntryURL == 'http://localhost' }">
<gel:log level="info" message="NULL ADDRESS IS LOCAL... "/>
<core:set var="v_IsLocal">1</core:set>
<core:set var="AddressIp">${EntryURL}</core:set>
</core:if>
</core:if>
<core:if test="${Port != null }">
<core:set value="${EntryURL}" var="EntryURL"/>
</core:if>
<core:if test="${v_IsLocal == 0 }">
<core:if test="${AddressIp != '' }">
<core:set value="http://${AddressIp}" var="EntryURL"/>
</core:if>
</core:if>
<core:set value="${config.getProperties().getDirectories().getInstallDir()}" var="NIKUHOME"/>
<core:set value="${EntryURL}/niku/xog" var="xogURL"/>
<core:set value="${config.getProperties().getMailServer().getHost()}" var="mailServer"/>
<soap:invoke endpoint="${xogURL}" var="auth">
<soap:message>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">
<soapenv:Header/>
<soapenv:Body>
<xog:Login>
<xog:Username>${xogUsername}</xog:Username>
<xog:Password>${xogPassword}</xog:Password>
</xog:Login>
</soapenv:Body>
</soapenv:Envelope>
</soap:message>
</soap:invoke>
<gel:set asString="true" select="$auth/SOAP-ENV:Envelope/SOAP-ENV:Body/xog:SessionID/text()" var="sessionID"/>
<core:choose>
<core:when test="${sessionID == null}">
<gel:log>Couldn't log in. Please check the hostname/username/password.</gel:log>
</core:when>
<core:otherwise>
<gel:log>Login successful. Session ID is ${sessionID}</gel:log>
</core:otherwise>
</core:choose>
<core:forEach items="${data.rows}" trim="true" var="row">
<core:set value="${row.get('idea_name')}" var="idea_name"/>
<core:set value="${row.get('idea_code')}" var="idea_code"/>
<core:set value="${row.get('idea_new_start_for_xog')}" var="idea_new_start_for_xog"/>
<core:set value="${row.get('idea_new_finish_for_xog')}" var="idea_new_finish_for_xog"/>
<soap:invoke endpoint="${xogURL}" var="updateFinish">
<soap:message>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">
<soapenv:Header>
<xog:Auth>
<xog:SessionID>${sessionID}</xog:SessionID>
</xog:Auth>
</soapenv:Header>
<soapenv:Body>
<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_idea.xsd">
<Header action="write" externalSource="NIKU" objectType="idea" version="8.0"/>
<Ideas>
<Idea name="${idea_name}" objectID="${idea_code}" openForTimeEntry="0" finish="${idea_new_finish_for_xog}">
</Idea>
</Ideas>
</NikuDataBus>
</soapenv:Body>
</soapenv:Envelope>
</soap:message>
</soap:invoke>
<soap:invoke endpoint="${xogURL}" var="updateStart">
<soap:message>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">
<soapenv:Header>
<xog:Auth>
<xog:SessionID>${sessionID}</xog:SessionID>
</xog:Auth>
</soapenv:Header>
<soapenv:Body>
<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_idea.xsd">
<Header action="write" externalSource="NIKU" objectType="idea" version="8.0"/>
<Ideas>
<Idea name="${idea_name}" objectID="${idea_code}" openForTimeEntry="0" start="${idea_new_start_for_xog}">
</Idea>
</Ideas>
</NikuDataBus>
</soapenv:Body>
</soapenv:Envelope>
</soap:message>
</soap:invoke>
</core:forEach>
<soap:invoke endpoint="${xogURL}" var="logoutresult">
<soap:message>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">
<soapenv:Header>
<xog:Auth>
<xog:SessionID>${sessionID}</xog:SessionID>
</xog:Auth>
</soapenv:Header>
<soapenv:Body>
<xog:Logout/>
</soapenv:Body>
</soapenv:Envelope>
</soap:message>
</soap:invoke>
<gel:parse var="v_mail_content">
<table>
<tr><font face="Calibri" size="3">
<th align="center">Idea Name</th>
<th align="center">Idea ID</th>
<th align="center">Start Date</th>
<th align="center">Finish Date</th>
<th align="center">Allocation Hours</th>
</font></tr>
</table>
</gel:parse>
<core:forEach items="${data.rows}" trim="true" var="row">
<core:set value="${row.get('idea_name')}" var="idea_name"/>
<core:set value="${row.get('idea_id')}" var="idea_id"/>
<core:set value="${EntryURL}/niku/nu#action:pma.ideaProperties&id=${idea_id}" var="v_inv_url"/>
<core:set value="${row.get('idea_code')}" var="idea_code"/>
<core:set value="${row.get('idea_new_start_for_email')}" var="idea_new_start_for_email"/>
<core:set value="${row.get('idea_new_finish_for_email')}" var="idea_new_finish_for_email"/>
<core:set value="${row.get('idea_allocation_hours')}" var="idea_allocation_hours"/>
<gel:parse var="v_detail_item">
<tr><font face="Calibri" size="3">
<td align="left"><a href="${v_inv_url}">${row.idea_name}</a></td>
<td align="center">${row.idea_code}</td>
<td align="center">${row.idea_new_start_for_email}</td>
<td align="center">${row.idea_new_finish_for_email}</td>
<td align="center">${row.idea_allocation_hours}</td>
</font></tr>
</gel:parse>
<core:catch var="gel_error">
<gel:set select="$v_mail_content/table" value="${v_detail_item}" insert="true" trim="false"/>
</core:catch>
</core:forEach>
<core:catch var="v_exception">
<gel:set asString="true" select="$v_mail_content/table" var="v_variable"/>
</core:catch>
<core:set value="0" var="emailCount"/>
<sql:query var="e_group">
<![CDATA[SELECT u.email_address
FROM CMN_CAPTIONS_NLS NLS,
cmn_sec_users u,
CMN_SEC_GROUPS G,
cmn_sec_user_groups UG,
CMN_LOOKUPS L,
srm_resources r
WHERE NLS.TABLE_NAME = 'CMN_SEC_GROUPS'
AND NLS.PK_ID = G.ID
AND NLS.LANGUAGE_CODE = 'en'
AND u.id = ug.user_id
AND u.id = r.user_id
AND G.GROUP_TYPE_ID = L.ID
and g.id = ug.group_id
AND L.LOOKUP_TYPE = 'SEC_GROUP_TYPE'
AND g.is_active = '1'
AND r.is_active = '1'
AND NLS.name = '${emailGroup}']]>
</sql:query>
<sql:query var="count_e_group">
<![CDATA[SELECT count(*) as num
FROM CMN_CAPTIONS_NLS NLS,
cmn_sec_users u,
CMN_SEC_GROUPS G,
cmn_sec_user_groups UG,
CMN_LOOKUPS L,
srm_resources r
WHERE NLS.TABLE_NAME = 'CMN_SEC_GROUPS'
AND NLS.PK_ID = G.ID
AND NLS.LANGUAGE_CODE = 'en'
AND u.id = ug.user_id
AND u.id = r.user_id
AND G.GROUP_TYPE_ID = L.ID
and g.id = ug.group_id
AND L.LOOKUP_TYPE = 'SEC_GROUP_TYPE'
AND g.is_active = '1'
AND r.is_active = '1'
AND NLS.name = '${emailGroup}']]>
</sql:query>
<core:if test="${count_e_group.rows[0].num == 1}">
<core:set value="${e_group.rows[0].email_address}" var="email_addr"/>
</core:if>
<core:if test="${count_e_group.rows[0].num > 1}">
<core:set value="${e_group.rows[0].email_address}" var="email_addr"/>
<core:forEach begin="1" items="${e_group.rows}" trim="true" var="email_row">
<core:set value="${email_addr},${email_row.email_address}" var="email_addr"/>
</core:forEach>
</core:if>
<core:if test="${count_e_group.rows[0].num == 0}">
<core:set value="${fromEmailAddress}" var="email_addr"/>
</core:if>
<core:invokeStatic className="java.lang.Class" method="forName" var="recipientType">
<core:arg type="java.lang.String" value="javax.mail.Message$RecipientType" />
</core:invokeStatic>
<core:set value="${recipientType.getField('TO')}" var="recipientTypeTO" />
<core:set value="${recipientTypeTO.TO}" var="recipientTypeTO" />
<core:set value="${recipientType.getField('CC')}" var="recipientTypeCC" />
<core:set value="${recipientTypeCC.CC}" var="recipientTypeCC" />
<core:set value="${recipientType.getField('BCC')}" var="recipientTypeBCC" />
<core:set value="${recipientTypeBCC.BCC}" var="recipientTypeBCC" />
<core:new className="java.util.Properties" var="props" />
<core:set value="${props.put('mail.transport.protocol', 'smtp')}" var="void" />
<core:set value="${props.put('mail.smtp.host', mailServer)}" var="void" />
<core:set value="${props.put('mail.smtp.auth', 'true')}" var="void" />
<core:new className="com.niku.union.notification.MailAuthenticator" var="ppm_auth" />
<core:invokeStatic className="javax.mail.Session" method="getInstance" var="mailSession">
<core:arg type="java.util.Properties" value="${props}" />
<core:arg type="javax.mail.Authenticator" value="${ppm_auth}" />
</core:invokeStatic>
<core:choose>
<core:when test="${idea_name == null}">
<gel:parse var="htmlMessage">
<message>
<![CDATA[<html>
<head>
<style>
table, th, td {
border: 1px solid black;
border-collapse: collapse;
}
th, td {
padding: 5px;
}
th {
text-align: left;
}
</style>
</head>
<body>
<font face="Calibri" size="3">
<p>No Ideas have been found in Intake with a Start Date less than 60 days out.</p>
</br></font></body></html>]]>
</message>
</gel:parse>
</core:when>
<core:otherwise>
<gel:parse var="htmlMessage">
<message>
<![CDATA[<html>
<head>
<style>
table, th, td {
border: 1px solid black;
border-collapse: collapse;
}
th, td {
padding: 5px;
}
th {
text-align: left;
}
</style>
</head>
<body>
<font face="Calibri" size="3">
<p>The following Ideas in Intake were found to start within 60 days and have been kicked back 6 months:</p>
]]>
${v_variable}
<![CDATA[</br>]]>
Please contact <![CDATA[<a href="mailto:itpmo@osumc.edu">IT PMO Demand Management</a>]]> with any questions.
<![CDATA[</br></font></body></html>]]>
</message>
</gel:parse>
</core:otherwise>
</core:choose>
<gel:forEach select="$htmlMessage/message/node()" var="thisMessageNode">
<gel:set asString="true" select="$thisMessageNode" var="thisMessageNodeText" />
<core:set value="${thisMessageText}${thisMessageNodeText}" var="thisMessageText" />
</gel:forEach>
<core:new className="javax.mail.internet.InternetAddress" var="sender">
<core:arg type="java.lang.String" value="${fromEmailAddress}" />
</core:new>
<core:new className="javax.mail.internet.InternetAddress" var="internetAddress"/>
<core:set value="${email_addr}" var="recipientsTO" />
<core:set value="${fromEmailAddress}" var="recipientsCC" />
<core:set value="Intake Queue Kick-The-Can-O-Matic" var="subject" />
<core:new className="javax.mail.internet.MimeMultipart" var="multiPart" />
<core:new className="javax.mail.internet.MimeBodyPart" var="bodyPart" />
<core:set value="${bodyPart.setContent(thisMessageText, 'text/html')}" var="void" />
<core:set value="${multiPart.addBodyPart(bodyPart)}" var="void" />
<core:new className="javax.mail.internet.MimeMessage" var="message">
<core:arg type="javax.mail.Session" value="${mailSession}" />
</core:new>
<core:set value="${mailSession.setDebug(debug)}" var="void" />
<core:set value="${message.setFrom(sender)}" var="void" />
<core:choose>
<core:when test="${recipientsTO.indexOf(',') > 0}">
<core:set value="${message.setRecipients(recipientTypeTO, internetAddress.parse(recipientsTO))}" var="void" />
</core:when>
<core:otherwise>
<core:new className="javax.mail.internet.InternetAddress" var="recipientsTO">
<core:arg type="java.lang.String" value="${recipientsTO}" />
</core:new>
<core:set value="${message.setRecipient(recipientTypeTO, recipientsTO)}" var="void" />
</core:otherwise>
</core:choose>
<core:choose>
<core:when test="${recipientsCC.indexOf(',') > 0}">
<core:set value="${message.setRecipients(recipientTypeCC, internetAddress.parse(recipientsCC))}" var="void" />
</core:when>
<core:otherwise>
<core:new className="javax.mail.internet.InternetAddress" var="recipientsCC">
<core:arg type="java.lang.String" value="${recipientsCC}" />
</core:new>
<core:set value="${message.setRecipient(recipientTypeCC, recipientsCC)}" var="void" />
</core:otherwise>
</core:choose>
<core:set value="${message.setSubject(subject)}" var="void" />
<core:set value="${message.setContent(multiPart)}" var="void" />
<core:set value="${mailSession.getTransport()}" var="transport" />
<core:set value="${transport.connect()}" var="void" />
<core:switch on="${transport.isConnected()}">
<core:case value="${true}">
<core:set value="${transport.send(message)}" var="void" />
<core:set value="${transport.close()}" var="void" />
</core:case>
<core:default>
<core:new className="java.lang.Exception" var="exception">
<gel:log level="ERROR">"Cannot connect to mail server: ${MailServer}"</gel:log>
</core:new>
</core:default>
</core:switch>
<gel:log> ${xogURL}, ${xogUsername}, ${xogPassword}, ${sessionID}</gel:log>
</gel:script>
Thanks all!