Category Archives: Scripts & Utilities

How to run a VBA macro when new mail is received in Outlook

It can be very useful torun a VBA macro when new mail is received in Outlook. A customer asked me to write something that would log an entry to a SQL database when a contact form was received.

It’s easy to do but can take a bit of trial and error to get working just how you want it.

You need to add an event listener to the Inbox which will process incoming messages. A the following code to ThisOutlookSession:

Option Explicit
Private WithEvents inboxItems As Outlook.Items

Private Sub Application_Startup()
  Dim outlookApp As Outlook.Application
  Dim objectNS As Outlook.NameSpace
  
  Set outlookApp = Outlook.Application
  Set objectNS = outlookApp.GetNamespace("MAPI")
  Set inboxItems = objectNS.GetDefaultFolder(olFolderInbox).Items
End Sub

Private Sub inboxItems_ItemAdd(ByVal Item As Object)
On Error GoTo ErrorHandler

Dim Msg As Outlook.MailItem
Dim MessageInfo
Dim Result
If TypeName(Item) = "MailItem" Then
    MessageInfo = "" & _
        "Sender : " & Item.SenderEmailAddress & vbCrLf & _
        "Sent : " & Item.SentOn & vbCrLf & _
        "Received : " & Item.ReceivedTime & vbCrLf & _
        "Subject : " & Item.Subject & vbCrLf & _
        "Size : " & Item.Size & vbCrLf & _
        "Message Body : " & vbCrLf & Item.Body
    Result = MsgBox(MessageInfo, vbOKOnly, "New Message Received")
End If

ExitNewItem:
    Exit Sub

ErrorHandler:
    MsgBox Err.Number & " - " & Err.Description
    Resume ExitNewItem
End Sub

Screenshot showing the location of where to paste code into ThisOutlookSession to trigger a macro when new mail is received.

You need to restart Outlook for the code to become active.

The above code will produce a simple message box that shows some of the message properties:

Screenshot of VBA Macro being triggered when new mail is received in Outlook

You can of course do whatever you like with the message when it is received. I used it to insert rows into a SQL table, then move the message to a different folder. It works very well.

It’s worth taking a look at all of the available properties of the Outlook mailitem that are available:

https://msdn.microsoft.com/en-gb/library/microsoft.office.interop.outlook.mailitem_properties.aspx

As this customer is on Office 365 I might investigate the possibility of replicating this functionality with Microsoft Flow.

Function to check if a file exists in VBScript

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