?
Solved

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

Posted on 2015-02-01
5
Medium Priority
?
1,085 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 46

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 2000 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

Introducing Priority Question

Increase expert visibility of your issues by participating in Priority Question, our latest feature for Premium and Team Account holders. Adjust the priority of your question to get emergent issues in front of subject-matter experts for help when you need it most.

Question has a verified solution.

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

A project that enables an administrator to perform actions within a user session context not just at the time of login but any time later on day(s) or week(s) later.
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...
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…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

771 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