Solved

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

Posted on 2015-02-01
5
965 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
A recent project that involved parsing Tableau Desktop and Server log files to extract reusable user queries for use in other systems. I chose to use PowerShell to gather the data, and SharePoint to present it...
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

749 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