Start Free Trial

asked on

Excel - Alerting System - Sending Reminders

Good Afternoon,

See attached excel template - I have basic spreadsheet that records equipment for PAT Testing
Template.xlsx

The last column of the spreadsheet has a review date..

I am looking for an automatic solution when the review date is due, I would like to send a email to a particular email address telling you that the equipment is due for testing.

In the email body..

I would like to include details of the equipment type from the relevant columns.

I am looking to the schedule the script to run on the server under task scheduler.

Thanks

Would you want to send a list of items that are due on a particular date or individual emails for each item?

John Tsioumpris

You could - with the help of VBA - add entries to the Outlook calendar that will send Email reminders to the recipients...
I am afraid there has being a lot of years since i dealt with this and on Access but its viable.
Of course you will need some extra notification logging/handling to avoid duplicates but on Excel i am afraid i don't have the time to check a possible implementation.
Here is a blog post to get you started

ASKER

Individual emails for each item .. would be fine

Something like this. I can't test because |I don't have OutLook available at the moment.

Option Explicit


Sub SendPATMail()

    Dim OutLookApp As Object
    Dim OutLookMailItem As Object
    Dim iCounter As Integer
    Const MailDest As String = email address here"
    Dim NumRows As Integer

    Set OutLookApp = CreateObject("Outlook.Application")
    Set OutLookMailItem = OutLookApp.CreateItem(0)
    NumRows = Sheet1.Range("A4").CurrentRegion.Rows.Count

    With OutLookMailItem


        For iCounter = 2 To NumRows
''/// + 3 should send a warning 3 days prior to due date
            If CLng(Date + 3) = CLng(Sheet1.Cells(iCounter, 9)) Then
                .To = MailDest
                .CC = ""
                .BCC = ""
                .Subject = "FYI"
                .Body = "PAT Test Reminder: Pat test due for: " & "Item" & Sheet1.Cells(iCounter, 1) & " " & "Located in " & Sheet1.Cells(iCounter, 5)
                .Send
            End If
        Next iCounter


    End With

    Set OutLookMailItem = Nothing
    Set OutLookApp = Nothing

End Sub

Open in new window

If you change .Send to .Show it will display the email whilst testing

You can also use PowerShell ... make sure to modify the first few lines where indicated:

#####################################################
# modify the following variables as appropriate
$fileName    = "C:\PowerShell\Template.xlsx"
$emailserver = "<yourSMTPserver>" 
$mailto      = "admin@domain.com"  
$mailfrom    = "PATTesting@domain.com"
$mailsubject = "Equipment Due for Review!"
$title       = "PAT Testing Review Due"
#####################################################

# function to release a COM object
Function Release-Ref ($ref) {
     ([System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$ref) -gt 0)
     [System.GC]::Collect()
     [System.GC]::WaitForPendingFinalizers()
}

