Solved

Create vbscript/powershell to send reminder email based on data in Excel

Posted on 2015-02-01
5
853 Views
Last Modified: 2015-02-03
Dear Experts,
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. abc1@abc.com;def1@def.com;jkl1@jkl.com)

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:-
Dear Sirs,
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.
kunghui
0
Comment
Question by:kunghui80
  • 2
  • 2
5 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 40584798
Please post a representative sample workbook.

What email software are you using?
0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
ID: 40584826
Hi, kunghui.

Assuming that you have Outlook installed, then this VBscript should do it.  To use thi

1.  Open Notepad
2.  Copy the code and paste it into Notepad
3.  Edit the code per the comments I included in the file
4.  Save the file.  You may name it anything you like so long as the file extension is .vbs
5.  Launch Windows Task Scheduler and create a new scheduled task
6.  Set the task to run each day
7.  Set the task to run a script
8.  Set this script as the script to run

'--> Define some constants
	'On the next line, edit the path to and name of the workobook to process
	Const WORKBOOK_PATH = "C:\Certificates.xlsx"
	Const olMailItem = 0
	Const olFormatHTML = 2

'--> Define some variables
	Dim excApp, excWkb, excWks, olkApp, olkSes, olkMsg, lngRow, intAge

'--> Connect to Excel
	Set excApp = CreateObject("Excel.Application")
	Set excWkb = excApp.Workbooks.Open(WORKBOOK_PATH)
	Set excWks = excWkb.Worksheets(1)

'--> Connect to Outlook
	Set olkApp = CreateObject("Outlook.Application")
	Set olkSes = olkApp.GetNamespace("MAPI")

'--> Main routine
	For lngRow = 2 To excWks.usedrange.rows.count
		intAge = DateDiff("d", excWks.Cells(lngRow,6).value, Date)
		Select Case intage
			Case 30,21,14
				Set olkMsg = olkApp.CreateItem(olMailItem)
				With olkMsg
					.To = excWks.cells(lngRow,12).value
					.Subject = "Certificate Expires in " & intAge & " Days"
					.BodyFormat = olFormatHTML
					.HTMLBody = "Dear Sirs,<br><br>" & _
								"Type of Certificate: " & excWks.cells(lngRow,3).value & "<br>" & _
								"Asset Reference: " & excWks.cells(lngRow,11).value & "<br>" & _
								"The certificate name () with Request ID: " & excWks.cells(lngRow,1).value & _
								" and Serial Number: " & excWks.cells(lngRow,4).value & _
								" will expire on " & excWks.cells(lngRow,6).value & "." 
					.Send
				End With
		End Select
	Next

'--> Disconnect from Outlook
	olkSes.Logoff
	Set olkSes = Nothing
	Set olkApp = Nothing

'--> Disconnect from Excel
	excWkb.Close False
	Set excWks = Nothing
	Set excWkb = Nothing
	Set excApp = Nothing

'--> Terminate script
	WScript.Quit

Open in new window

0
 
LVL 2

Author Comment

by:kunghui80
ID: 40584876
H BlueDevilFan,
I don't have Outlook application installed on server. Is it difficult if change to default component such as CDO.message or something that usually come with default? I'm using Microsoft Windows Server 2008. Thanks much for your reply, I'm testing on this and let you know soon.
0
 
LVL 2

Author Comment

by:kunghui80
ID: 40585628
Hi BlueDevilFan,
I manage to achieve sendmail using CDO.Message and it works good.  Thanks for the efforts!

For lngRow = 2 To excWks.usedrange.rows.count
	intAge = DateDiff("d", excWks.Cells(lngRow,6).value, Date)
	Select Case intage
		Case 30,21,14
			Set objEmail = CreateObject("CDO.Message")
			Set objConf = CreateObject("CDO.Configuration")
			Set objFlds = objConf.Fields
			With objFlds
				.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
				.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "MyMailServer"
				.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
				.Update
			End With
			Set objEmail.Configuration = objConf
			objEmail.From = "CompanyEmail@abc.com"
			objEmail.To = excWks.cells(lngRow,12).value
			objEmail.Cc = "Somebody@abc.com"
			objEmail.Subject = "Certificate (" & excWks.cells(lngRow,10).value & ") Expires in " & intAge & " Days"
			objEmail.TextBody = "Dear Sirs," & vbcrlf & vbcrlf & _
				"Type of Certificate: " & excWks.cells(lngRow,3).value & vbcrlf & vbcrlf & _
				"Asset: " & excWks.cells(lngRow,11).value & vbcrlf & vbcrlf & _
				"The certificate name " & excWks.cells(lngRow,10) & " with Request ID: " & excWks.cells(lngRow,1).value & _
				" and Serial Number: " & excWks.cells(lngRow,4).value & _
				" will expire on " & excWks.cells(lngRow,6).value & "."
			objEmail.Send
	End Select
Next

Open in new window

0
 
LVL 76

Expert Comment

by:David Lee
ID: 40585911
You're welcome, kunghui80.  Sorry that I wasn't able to respond faster.  

You'd mentioned wanting an HTML message.  You can have that with CDO by switching TextBody to HTMLBody.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Are you one of those front-line IT Service Desk staff fielding calls, replying to emails, all-the-while working to resolve end-user technological nightmares? I am! That's why I have put together this brief overview of tools and techniques I use in o…
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now