Tachytelic.net

VBScript

VBScript lpad and rpad functions

by Leave a Comment

VBScript does not include any functions to left pad or right pad a string, so these two functions will enable you to do just that.

Function LPad(StringToPad, Length, CharacterToPad)
  Dim x : x = 0
  If Length > Len(StringToPad) Then x = Length - len(StringToPad)
  LPad = String(x, CharacterToPad) & StringToPad
End Function

Function RPad(StringToPad, Length, CharacterToPad)
  Dim x : x = 0
  If Length > Len(StringToPad) Then x = Length - len(StringToPad)
  RPad = StringToPad & String(x, CharacterToPad)
End Function

Add a Leading Zero

Left pads the number four to a length of two with a zero:
LPad("4", 2, "0")

Add a Trailing Zero

Right pads the number four to a length of two with a zero:
RPad("4", 2, "0")

Left Pad with spaces

Left pads a string with spaces to a length of ten:
LPad("Hello ", 10, " ")

Right Pad with spaces

Right pads a string with spaces to a length of ten:
RPad("hello", 10, " ")

It is a shame there isn’t a native way to pad with VBScript. But these small functions make it simple. I’ve also written up how to do padding with Powershell, which makes things much easier.

Use CDO to send email from VBScript or VBA through Office 365

by Leave a Comment

Sending email via VBScript or VBA using CDO is easy to do, but the correct configuration to relay through Office 365 is confusing to say the least and it took me me a while to find the correct settings.

I knew from configuring other devices and software that the preferred way to setup SMTP to relay to Office 365 was to use TLS on port 587. The problem is that officially CDO does not support TLS, but unofficially it does. So I tried in vain to develop some code that would send email via smtp.office365.com using TLS and always came up with the following error:

The server rejected the sender address. The server response was: 530 5.7.57 SMTP; Client was not authenticated to send anonymous mail during MAIL FROM

So clearly the TLS support in CDO is not sufficient to be able to work with the Office 365 SMTP Server. The trick is not to use TLS at all, but to use SSL instead on port 25 instead, which seems to work fine:

VBScript to Send Email via Office 365 (smtp.office365.com)

This is just quick sample code to get you on the right path:

Dim objMessage, objConfig, Fields
Set objMessage = CreateObject("CDO.Message") 
Set objConfig = CreateObject("CDO.Configuration")
Set Fields = objConfig.Fields
With Fields
  .Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 
  .Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.office365.com"
  .Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
  .Item ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
  .Item ("http://schemas.microsoft.com/cdo/configuration/sendusername") = "[email protected]"
  .Item ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "Office365Password"
  '.Item("http://schemas.microsoft.com/cdo/configuration/sendtls") = True
  .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
  .Update
End With
Set objMessage.Configuration = objConfig

With objMessage
  .Subject = "Test Message"
  .From = "[email protected]"
  .To = "[email protected]"
  .HTMLBody = "Test Mesage"
end With
objMessage.Send

VBA to send email via Office 365 (smtp.office365.com)

The code for VBA is almost the same as VBScript and I tested it using Excel 2016 without any problems at all. But first you need to add a reference to your project:

  1. In the Office VBA Code Editor go to Tools, and then References:
    Image showing how to add references to an Office VBA Project
  2. In the References dialog box add a reference to the “Microsoft CDO for Windows 2000 Library”:
    Image showing how to add the Microsoft CDO Library to the Office VBA Environment
  3. Use the following sample code….
Sub Office365_Email_Test()
    Dim objMessage, objConfig, fields
    Set objMessage = New CDO.Message
    Set objConfig = New CDO.Configuration
    Set fields = objConfig.fields
    With fields
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.office365.com"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "[email protected]"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "Office365Password"
        '.Item("http://schemas.microsoft.com/cdo/configuration/sendtls") = True
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
        .Update
    End With
    Set objMessage.Configuration = objConfig
    
    With objMessage
        .Subject = "Test Message"
        .From = "[email protected]"
        .To = "[email protected]"
        .HTMLBody = "Test Message"
    End With
    objMessage.Send
