Easy way to create on-the-fly Excel attachment on emails

While working on a project, I got asked by a customer – ‘Is it possible to add an on-the-fly attachment to a notification?’. That left me wondering as it was one of the most unusual requests. When we are sending out a notification from say an incident, there is no way to create & add an attachment to it (at least I haven’t noticed). So I set about creating one..

    1. The first step is to create data ready for csv file. In my scenario, I have to trigger an email notification with all stock items marked as ‘processed’. There will be a UI action on form which will raise an event for that email notification. I’m using Incident table for this.

var csv = '';
var movestockRec = new GlideRecord('u_movestock');
movestockRec.addQuery('u_processed', true);
movestockRec.addQuery('u_batch_no', source.u_batch_no);
movestockRec.query();
while (movestockRec.next()) {
csv += movestockRec.u_part_number + ','
+ movestockRec.u_good_qty + ','
+ movestockRec.u_from_location + ','
+ movestockRec.u_from_bin + ','
+ movestockRec.u_to_loc + ','
+ movestockRec.u_to_bin + ','
+ movestockRec.u_disposition + ','
+ movestockRec.u_cost_centre + ','
+ movestockRec.u_reference + ','
+ movestockRec.u_reason_code + ','
+ movestockRec.u_notes + '\n';
}

2. Next is add an attachment to sys_attachment table and raise an event.

var att = new Attachment();
att.setRecord(current);
att.setFilename(current.number + '-Stock-Move-Batch-' + '.csv');
att.setTargetTable('incident');
att.setTargetID(current.sys_id.toString());
att.setContentType('text/csv');
att.setValue(csv.toString());
var id = att.attach();
gs.eventQueue(“incident.processed”, current, id);

3. Now in the email notification which is using “incident.processed” event, I have used below Notification Email Script to add attachment link on the email.

(function runMailScript(current, template, email, email_action, event) {
// Add your code here
printattachments();
function printattachments() {
var gr = new GlideRecord('sys_attachment');
gr.addQuery('sys_id',event.parm1);
gr.query();
while (gr.next()) {
template.print('Attachment: ' + gr.file_name + '\n');
}
}
})(current, template, email, email_action, event);

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s