• 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.

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.

6 Run the script using the following command.

   $ node index.js

    Certificate sent to : email1
    Certificate sent to : emailN

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]);

For any issue or query I'll love to hear at : [email protected]

We love contributions ❤️
Contribute to this project here