In/Out Board for Outlook?

andrew_transparent
andrew_transparent used Ask the Experts™
on
I'm looking for a some type of an in/out board that can be integrated with Office 2003 (Outlook).
Does anyone know of any?

or a small utility that you know could help too.

Any suggestions would help.

thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
in/out board?  Can you explain what you mean?

Chris

Author

Commented:
we want to be able to know which users are in the office and not in the office.
so an in and out board either on outlook or a small utility on their computers that they just click or enable whenever they are in or out of the office.

any suggestions?
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
I presume all the users are on exchange and if so perhaps there is a capability therein.  If you are on exchange then try using the request attention button to get the exchange server zone added ... let them know which version of exchange and since it's potentially specific functionality it won't harm to post your outlook/exchange version(s) here as well

Chris
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
yes, they are all exchange users. Running on Small Business server, Exchange 2003.
Using a mixed of Outlook 2003 and 2010 (mostly 2003 though).

Top Expert 2010

Commented:
Hi, andrew_transparent.

How do you envision this working?  Should a person be marked as "in" as soon as they login to the computer, when they launch Outlook, or should they have to take some conscious action to mark themselves as "in"?  Same question for "out".  
I dont think this is possible when using the software Outlook in Office it does not have the capabilities to program who is using it and who is not using it.
The closest match to your requests is a web board.
But that's not what you want.
You didnt not posted the OS version so at a base level
To see who is logged onto the server or your network use the CMD prompt > NBSTAT
http://www.windowsnetworking.com/kbase/WindowsTips/WindowsNT/AdminTips/Accounts/Nbtstatrevealswhoisloggedon.html
and this one to find out the users logged in using the cmd line from systernals
PsLoggedOn v1.33
http://social.technet.microsoft.com/Forums/en-US/winserverDS/thread/82118eeb-90fd-4f99-b6fe-7a95951d75ac


Most Valuable Expert 2011
Top Expert 2011

Commented:
Microsoft Office Communicator works well, integrates with Exchange and shows free/busy status, and if they are logged on to the PC/Idle.....

Author

Commented:
well, if there is something for Outlook, that would be the best option.
But just like i said, if there is a small utility that we can install on their computers, i can check it out too..

as for setting if they are in or out of the office, we're looking to have it so that they can set it to IN or OUT.
But if there is an option to set it automatically when they login to their OFFICE computers, then that works as well.

Author

Commented:
Office Communicator requires the server-side software and as well as the workstation-side, am i correct?

If so, this wouldn't work as having to install another software on the server means using up more of its resources, we don't want to setup another server just for this solution..
Top Expert 2010

Commented:
Yes, Office Communicator requires Office Communications Server.  

Do you have a web server?  If so, does it support Microsoft Active Server Pages?  If not, is there a shared folder that everyone has, or can be given, access to?

Author

Commented:
we just have a SBS server and a Terminal server.
But dont want to start adding software on both server that eats up resources..

Also, im pretty sure Office Communications requires additional licenses?

Yes, we have a shared folder on our server...
Top Expert 2010

Commented:
I can do this with an Access database and simple bit of scripting.  If that's an option, then I can put the script and database together and post them along with instructions.

Author

Commented:
sure, i can look into that..
Top Expert 2010
Commented:
This solution comes in three parts.  This is part #1.

Follow these instructions to add the code to Outlook.

1.  Save the attached database to a shared folder that everyone has access to
2.  Start Outlook
3.  Click Tools > Macro > Visual Basic Editor
4.  If not already expanded, expand Microsoft Office Outlook Objects
5.  Right-click on Class Modules, select Insert > Class Module
6.  In the Properties panel click on Name and enter SimpleIOB
7.  Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook's VB Editor window
8.  Edit the code as needed.  I included comments wherever something needs to or can change
9.  Click the diskette icon on the toolbar to save the changes
10. Close the VB Editor


'On the next line change the path to and name of the database'
Const DB_PATH = "C:\eeTesting\OutlookIOB.mdb"
'On the next line change the path to and name of the output HTML file'
Const HTML_PATH = "C:\eeTesting\OutlookIOB.html"

Private strUser As String
Private adoCon As ADODB.Connection

Private Sub Class_Initialize()
    strUser = Outlook.Session.CurrentUser
    Set adoCon = CreateObject("ADODB.Connection")
    adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DB_PATH & ";Persist Security Info=False"
End Sub

Private Sub Class_Terminate()
    adoCon.Close
    Set adoCon = Nothing