End Sub

A few things to note:

  • The account that you use must have at least an Exchange Online license.
  • You will only be able to send from addresses that account has send as permission for or an alias of that account.
  • It would probably be a good idea to use an account dedicated for sending SMTP because the password is being stored in clear text.
  • Your Firewall or your ISPs Firewall may block outbound port 25.
  • I’ve left the code for TLS in-place above but commented out, in case anyone else wants to have a play with it.

I hope this helps, I spent ages trying to relay through Office 365 over TLS with VBA, but SSL works just fine.

 

Function to check if a file exists in VBScript

by Leave a Comment

It’s very easy to check if a file exists in VBScript, but to make this common task even easier it’s best to use a quick function to do the job. I’ve written two functions, one using the FilesystemObject and another using WMI, both of which return a Boolean.

VBScript Function to check if a file exists on the local computer

Function FileExists(FilePath)
  Set fso = CreateObject("Scripting.FileSystemObject")
  If fso.FileExists(FilePath) Then
    FileExists=CBool(1)
  Else
    FileExists=CBool(0)
  End If
End Function

To use the function just do something like the following:

If FileExists("c:\testfolder\testfile.txt") Then
  WScript.Echo "Does Exist"
Else
  WScript.Echo "Does not exist"
End If

This will work perfectly if you are only looking for files on your local machine. But what if you want to check if a file exists on a remote machine?

VBScript Function to check if a file exists on a remote machine

