Show meeting attendees in organizational chart

Introduction

In this post I will show how to generate an organisational chart from Outlook, like displayed in the video below:



View the video below for installation instructions:

The Tools and data

• How to download your active directory data with Excel / Power Query.
• Neo4J installation file. . Choose the community edition.
• The apache webserver.

The code

You need code below to import your adress book / active directory data:

Code to load the active directory users in Neo4J:

  
LOAD CSV FROM 'file:///AD.csv' AS line
CREATE (:Person { name: line[0], department: line[1], title: line[2], manager: line[3] })

The code below will create the relationship between person and manager:

  
USING PERIODIC COMMIT 500
LOAD CSV FROM 'file:///AD.csv' AS line
MATCH (p { name: line[0]}),(m { name: line[3]})
CREATE (p)-[:HAS_MANAGER]->(m)

Code for the webpage required script1, and script2.

Code to paste in Outlook.

  


Public Function URLEncode( _
   StringVal As String, _
   Optional SpaceAsPlus As Boolean = False _
) As String

  Dim StringLen As Long: StringLen = Len(StringVal)

  If StringLen > 0 Then
    ReDim result(StringLen) As String
    Dim i As Long, CharCode As Integer
    Dim Char As String, Space As String

    If SpaceAsPlus Then Space = "+" Else Space = "%20"

    For i = 1 To StringLen
      Char = Mid$(StringVal, i, 1)
      CharCode = Asc(Char)
      Select Case CharCode
        Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126
          result(i) = Char
        Case 32
          result(i) = Space
        Case 0 To 15
          result(i) = "%0" & Hex(CharCode)
        Case Else
          result(i) = "%" & Hex(CharCode)
      End Select
    Next i
    URLEncode = Join(result, "")
  End If
End Function

Sub open_webpage()

    Dim objApp As Outlook.Application
    Dim objItem As Object
    Dim objAttendees As Outlook.Recipients
    Dim strNames As String
    Dim chromePath As String
    
    Set objApp = CreateObject("Outlook.Application")
    Set objItem = Outlook.Application.ActiveExplorer.Selection.Item(1)
    Set objAttendees = Outlook.Application.ActiveExplorer.Selection.Item(1).Recipients
    chromePath = """C:\Program Files (x86)\Google\Chrome\Application\chrome.exe"""

    For x = 1 To objAttendees.Count
            strNames = strNames & objAttendees(x).Name & ";"
    Next
    
    strNames = URLEncode(strNames)
    
    Dim stradres As String
    stradres = "http://localhost/orgchart.html?Names=" & strNames
    Shell (chromePath & " -url " & stradres)

    
    Set objApp = Nothing
    Set objItem = Nothing
    Set objAttendees = Nothing

End Sub

First experience with Neo4J

During the DWH and BI summit, I got inspired to look into Graph Databases. A former colleague recommended looking into Neo4J. It turned out to be quite easy to load data in Neo4J and more easily explore related data. In our case, we wanted to analyze our Meta Data Driven DWH Framework. Which had grown quite a bit and became more and more difficult to maintain. We gave the 'Force directed Graph' visualization in Power BI a try, but this was to limited for our analysis goals.

The video below shows the results.

More in Neo4J in the future!