# style information for email
$header = @" 
<style>
body, TH, TD { font-family: Segoe UI, tahoma, Arial, sans-serif ; font-size:14px; }
h2 { font-family: tahoma; font-size:20px; }
TABLE {border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;width: 50%} 
TH {border-width: 1px;padding: 3px;border-style: solid;border-color: black;color:white; background-color: #6495ED;} 
TD {border-width: 1px;padding: 3px;border-style: solid;border-color: black;background-color:#dddddd;} 
</style>
"@

$objExcel = New-Object -ComObject Excel.Application 
$objExcel.Visible = $True
$wb = $objExcel.Workbooks.Open($fileName) 
$ws = $wb.Worksheets.Item("Template")

$today = (Get-Date).ToShortDateString()

# start at row 5
$intRow = 5

Do {
    # check review date
    $reviewDate = ([DateTime]$ws.Cells.Item($intRow, 9).Value()).ToShortDateString()
    if ($reviewDate -eq $today) {
	# create a custom PowerShell object with the equipment informatioin
	$equipInfo = New-Object -Type PSObject -Property @{
		'Item No.'=$ws.Cells.Item($intRow,1).Value()
		'Equipment'=$ws.Cells.Item($intRow,2).Value()
		'Model'=$ws.Cells.Item($intRow,3).Value()
		'Description'=$ws.Cells.Item($intRow,4).Value()
		'Location'=$ws.Cells.Item($intRow,5).Value()
		'Date Tested'=$ws.Cells.Item($intRow,6).Value().ToShortDateString()
		'Outcome'=$ws.Cells.Item($intRow,7).Value()
		'Frequency'=$ws.Cells.Item($intRow,8).Value()
		'Next Review'=$ws.Cells.Item($intRow,9).Value().ToShortDateString()
	}
	# convert to HTML and send message
	$message = $equipInfo | Select 'Item No.', 'Equipment', 'Model', 'Description', 'Location', 
	'Date Tested', 'Outcome', 'Frequency', 	'Next Review' | ConvertTo-Html -head $header -Title $title -PreContent "<h2>$title</h2>" 
	Send-MailMessage -From $mailfrom -To $mailto -Subject $mailSubject -Body "$message" -SmtpServer $emailserver -BodyAsHtml
    }
    $intRow++

} While ($ws.Cells.Item($intRow,1).Value() -ne $null)

$objExcel.Quit()

$void = Release-Ref $ws 
$void = Release-Ref $wb
$void = Release-Ref $objExcel

Open in new window

This will generate an email similar to the following (I changed one of the dates to today so it would generate the email):

User generated image

ASKER

Please could you add smtp user, smtp password and smtp port to the parameters.

When you run the power shell is it possible when it opens excel to have an invisible open..

Yes ... give me a bit to update ...

Please try this (sorry, I haven't had a chance to try the modified version) ... please let me know if you have any issues.
Also, do you need to have the SSL turned on for your email? If so, add -UseSSL to the Send-MailMessage command.

#####################################################
# modify the following variables as appropriate
$fileName    = "C:\PowerShell\Template.xlsx"
$SMTPserver = "<yourSMTPserver>" 
$SMTPport = 25
$mailto      = "admin@domain.com"  
$mailfrom    = "PATTesting@domain.com"
$mailsubject = "Equipment Due for Review!"
$title       = "PAT Testing Review Due"
#####################################################

# function to release a COM object
Function Release-Ref ($ref) {
     ([System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$ref) -gt 0)
     [System.GC]::Collect()
     [System.GC]::WaitForPendingFinalizers()
}

# style information for email
$header = @" 
<style>
body, TH, TD { font-family: Segoe UI, tahoma, Arial, sans-serif ; font-size:14px; }
h2 { font-family: tahoma; font-size:20px; }
TABLE {border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;width: 50%} 
TH {border-width: 1px;padding: 3px;border-style: solid;border-color: black;color:white; background-color: #6495ED;} 
TD {border-width: 1px;padding: 3px;border-style: solid;border-color: black;background-color:#dddddd;} 
</style>
"@

# get user's credentials
$creds = Get-Credential
if ($creds -eq $Null) { Return }

$objExcel = New-Object -ComObject Excel.Application 
$objExcel.Visible = $False
$wb = $objExcel.Workbooks.Open($fileName) 
$ws = $wb.Worksheets.Item("Template")

$today = (Get-Date).ToShortDateString()

# start at row 5
$intRow = 5

Do {
    # check review date
    $reviewDate = ([DateTime]$ws.Cells.Item($intRow, 9).Value()).ToShortDateString()
    if ($reviewDate -eq $today) {
	# create a custom PowerShell object with the equipment informatioin
	$equipInfo = New-Object -Type PSObject -Property @{
		'Item No.'=$ws.Cells.Item($intRow,1).Value()
		'Equipment'=$ws.Cells.Item($intRow,2).Value()
		'Model'=$ws.Cells.Item($intRow,3).Value()
		'Description'=$ws.Cells.Item($intRow,4).Value()
		'Location'=$ws.Cells.Item($intRow,5).Value()
		'Date Tested'=$ws.Cells.Item($intRow,6).Value().ToShortDateString()
		'Outcome'=$ws.Cells.Item($intRow,7).Value()
		'Frequency'=$ws.Cells.Item($intRow,8).Value()
		'Next Review'=$ws.Cells.Item($intRow,9).Value().ToShortDateString()
	}
	# convert to HTML and send message
	$message = $equipInfo | Select 'Item No.', 'Equipment', 'Model', 'Description', 'Location', 
	'Date Tested', 'Outcome', 'Frequency', 	'Next Review' | ConvertTo-Html -head $header -Title $title -PreContent "<h2>$title</h2>" 
	Send-MailMessage -From $mailfrom -To $mailto -Subject $mailSubject -Body "$message" -Credential $creds -SmtpServer $SMTPserver -Port $SMTPport -BodyAsHtml 
    }
    $intRow++

} While ($ws.Cells.Item($intRow,1).Value() -ne $null)

$objExcel.Quit()

$void = Release-Ref $ws 
$void = Release-Ref $wb
$void = Release-Ref $objExcel

Open in new window

ASKER

Thanks for the amended code..

I am getting an error message in the process - see image

User generated image

I see that you open a pop up window so that I can enter 'username' & 'password', is it not possible embed this data into the code.

As the idea is to schedule this job in the task scheduler

Thanks for your help

I will update this for you shortly.

Do you have any blanks in the review date column?
Can you stick this line:

    Write-Host "Checking row: $($intRow) ... review date: $($ws.Cells.Item($intRow, 9).Value())"

Open in new window

... right after:

    # check review date

Open in new window

What is the value displayed right before the error?
I'll send you the code for the credential update in just a bit.

While you can't put your credentials (ID and password) into the script (and you really wouldn't want to), you can save them securely, and then have the script retrieve and use them. The first step is to do this separately - you only need to do this once:

$creds = Get-Credential
$creds.Password | ConvertFrom-SecureString | Set-Content c:\temp\encryptedPW.txt

Open in new window

This will give you the pop-up where you can enter your credentials, and the second line stores it securely on your PC.
I'll send you the modified code for the above to then retrieve and use it.

Nick,

Please see if this fixes the error ... I've updated the credential code ... note that you need to modify the username variable at the top.

#####################################################
# modify the following variables as appropriate
$fileName    = "C:\PowerShell\Template.xlsx"
$SMTPserver  = "<yourSMTPserver>" 
$SMTPport    = 25
$mailto      = "admin@domain.com"  
$mailfrom    = "PATTesting@domain.com"
$mailsubject = "Equipment Due for Review!"
$title       = "PAT Testing Review Due"
$userName    = "your username"
$credentialFile = "c:\temp\encryptedPW.txt"
#####################################################

# function to release a COM object
Function Release-Ref ($ref) {
     ([System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$ref) -gt 0)
     [System.GC]::Collect()
     [System.GC]::WaitForPendingFinalizers()
}

# style information for email
$header = @" 
<style>
body, TH, TD { font-family: Segoe UI, tahoma, Arial, sans-serif ; font-size:14px; }
h2 { font-family: tahoma; font-size:20px; }
TABLE {border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;width: 50%} 
TH {border-width: 1px;padding: 3px;border-style: solid;border-color: black;color:white; background-color: #6495ED;} 
TD {border-width: 1px;padding: 3px;border-style: solid;border-color: black;background-color:#dddddd;} 
</style>
"@

# retrieve secured credentials
$securePW = Get-Content $credentialFile | ConvertTo-SecureString
$creds = New-object System.Management.Automation.PSCredential($userName,$securePW)

$objExcel = New-Object -ComObject Excel.Application 
$objExcel.Visible = $False
$wb = $objExcel.Workbooks.Open($fileName) 
$ws = $wb.Worksheets.Item("Template")

$today = (Get-Date).ToShortDateString()

# start at row 5
$intRow = 5

Do {
    # check review date
    Write-Host "Checking row: $($intRow) ... review date: $($ws.Cells.Item($intRow, 9).Value())... date tested: review date: $($ws.Cells.Item($intRow, 6).Value())"
    $reviewDate = ([DateTime]$ws.Cells.Item($intRow, 9).Value()).ToShortDateString()
    if ($reviewDate -eq $today) {
	# create a custom PowerShell object with the equipment information
	$equipInfo = New-Object -Type PSObject -Property @{
		'Item No.'=$ws.Cells.Item($intRow,1).Value()
		'Equipment'=$ws.Cells.Item($intRow,2).Value()
		'Model'=$ws.Cells.Item($intRow,3).Value()
		'Description'=$ws.Cells.Item($intRow,4).Value()
		'Location'=$ws.Cells.Item($intRow,5).Value()
		'Date Tested'=([DateTime]$ws.Cells.Item($intRow, 6).Value()).ToShortDateString()
		'Outcome'=$ws.Cells.Item($intRow,7).Value()
		'Frequency'=$ws.Cells.Item($intRow,8).Value()
		'Next Review'=([DateTime]$ws.Cells.Item($intRow, 9).Value()).ToShortDateString()
	}
	# convert to HTML and send message
	$message = $equipInfo | Select 'Item No.', 'Equipment', 'Model', 'Description', 'Location', 
	'Date Tested', 'Outcome', 'Frequency', 	'Next Review' | ConvertTo-Html -head $header -Title $title -PreContent "<h2>$title</h2>" 
	Send-MailMessage -From $mailfrom -To $mailto -Subject $mailSubject -Body "$message" -Credential $creds -SmtpServer $SMTPserver -Port $SMTPport -BodyAsHtml 
    }
    $intRow++

} While ($ws.Cells.Item($intRow,1).Value() -ne $null)

$objExcel.Quit()

$void = Release-Ref $ws 
$void = Release-Ref $wb
$void = Release-Ref $objExcel

Open in new window

Please note that you need to change the location of your credentials in line 32.

Nick ... I modified the above and put the name of the credential file at the top with the other variables.

ASKER

I made the amendments..

Different Error Message

User generated image

Are you saving and retrieving the password on the same PC or server?
For security reasons they must be the same.

ASKER

It's all on the server..

Please could you explain

The ConvertFrom-SecureString and ConvertTo-SecureString must be done on the same server and using the same ID.
On line 10 above ($userName), did you add your domain?

ASKER

Sorry.. you have to be patient with me as I have no expert knowledge with this.

The details I am entering are the same credentials i have been given and the username has a domain

Here is a nice summary of storing/retrieving credentials:

No need for apologies ... we've all been where you are ... :)

ASKER

I think I am getting there..

User generated image

Change line 66 above to (added -UseSSL):

	Send-MailMessage -From $mailfrom -To $mailto -Subject $mailSubject -Body "$message" -Credential $creds -SmtpServer $SMTPserver -Port $SMTPport -UseSSL -BodyAsHtml

Open in new window

ASKER

Still getting the same message.. we must be so close

Hmmm ... most SMTP servers will only accept connections from authorized IP addresses.
This is to prevent someone from sending our SPAM using your email server.
Can you find out if the server you are sending from is authorized?

ASKER

Yes it is authorized..

I send other email using VB Scripts..

When I was testing about a hour ago.. I did get a email generated but had other error messages

ASKER

Found the error message.. username has a full stop in the line

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

I left a full-stop in the text when the typed the password and I have removed it, it works accordingly

Thanks

You are most welcome ... glad you got it working.

ASKER

It's a very good process

User generated image

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Thanks for completing the above script - I would like to make a small change to process..

When the script can't find any entries in the spreadsheet, I would like the script to abort instead of sending a email confirming it can't find anything

Thanks

#####################################################
# modify the following variables as appropriate
$fileName    = "C:\PowerShell\Template.xlsx"
$SMTPserver  = "<yourSMTPserver>" 
$SMTPport    = 25
$mailto      = "admin@domain.com"  
$mailfrom    = "PATTesting@domain.com"
$mailsubject = "Equipment Due for Review!"
$title       = "PAT Testing Review Due"
$userName    = "your username"
$credentialFile = "c:\temp\encryptedPW.txt"
#####################################################

# function to release a COM object
Function Release-Ref ($ref) {
     ([System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$ref) -gt 0)
     [System.GC]::Collect()
     [System.GC]::WaitForPendingFinalizers()
}

# style information for email
$header = @" 
<style>
body, TH, TD { font-family: Segoe UI, tahoma, Arial, sans-serif ; font-size:14px; }
h2 { font-family: tahoma; font-size:20px; }
TABLE {border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;width: 50%} 
TH {border-width: 1px;padding: 3px;border-style: solid;border-color: black;color:white; background-color: #6495ED;} 
TD {border-width: 1px;padding: 3px;border-style: solid;border-color: black;background-color:#dddddd;} 
</style>
"@

# retrieve secured credentials
$securePW = Get-Content $credentialFile | ConvertTo-SecureString
$creds = New-object System.Management.Automation.PSCredential($userName,$securePW)

$objExcel = New-Object -ComObject Excel.Application 
$objExcel.Visible = $False
$wb = $objExcel.Workbooks.Open($fileName) 
$ws = $wb.Worksheets.Item("Template")

$today = (Get-Date).ToShortDateString()

# start at row 5
$intRow = 5

$equipInfo = @()

Do {
    # check review date
    $reviewDate = ([DateTime]$ws.Cells.Item($intRow, 9).Value()).ToShortDateString()
    if ($reviewDate -eq $today) {
	   # create a custom PowerShell object with the equipment informatioin
	   $equipInfo += New-Object -Type PSObject -Property @{
		'Item No.'=$ws.Cells.Item($intRow,1).Value()
		'Equipment'=$ws.Cells.Item($intRow,2).Value()
		'Model'=$ws.Cells.Item($intRow,3).Value()
		'Description'=$ws.Cells.Item($intRow,4).Value()
		'Location'=$ws.Cells.Item($intRow,5).Value()
		'Date Tested'=([DateTime]$ws.Cells.Item($intRow, 6).Value()).ToShortDateString()
		'Outcome'=$ws.Cells.Item($intRow,7).Value()
		'Frequency'=$ws.Cells.Item($intRow,8).Value()
		'Next Review'=([DateTime]$ws.Cells.Item($intRow, 9).Value()).ToShortDateString()
	   }
    
    }
    $intRow++

} While ($ws.Cells.Item($intRow,1).Value() -ne $null)

# convert to HTML and send message
if ($equipInfo.Length -gt 0) {
	$message = $equipInfo | Select 'Item No.', 'Equipment', 'Model', 'Description', 'Location', 
	'Date Tested', 'Outcome', 'Frequency', 	'Next Review' | ConvertTo-Html -head $header -Title $title -PreContent "<h2>$title - $today</h2>" 
	Send-MailMessage -From $mailfrom -To $mailto -Subject $mailSubject -Body "$message" -Credential $creds -SmtpServer $SMTPserver -Port $SMTPport -UseSSL -BodyAsHtml 
} else {
	Write-Host "No equipment is due for review today."  
}


$objExcel.Quit()

$void = Release-Ref $ws 
$void = Release-Ref $wb
$void = Release-Ref $objExcel

Open in new window