Function FileExists(FilePath, Computer)
  Set objWMIService = GetObject("winmgmts:\\" & Computer & "\root\cimv2")
  FilePath = Replace(FilePath, "\", "\\")
  Set Files = objWMIService.ExecQuery ("Select * From CIM_Datafile Where Name = '" & FilePath & "'")
  
  If Files.Count > 0 Then
      FileExists=CBool(1)
  Else
      FileExists=CBool(0)
  End If
End Function

Use the function like this:

If FileExists("c:\testfolder\testfile.txt", "testcomputer") Then
  WScript.Echo "Does Exist"
Else
  WScript.Echo "Does not exist"
End If

Now that you know how to check for a file in VBScript, read up on how to check if a file exists with Powershell.

If you found this post helpful, I’d really appreciate it if you left a rating.

Script to generate email alerts from Dell Openmanage (OMSA)

by 201 Comments

Dell Openmanage Server Administrator is a really useful tool for configuring and monitoring server hardware but it lacks built in E-Mail alerts.

It is capable of executing  scripts when a server alert is triggered so I have written a simple script in VBScript that provides the missing E-Mail alert functionality.

Updated version of this script

Many people land on this page, while the script and instructions on this page still work, there is an updated post with new information, please see this post:

https://www.tachytelic.net/2011/09/dell-poweredge-email-alerts/

How to configure email alerts from Dell Open Manage

  1. Download the script
  2. Extract the contents of the zip file to a folder on your machine
  3. Edit the top of the script to make the SMTP settings appropriate for your environment:

    Image showing how to reconfigure Dell Alert VBScript
    If a remote SMTP server is not specified the script will try to use the local machine to send the E-Mail.
  4. Test the email functionality by running:
    cscript dellalert.vbs testemail
  5. Setup the alerts by running:
    cscript dellalert.vbs setup
  6. Once this is done you should be able to see that the script has been added into OMSA:

Now you need to generate some real alerts to test the script properly. How you do this will depend on the capabilities of your server, I was able to disconnect the redundant power supply and have an alert generated almost immediately. Opening the case will also generate an event on some servers. Hopefully you should get something like this in your Inbox:

Let me know how you get on and leave any questions in the comments area.

Windows update automatic e-mail notification

by 45 Comments

This script, will examine the machine it is running on and send an email report of all the windows updates that are available for installation.

The script sends e-mail notifications providing details of which Windows Updates are available for installation.  You can choose which of the five patch levels will trigger an e-mail alert:

  • Critical
  • Important
  • Moderate
  • Low
  • Unclassified

I would suggest that you at least choose Critical, Important and Unclassified. For some reason Microsoft do not mark all updates with a severity, but the “Unclassified” category seems to contain a lot of what I would consider to be important updates. My thanks to one of the guys that commented for pointing this out.

If there are no outstanding patches at the appropriate alert levels to be installed then the script will quit without sending an e-mail.

The script can be run manually or as a scheduled task. The report includes links to the relevant KB articles and further information made available by Microsoft.

Image showing Windows Update Email Notification

 

Windows update email notification script configuration

The script is very quick to setup and the most complicated part will likely be your SMTP configuration. At the top of the script you will see a number of variables:

Windows Update Alert Levels

First of all you should configure what severity of Windows update will trigger an email alert. These range from critical to low. Setting each value to 1 or 0 will enable or disable alerts for that category. As mentioned above some updates do not have any severity assigned. These seem to be things like Windows Defender definitions or updates to the malicious software removal tool.

I would recommend at least having Critical, Import and and Unclassified set to 1.

Configure email settings

Next you need to configure your SMTP settings:
Image showing SMTP Configuration for Windows Update Notifications

If you leave the SMTP server empty, the script will attempt to use the local machine to send the email. I suspect most people will use an internal or external relay, so configure it as per your environment. You can use SMTP over SSL by setting the SMTP_UseSSL variable to 1.

Testing Windows Updates Notification Emails

  1. Download the Script:
    Windows Update Email Notification Script
  2. Extract the contents to a folder on your server and then setup the variables to suit your preferences and environment
  3. Test the script
    1. Go into a command prompt
    2. Change directory to the location where you saved the script.
    3. Run:
      cscript winupdates.vbs
    4. Check your email

Once you are happy that the script is reporting correctly, setup a Windows Scheduled Task to do this automatically. Assuming an installation directory of “c:\scripts” the scheduled task command should look something like this:

C:\WINDOWS\system32\cscript.exe c:\scripts\winupdates.vbs

The script uses the Windows Update Agent API Com interface, which is quite interesting in itself.

Sample Email Configurations

EmailFrom 		= "[email protected]"
EmailTo 		= "[email protected]"
'If SMTP Server is left empty it will try to use the local SMTP Server without auth
SMTP_Server		= "smtp.office365.com"
SMTP_Port		= "25"
SMTP_User		= "[email protected]"
SMTP_Pass		= "YourOffice365Password"
'Set this variable to 1 to enable SMTP over SSL
SMTP_UseSSL 	= "1"
EmailFrom 		= "[email protected]"
EmailTo 		= "[email protected]"
'If SMTP Server is left empty it will try to use the local SMTP Server without auth
SMTP_Server		= "smtp.gmail.com"
SMTP_Port		= "465"
SMTP_User		= "[email protected]"
SMTP_Pass		= "gmailPassword"
'Set this variable to 1 to enable SMTP over SSL
SMTP_UseSSL 	= "1"
EmailFrom 		= "[email protected]"
EmailTo 		= "[email protected]"
'If SMTP Server is left empty it will try to use the local SMTP Server without auth
SMTP_Server		= "192.168.250.249"
SMTP_Port		= "25"
SMTP_User		= ""
SMTP_Pass		= ""
'Set this variable to 1 to enable SMTP over SSL
SMTP_UseSSL 	= "0"

 

I originally wrote this script in 2007 and noticed a lot of people were still downloading and using it. So this evening (December 2019), while my wife was at her work Christmas party I decided to see if it still worked and address some of the points made in the comments. I didn’t have a huge amount of time to spend on it, but found time to clean up the code a bit, enhanced the email options and added the functionality to include updates that do not have any severity assigned. It was tested by me on Windows 2008, 2012, 2016 and 2019 and they all worked!

I am glad people are still fining it useful more than 10 years after I wrote the original version!