Overview

This script written in NodeJS is a straightforward tool capable of generating and mailing certificates in bulk, utilizing data from a spreadsheet (.xlsx).
The script creates certificates by utilizing a fillable PDF template and subsequently sends them to the designated email address.
Initially, I developed this project for sending MLSA certificates in bulk. However, it can be customized to generate certificates with a template of your choice. For instructions on how to do this, please refer to the "Changing Certificate Template" section.
Please note that there is limit on sending emails in bulk using a standard gmail account, so you may have to send in batches.

Setup for sending MLSA Certificates

1 Clone the repository using the command.

  $ git clone https://github.com/MR-DHRUV/Automate_certificate_generation_and_mailing.git

2 Open a terminal in the root folder of the project and install npm packages using the following command.

  $ npm i

3 Completing the .env file to initialize the mailer

Create a file named as .env in the root folder of the project and copy the contents of env.txt in it.
Put your Gmail id that will be used for mailing in front of "EMAIL" in the .env file.
Obtain app password for your gmail id that will act as password and will be used to send emails by reffering this link https://support.google.com/mail/answer/185833?hl=en, and paste it in front of "EMAIL_PASS2" in the env.

4 Update the event details in the eventDetails.js file.

5 Populate the data in the data.xlsx file in the following format.

Name	Email
`Name of the attendee`	`Email of the attendee`

6 Run the script using the following command.

   $ node index.js

7 Progress will be reported in the terminal as follows

    Certificate sent to : email1
    Certificate sent to : emailN

Changing Certificate Template

1 To generate certificates, it is necessary to create a fillable PDF template. For guidance on creating one, please refer to the following link . Save the pdf as template.pdf.

2 Update the eventDetails.js file and add the fixed(That will be same in all certificates) properties that you created in the template pdf.

3 Updating the genCert.js file to populate the new fixed properties.

    // Lets concider that we have added a new property named as "E_DATE" and that will be fixed in all certificates
    // Updating eventDetails.js
    module.exports = Object.freeze({
        ...
        // adding new property
        date : "16-03-23" // date of event
    });
    // Updating genCert.js
    // After the line 23 add the new feild as follows
    const E_DATE = form.getTextField('E_DATE');
    // Fill the property
    E_DATE.setText(details.date);

4 Adding properties that will be populated from spreadsheet.

    // Updating genCert.js
    // To populate this new property from excel we will pass this new property as a parameter to the function genCert present at line 10 as follows
    const genCert = async (name,email,date) => {....
 
    // After the line 23 add the new feild as follows
    const E_DATE = form.getTextField('E_DATE');
 
    // Now we have to fill the property as follows
    E_DATE.setText(date);
 
    // Updating index.js
    // Say this new property date is present in 3rd column of the excel sheet, so we'll modify line no 18 as follows
 
    // we'll add row[col-1] as js uses 0 based indexing
    genCert(row[0], row[1],row[2]);

We love contributions ❤️ and if you have any suggestions or improvements, feel free to open an issue or a pull request here.

Certify

Contents

Overview

Setup for sending MLSA Certificates

Changing Certificate Template