Introduction
I had recently got a requirement in which the managers would want to see all the emails in several mailboxes, the agent that is responsible for
each mailbox, emails that came in today, and everything that came in before today.
The main reason for this was to manage the capacity and resources working on those emails. This would include monitoring and escalation of
non-worked emails
if it sits in there for a long time. Well, that’s not part of the development requirement.
For starting with the project, I had to come up with a solution that is fast and comes really close to counting the emails on real time. So there are
two parts we need for this to work.
Using the code
- A Windows based app that would pull/ count emails from the email servers and upload these numbers into an SQL database.
- A webpage that would show these number in a graphical (or any other format) that is required.
There are 30 mailboxes that this app has to go through and get the count from All the email boxes are on an 2010 Exchange server.
First, get a domain ID (username) that has access to all these email boxes.
Set up a SQL database with the below columns
Columns required: view_no (int
), uname (varchar
), co_code (varchar
), pending (int
), ageing (int
).
view_no
is to split the display on the webpage later into 2 sets, so that the data is clearer on a webpage.uname
would be the name of the user that handles that mailbox.co_code
would be the abbreviated name of the email box( or you can keep the full name of the mailbox itself) this is used
as a map from the Windows app to the SQL Server database.pending
would be the number of emails that came in today.ageing
would be the number of emails that are greater than 24 hours.
Fill the view_no, uname and co_code with the relevant data. I.e., if you have 40 mailboxes to retrieve data from then give the
first 20 view_no
as "1" and the rest 20 as "2". If you would want to show all 40 in the same graph or chart or table in the webpage, put "1" for all the 40 mailboxes.
The co_code should be filled with the name that you would specify in the
<DatabaseTable>...</DatabaseTable>
tag in the mapping file
which is EmailSettings.xml which is explained below. uname should be filled with the user's display name.
Creating the Windows app
You would need the Microsoft Exchange Web Services library (EWS) which can be
downloaded from the Microsoft website. Start a new windows project from Visual
Studio. From the Project menu --> Add Reference menu, browse and select the
downloaded "Microsoft.Exchange.WebServices.dll" library.
Next add in the following Imports
statements.
Imports System.Net.Security
Imports System.Security.Cryptography.X509Certificates
Imports Microsoft.Exchange.WebServices
Imports Microsoft.Exchange.WebServices.Data
Imports System Imports System.Data
Imports System.Data.SqlClient
Imports System.Linq
Imports System.Collections.Generic
Imports System.Xml.Linq
Imports System.IO
For a faster approach, go into you project folder, which is normally located in the
"C:\Documents and Settings\USERNAME\My Documents\Visual Studio 2010\Projects\MYMAILPROJECT\MYMAILPROJECT".
Create a bin folder in here and then a debug
folder inside the bin folder, these folders would be created by
VS once you build the project as well. In the bin folder create two xml files as
below:
Configuration.xml (with the below details inside the file):
<Configurations>
<Configuration>
<RefreshInterval>5</RefreshInterval>
</Configuration>
</Configurations>
The number between <RefreshInterval>…</RefreshInterval>
would indicate the interval in minutes that the tool should go through all the email IDs.
It's preferable to put in a 5 minutes or so. You can try with a lower number (anything above 0) and see if the application breaks down if it does, then increase up the time here.
EmailSettings.xml (with the below details inside the file)
<EmailsMapping>
<EmailMap>
<Email>ADDRESS1@MAIL.COM</Email>
<DatabaseTable>Mail1</DatabaseTable>
</EmailMap>
<EmailMap>
<Email>ADDRESS2@MAIL.COM</Email>
<DatabaseTable>MAIL2</DatabaseTable>
</EmailMap>
<EmailMap>
<Email>ADDRESS3@MAIL.COM</Email>
<DatabaseTable>MAIL3</DatabaseTable>
</EmailMap>
</EmailsMapping>
Fill the above with as many email address as you want with each email ID inside the
<Email>...</Email>
tag and their relevant name in the SQL database
inside the <DatabaseTable>…</DatabaseTable>
tag. That's it, you're done with letting the app know the email ids that are required.
Now back to creating the Windows app. There should be a subroutine that lets the app get the email list from the XML file that should be processed.
Private Sub LoadEmailList()
If File.Exists(Application.StartupPath + "\EmailSettings.xml") Then
Dim xdoc As XDocument = XDocument.Load(Application.StartupPath + "\EmailSettings.xml")
Dim mapping = From map In xdoc.Descendants("EmailMap")
Select New With { _
.EmailAddress = map.Element("Email").Value, _
.DatabaseTable = map.Element("DatabaseTable").Value _
}
ReDim mailboxes(mapping.Count - 1, 2)
For m As Integer = 0 To mapping.Count() - 1
mailboxes(m, 0) = mapping(m).EmailAddress.Trim
mailboxes(m, 1) = mapping(m).DatabaseTable.Trim
Next
End If
End Sub
The above code uses LINQ. Each mailbox is taken along with its mapping name in the sql and loaded into an array, i.e., in this case ReDim mailboxes
.
The next sub is to get the interval time for the timer to execute.
Private Sub LoadConfiguration()
If File.Exists(Application.StartupPath + "\Configuration.xml") Then
Dim xdoc As XDocument = XDocument.Load(Application.StartupPath + "\Configuration.xml")
Dim config = From con In xdoc.Descendants("Configuration")
Select New With { _
.RefreshInterval = con.Element("RefreshInterval").Value}
For Each c As Object In config
Timer1.Interval = (c.RefreshInterval * 60 * 1000)
Next
End If
End Sub
As you can see, the value mentioned in the <RefreshInterval>
is taken and converted to milliseconds (c.RefreshInterval * 60 * 1000
) as VS timer accepts
interval as milliseconds.
In the page load section call these two subs.
Private Sub Form1_Load(sender As Object, e As System.EventArgs) Handles Me.Load
LoadEmailList()
LoadConfiguration()
End Sub
We are getting somewhere now.
For the UI of the Form itself, it would look something like the below, or you can throw in your creativity.
The controls and their names as used in the code behind file are mentioned in the image.
-
UID
,
PWD
, and
Error List
are just labels that would not be used in the code as such. -
lbl_status
would show messages like
“Process Busy”, “Completed” etc -
lbl_runtime
would show the total
times the app has executed and there by went through a full cycle of obtaining
the info on the emails and their count and updating them in the sql database.
-
txt_uid
would be the user name of the
Domain user that has access to all the other email boxes that would be searched
through. -
txt_pwd
, well the password of the
above user. -
txt_details
would show the name of
the email box that is currently being read and the number of mails in it.
This is just to cross check and see if the app is running. -
txt_err
shows any error messages that
might occur during runtime
- Add a
Timer
control and set
Enabled
to
False
- Add a
BackgroundWorker
control and set the WorkerReportsProgress
and
WorkerSupportsCancellation
to True
.
Back to the code-behind file.
Declare these variables:
Public Class Form1
Dim sqlins As String
Dim dbconn As New SqlConnection
Dim dbcomm As SqlCommand
Dim dbpath1 As String = "Server=SERVERNAME;Database=DB_NAME;Trusted_Connection=True;"
Dim run_time As Integer
Dim mailboxes(,) As String
If you are using SQLEXPRESS then the dbpath1
should be as below:
Dim dbpath1 As String = "Server=SERVERNAME\SQLEXPRESS;Database=DB_NAME;Trusted_Connection=True;"
We would be using the
Backgroundworker
control to run the main part of the code.
The
Backgroundworker
cannot access the labels or textboxes of the Form directly,
for this, you should use delegation as below.
Delegate Sub SetLabelText_Delegate(ByVal [Label] As Label, ByVal [text] As String)
Private Sub SetLabelText_ThreadSafe(ByVal [Label] As Label, ByVal [text] As String)
If [Label].InvokeRequired Then
Dim MyDelegate As New SetLabelText_Delegate(AddressOf SetLabelText_ThreadSafe)
Me.Invoke(MyDelegate, New Object() {[Label], [text]})
Else
[Label].Text = [text]
End If
End Sub
The above code is to delegate text to the label controls. The below code is to delegate text to the textbox controls.
Delegate Sub SettextboxText_Delegate(ByVal [Textbox] As TextBox, ByVal [text] As String)
Private Sub SettextboxText_ThreadSafe(ByVal [Textbox] As TextBox, ByVal [text] As String)
If [Textbox].InvokeRequired Then
Dim MyDelegate1 As New SettextboxText_Delegate(AddressOf SettextboxText_ThreadSafe)
Me.Invoke(MyDelegate1, New Object() {Textbox, [text]})
Else
[Textbox].Text = [text]
End If
End Sub
Next, add in the function for the Clear button.
Private Sub btn_clear_Click(sender As System.Object, e As System.EventArgs) Handles btn_clear.Click
txt_details.Clear()
txt_err.Clear()
lbl_status.Text = ""
lbl_runtime.Text = ""
End Sub
Then codes for the Start and Stop buttons.
‘////STOP BUTTON
Private Sub btn_stop_Click(sender As System.Object, e As System.EventArgs) Handles btn_stop.Click
Timer1.Stop()
If BackgroundWorker1.IsBusy Then
If BackgroundWorker1.WorkerSupportsCancellation Then
BackgroundWorker1.CancelAsync()
Timer1.Stop()
End If
End If
Me.btn_start.Enabled = True
Me.btn_stop.Enabled = False
End Sub
‘////START BUTTON
Private Sub btn_start_Click(sender As Object, e As System.EventArgs) Handles btn_start.Click
Me.btn_start.Enabled = False
Me.lbl_runtime.Text = 0
Me.btn_stop.Enabled = True
txt_err.Text = ""
Timer1.Enabled = True
Timer1.Start()
BackgroundWorker1.RunWorkerAsync()
End Sub
For the next part of the code to work make sure that WorkerReportsProgress
and
WorkerSupportsCancellation
of the BackgroundWorker
are set to
True
.
Private Sub BackgroundWorker1_RunWorkerCompleted(sender As Object, _
e As System.ComponentModel.RunWorkerCompletedEventArgs) _
Handles BackgroundWorker1.RunWorkerCompleted
If e.Cancelled Then
Me.lbl_status.Text = "Cancelled"
Else
Me.lbl_status.Text = "Completed"
If Not dbconn.State = ConnectionState.Closed Then
dbconn.Close()
End If
End If
End Sub
For the above, you can build up something to update a progress bar as well. This is not covered in this article.
Now for the main part of the code that would do all the work, the BackgroundWorker_DoWork
.
Private Sub BackgroundWorker1_DoWork(sender As Object, _
e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork
If BackgroundWorker1.CancellationPending Then
e.Cancel = True
Exit Sub
End If
SetLabelText_ThreadSafe(Me.lbl_status, "...Started..")
Try
dbconn = New SqlConnection(dbpath1)
dbconn.Open()
Catch ex As Exception
SettextboxText_ThreadSafe(Me.txt_err, ex.Message.ToString & " A")
End Try
Dim myexchange As New ExchangeService(ExchangeVersion.Exchange2010)
Dim myversion As New ExchangeVersion
myexchange.Credentials = New NetworkCredential(txt_uid.Text, txt_pwd.Text)
myexchange.Url = New Uri("https://SERVER.DOMAIN.COM/EWS/Exchange.asmx")
Dim inboxfolder As New Folder(myexchange)
Dim mb As Mailbox
Dim fid1 As FolderId
SettextboxText_ThreadSafe(txt_details, "")
Dim bound0 As Integer = mailboxes.GetUpperBound(0)
Dim bound1 As Integer = mailboxes.GetUpperBound(1)
Dim emailid As String
Dim cntryname As String
For i As Integer = 0 To bound0
emailid = mailboxes(i, 0)
cntryname = mailboxes(i, 1)
mb = New Mailbox(emailid)
Dim iv_old As ItemView = New ItemView(999)
Dim oldmail As Integer
Dim newmail As Integer
Dim filters_old As SearchFilter = New SearchFilter.IsLessThan(_
ItemSchema.DateTimeReceived, DateTime.Today.AddHours(-24))
Dim olditems As FindItemsResults(Of Item) = Nothing
Try
fid1 = New FolderId(WellKnownFolderName.Inbox, mb.Address)
inboxfolder = folder.Bind(myexchange, fid1)
iv_old.Traversal = ItemTraversal.Shallow
olditems = myexchange.FindItems(inboxfolder.Id, filters_old, iv_old)
oldmail = olditems.Items.Count
iv_old = Nothing
sqlins = "UPDATE tbl_TABLENAME SET ageing = " & oldmail & " WHERE co_code = '" & cntryname & "'"
dbcomm = New SqlCommand(sqlins, dbconn)
dbcomm.ExecuteNonQuery()
Catch ex As Exception
SettextboxText_ThreadSafe(Me.txt_err, Me.txt_err.Text & _
ex.Message.ToString & cntryname & " : C " & "| TRYING AGAIN" & vbNewLine)
Finally
End Try
Dim iv_new As ItemView = New ItemView(999)
iv_new.Traversal = ItemTraversal.Shallow
Dim filters_new As SearchFilter = _
New SearchFilter.IsGreaterThanOrEqualTo(ItemSchema.DateTimeReceived, DateTime.Today.AddHours(-24))
Dim newitems As FindItemsResults(Of Item) = Nothing
newitems = myexchange.FindItems(inboxfolder.Id, filters_new, iv_new)
newmail = newitems.Items.Count
iv_new = Nothing
Try
sqlins = "UPDATE tbl_TABLENAME SET pending = " & newmail & " WHERE co_code = '" & cntryname & "'"
dbcomm = New SqlCommand(sqlins, dbconn)
dbcomm.ExecuteNonQuery()
Catch ex As Exception
SettextboxText_ThreadSafe(Me.txt_err, Me.txt_err.Text & _
ex.Message.ToString & cntryname & " : C " & "| TRYING AGAIN" & vbNewLine)
Finally
End Try
SettextboxText_ThreadSafe(Me.txt_details, txt_details.Text + emailid + " : " + _
cntryname + " : " + newmail.ToString + " : " + oldmail.ToString + vbNewLine)
Next
mb = vbNull
inboxfolder = Nothing
fid1 = Nothing
If dbconn.State = ConnectionState.Open Then
dbconn.Close()
End If
SetLabelText_ThreadSafe(Me.lbl_runtime, lbl_runtime.Text + 1)
End Sub
In the SearchFilter
mentioned above you can put in your own search criteria.
The code is very basic so please modify it to suit your needs. Displaying it on a webpage is fairly simple, however,
if any one would require it please let me know, that can be the Part 2 of this article.
Points of interest
During the initial stages, I did get into trouble trying to get EWS to work properly. There is always a missing factor, either Permissions or some wrong
usage of code or in one case the email server was down and I was breaking my head thinking it was the problem with the coding. So have a coffee while any of
these things happen, sit back and relax.
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.