Send report monthly with attachments

[si21]

Hi Experts,

We have 'Internet Claim' record producer form. User will submit the form to claim their expenses and will attach required documents.

We have requirement to consolidate all the Internet Claim related HR cases created in a month and send it Claims team email along with the attachments in the cases.

Could you please guide me on the feasibility or the approach to follow?

TIA

[JenniferRah]

It sounds like a simple scheduled report is all that is needed, correct? If so, just create the report however you want and then click on "Schedule" from the Sharing tab. It should walk you through all the options. You can send it as a PDF, Excel, etc.

[si21]

Hi Jennifer, is there a way to attach Attachments present on the cases to the report?

[Pooja58]

Hi @si21 ,

Try creating scheduled job as shown below for Incident report.

// Define the list to store incident and attachment details
var incidentReport = 'Daily Closed Incidents Report\n\n';
incidentReport += 'Incident Number | Short Description | Attachments\n';
incidentReport += '-----------------------------------------------------\n';

// Query incidents that are closed today
var incidentGR = new GlideRecord('incident');
incidentGR.addQuery('state', '6'); // Assuming '6' is the Closed state
incidentGR.addQuery('closed_at', '>=', gs.beginningOfToday()); // Get incidents closed today
incidentGR.addQuery('closed_at', '<=', gs.endOfToday()); // Ensure only today's incidents are included
incidentGR.query();

while (incidentGR.next()) {
    // Build the list of closed incidents
    incidentReport += incidentGR.number + ' | ' + incidentGR.short_description + ' | ';

    // Query attachments for each incident
    var attachmentList = '';
    var attachmentGR = new GlideRecord('sys_attachment');
    attachmentGR.addQuery('table_name', 'incident');
    attachmentGR.addQuery('table_sys_id', incidentGR.sys_id);
    attachmentGR.query();

    while (attachmentGR.next()) {
        // Add attachment names and download URLs to the list
        attachmentList += attachmentGR.file_name + ' (Download URL: ' + gs.getProperty('glide.servlet.uri') + 'sys_attachment.do?sys_id=' + attachmentGR.sys_id + ') | ';
    }

    // If no attachments found, indicate that
    if (attachmentList == '') {
        attachmentList = 'No attachments';
    }

    incidentReport += attachmentList + '\n';
}

// Check if any incidents were found, then send the notification
if (incidentReport != '') {
    // Set up the email notification
    var email = new GlideEmailOutbound();
    email.setSubject('Daily Closed Incidents Report');
    email.setFrom('your-email@example.com'); // Replace with your email address
    email.setTo('recipient@example.com'); // Replace with the recipient's email address
    email.setBody(incidentReport);

    // Optional: Add any attachments you want in the email
    var attachmentGR = new GlideRecord('sys_attachment');
    attachmentGR.addQuery('table_name', 'incident');
    attachmentGR.addQuery('table_sys_id', incidentGR.sys_id);
    attachmentGR.query();
    
    // If you want to attach the list as a CSV or text file
    var attachmentContent = 'Incident Report\n' + incidentReport;
    var attachmentFile = new GlideSysAttachment();
    var file = new GlideSysAttachment();
    file.write('incident_report.txt', 'text/plain', attachmentContent);  // Save file in the attachment
    email.addAttachment(file);
    
    // Send the email
    email.send();
}

Please mark as correct/helpful if this solves your query.

Best Regards,

Pooja

[Ankur Bawiskar]

@si21 

Would you mind closing your earlier questions by marking appropriate response as correct?

Members have invested their time and efforts in helping you.