End Sub

Private Sub Export2HTML()
    Dim adoRec As ADODB.Recordset, objFSO As FileSystemObject, objFile As TextStream, strBuffer As String
    Set adoRec = adoCon.Execute("SELECT * FROM Presence ORDER BY EmpName")
    Do Until adoRec.EOF
        With adoRec
            strBuffer = strBuffer & "<tr><td>" & .Fields("EmpName") & "</td><td>" & .Fields("EmpStatus") & "</td><td>" & .Fields("EmpStatusDate") & "</td></tr>"
            .MoveNext
        End With
    Loop
    adoRec.Close
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile(HTML_PATH, ForWriting, True)
    objFile.Write "<table><tr><td width=""25%""><b>Name</b></td><td width=""15%""><b>Status</b></td><td><b>Checkin Time</b></td></tr>" & strBuffer & "</table>"
    objFile.Close
    Set objFile = Nothing
    Set objFSO = Nothing
    Set adoRec = Nothing
End Sub

Public Sub ChangeStatus(strStatus As String)
    Dim adoRec As ADODB.Recordset, strSQL As String
    Set adoRec = adoCon.Execute("SELECT * FROM Presence WHERE EmpName='" & strUser & "'")
    If adoRec.BOF Or adoRec.EOF Then
        strSQL = "INSERT INTO Presence (EmpName,EmpStatus,EmpStatusDate) VALUES('" & strUser & "','" & strStatus & "','" & Now & "')"
    Else
        strSQL = "UPDATE Presence SET EmpStatus='" & strStatus & "', EmpStatusDate='" & Now & "' WHERE EmpName='" & strUser & "'"
    End If
    adoCon.Execute strSQL
    adoRec.Close
    Set adoRec = Nothing
    Export2HTML
End Sub

Open in new window

OutlookIOB.mdb
Top Expert 2010
Commented:
This is part #2.

Follow these instructions to add the code to Outlook.

1.  Start Outlook
2.  Click Tools > Macro > Visual Basic Editor
3.  If not already expanded, expand Microsoft Office Outlook Objects and click on ThisOutlookSession
4.  Copy the code from the Code Snippet box and paste it into the right-hand pane of
5.  Outlook's VB Editor window
6.  Edit the code as needed.  I included comment lines wherever something needs to or can change
7.  Click the diskette icon on the toolbar to save the changes
8.  Close the VB Editor
9.  Click Tools > Macro > Security
10. Set the Security Level to Medium
11. Close Outlook
12. Start Outlook
13. Outlook will display a dialog-box warning that ThisOutlookSession contains macros and asking if you want to allow them to run.  Say yes.

Dim objIOB As Object

Private Sub Application_Quit()
    objIOB.ChangeStatus "Out"
    Set objIOB = Nothing
End Sub

Private Sub Application_Startup()
    Set objIOB = New SimpleIOB
    objIOB.ChangeStatus "In"
End Sub

Sub MarkMeIn()
    objIOB.ChangeStatus "In"
End Sub

Sub MarkMeOut()
    objIOB.ChangeStatus "Out"
End Sub

Open in new window

Top Expert 2010
Commented:
This is part #3.

1.  Create a new mail folder.  I recommend something like "In/Out Board", but the name is up to you.
2.  Right-click the new folder and select Properties.
3.  Click the "Home Page" tab.
4.  In the "Address" field enter the path to the HTML file from line #4 of the code in part #1.
5.  Check the "Show home page by default for this folder" box
6.  Click OK.

You must add the code to and create the folder on each person's copy of Outlook.  You can also create toolbar buttons that the staff can use to sign in and out without closing and reopening Outlook.  Open the In/Out Board folder whenever you want to check the status of an employee.

This is a very simple solution.  When a person signs in/out the code writes a status update to the database and creates a new HTML that reflects everyone's status.  When you navigate to the In/Out Board folder Outlook displays the HTML file.  
SimpleIOB.jpg

Author

Commented:
cool, i'll try it out.

questions:
1. does this code need to be entered on each user profiles on a computer? i.e. multiple users using the computer, each of them have their outlook profile.
2. does the database grow fast?
3. does this work with any version of outlook?

thanks!

Author

Commented:
ok, i've followed the steps and wasn't able to get it to work..

using Outlook 2007.

error when I closed Outlook:
Runtime error 91

object variable or with block variable not set
Debug points to this line of code: objIOB.ChangeStatus "Out"

