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
NICK COLLINSAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
Would you want to send a list of items that are due on a particular date or individual emails for each item?
John TsioumprisSoftware & Systems EngineerCommented:
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
NICK COLLINSAuthor Commented:
Individual emails for each item .. would be fine
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Roy CoxGroup Finance ManagerCommented:
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
Sam JacobsDirector of Technology Development, IPMCommented:
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):
ReviewDue.jpg
NICK COLLINSAuthor Commented:
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..
Sam JacobsDirector of Technology Development, IPMCommented:
Yes ... give me a bit to update ...
Sam JacobsDirector of Technology Development, IPMCommented:
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

NICK COLLINSAuthor Commented:
Thanks for the amended code..

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

ErrorMessage.jpg
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
Sam JacobsDirector of Technology Development, IPMCommented:
I will update this for you shortly.
Sam JacobsDirector of Technology Development, IPMCommented:
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.
Sam JacobsDirector of Technology Development, IPMCommented:
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.
Sam JacobsDirector of Technology Development, IPMCommented:
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

Sam JacobsDirector of Technology Development, IPMCommented:
Please note that you need to change the location of your credentials in line 32.
Sam JacobsDirector of Technology Development, IPMCommented:
Nick ... I modified the above and put the name of the credential file at the top with the other variables.
NICK COLLINSAuthor Commented:
I made the amendments..

Different Error Message

Capture.JPG
Sam JacobsDirector of Technology Development, IPMCommented:
Are you saving and retrieving the password on the same PC or server?
For security reasons they must be the same.
NICK COLLINSAuthor Commented:
It's all on the server..

Please could you explain
Sam JacobsDirector of Technology Development, IPMCommented:
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?
NICK COLLINSAuthor Commented:
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
Sam JacobsDirector of Technology Development, IPMCommented:
Here is a nice summary of storing/retrieving credentials:
Sam JacobsDirector of Technology Development, IPMCommented:
No need for apologies ... we've all been where you are ... :)
NICK COLLINSAuthor Commented:
I think I am getting there..

Capture.JPG
Sam JacobsDirector of Technology Development, IPMCommented:
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

NICK COLLINSAuthor Commented:
Still getting the same message.. we must be so close
Sam JacobsDirector of Technology Development, IPMCommented:
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?
NICK COLLINSAuthor Commented:
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
NICK COLLINSAuthor Commented:
Found the error message.. username has a full stop in the line
Sam JacobsDirector of Technology Development, IPMCommented:
Not sure what you mean by a 'full stop'  ... Can you send a screenshot of the error?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NICK COLLINSAuthor Commented:
I left a full-stop in the text when the typed the password and I have removed it, it works accordingly

Thanks
Sam JacobsDirector of Technology Development, IPMCommented:
You are most welcome ... glad you got it working.
NICK COLLINSAuthor Commented:
It's a very good process
Sam JacobsDirector of Technology Development, IPMCommented:
thumbs-up1-20pct.png
Sam JacobsDirector of Technology Development, IPMCommented:
In order to send a single email for the day, try this (modifications begin at line #46):
#####################################################
# 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>" 
} else {
	$message = "$($header) <h2>$title</h2> No equipment is due for review today - $($today)"  
}

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

$objExcel.Quit()

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

Open in new window

NICK COLLINSAuthor Commented:
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
Sam JacobsDirector of Technology Development, IPMCommented:
#####################################################
# 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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Bat

From novice to tech pro — start learning today.