Create vbscript/powershell to send reminder email based on data in Excel
Posted on 2015-02-01
I need some assistance on VB scriptings/PowerShell to read excel file created in 2010/2013 format.
I have excel spreadsheet (*.xlsx) which consists of list of data for certificate that going to expire. I need to make use of VB script to read excel column and compare date to send out reminder for certificate that going to expire in 30 days, 21 days & 14 days.
Here my column information:-
Column A: Request ID
Column C: Certificate Template
Column D: Serial Number
Column F: Expiry Date in dd/mm/yyyy format
Column J: Issued Common Name
Column K: Internal Assest Reference
Column L: Consists of different email address in format (i.e. firstname.lastname@example.org;email@example.com;firstname.lastname@example.org)
I need the reminder email to work in the following way.
The script will run daily. When a certificate listed in excel spreadsheet is due expiry in 30days, 21days or 14days, an email reminder should be sent base on list of emails in Column L.
Email content will be something like:-
Type of Certificate: <Certificate Template>
Asset Reference: <Internal Assest Reference>
The certificate name (<Issued Common Name>) with Request ID: <RequestID> and Serial Number: <Serial Number> will expire on <Date dd/mm/yyyy>.
If can be formatted using HTML format it will be even great, else plain text should be sufficient.
If PowerShell is being used, please let me know what are additinal add-ins required for it to work and where to download, I'm not very familiar with Powershell.
Thanks much in advance.