error when opening Outlook, after enabling macros:
Compile error
user-defined type not defined
Debug points to this line of code: Private adoCon As ADODB.Connection


Also, do i need to create the html file? or will it create it automatically once in/out script is working..

thanks.


Top Expert 2010

Commented:
1. does this code need to be entered on each user profiles on a computer?
Yes

2. does the database grow fast?
The database shouldn't grow at all once each user has a record in it.  When a user first signs in the code creates a record for them.  All subsequent uses update that record.  It doesn't add a record for each sign in/out.  If you have 50 employees, then there'll be 50 records.  Each record is fairly small, consiting of just three fields: name, status, date/time of last udpate.

3. does this work with any version of outlook?
It should.  there's nothing Outlook specific about the code.
Top Expert 2010

Commented:
My fault.  I forgot to change those references.  I'll fix the code and repost in a minute.

No, you don't need to create the HTML file.  The code does it for you after each update.
Top Expert 2010

Commented:
Replace the code from part #1 with the code below.  I removed all the offending references.
'On the next line change the path to and name of the database'
Const DB_PATH = "C:\eeTesting\OutlookIOB.mdb"
'On the next line change the path to and name of the output HTML file'
Const HTML_PATH = "C:\eeTesting\OutlookIOB.html"

Private strUser As String
Private adoCon As Object

Private Sub Class_Initialize()
    strUser = Outlook.Session.CurrentUser
    Set adoCon = CreateObject("ADODB.Connection")
    adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DB_PATH & ";Persist Security Info=False"
End Sub

Private Sub Class_Terminate()
    adoCon.Close
    Set adoCon = Nothing
End Sub

Private Sub Export2HTML()
    Dim adoRec As Object, objFSO As Object, objFile As Object, strBuffer As String
    Set adoRec = adoCon.Execute("SELECT * FROM Presence ORDER BY EmpName")
    Do Until adoRec.EOF
        With adoRec
            strBuffer = strBuffer & "<tr><td>" & .Fields("EmpName") & "</td><td>" & .Fields("EmpStatus") & "</td><td>" & .Fields("EmpStatusDate") & "</td></tr>"
            .MoveNext
        End With
    Loop
    adoRec.Close
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile(HTML_PATH, ForWriting, True)
    objFile.Write "<table><tr><td width=""25%""><b>Name</b></td><td width=""15%""><b>Status</b></td><td><b>Checkin Time</b></td></tr>" & strBuffer & "</table>"
    objFile.Close
    Set objFile = Nothing
    Set objFSO = Nothing
    Set adoRec = Nothing
End Sub

Public Sub ChangeStatus(strStatus As String)
    Dim adoRec As Object, strSQL As String
    Set adoRec = adoCon.Execute("SELECT * FROM Presence WHERE EmpName='" & strUser & "'")
    If adoRec.BOF Or adoRec.EOF Then
        strSQL = "INSERT INTO Presence (EmpName,EmpStatus,EmpStatusDate) VALUES('" & strUser & "','" & strStatus & "','" & Now & "')"
    Else
        strSQL = "UPDATE Presence SET EmpStatus='" & strStatus & "', EmpStatusDate='" & Now & "' WHERE EmpName='" & strUser & "'"
    End If
    adoCon.Execute strSQL
    adoRec.Close
    Set adoRec = Nothing
    Export2HTML
End Sub

Open in new window

Author

Commented:
Getting another error after enabling macros..

Run-time error '5"
Invalid procedure call or argument

Debug points to "objIOB.ChangeStatus "In"

On the ThisOUtlookSession module
Private Sub Application_startup()
Set objIOB = New SimpleIOB
Top Expert 2010

Commented:
Can't imagine how that's possible.  Not doubting that it's happening, but I can't see how or why.  I tested the code before posting and didn't have any errors.  When the run-time error dialog-box appears click Debug.  When the code window appears press F8.  Does that take you to line 41 of the code in the class module?  If yes, keep pressing F8 to run the highlighted line of code and move to the next line.  Tell me which line kicks you back to ThisOutlookSession.

Author

Commented:
i'm not getting any errors now, but i don't see any status on the html file (on the outlook folder).
its just blank.. any ideas?
Top Expert 2010

Commented:
Is there an HTML file in the path you specified with the name you set the code to use?  Is the code firing at all?  The best way to check that is to set a few breakpoints.  If they're triggered, then the code is firing.  If they don't trigger, then the code's not running at all.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial