VBA to count email Subject names

[Richard Cottrell]

Hi I am really new to VBA and have added a code that offers a list of subjects for all new emails. I would like to add code that counts how many of these subjects have been sent and seperatelty counts how may received


Eg:
Sent Received
Subject 1 =10 = 11
Subject 2 =31 = 6
Subject 3 = 4 = 0
Subject 4 = 6 = 14
Subject 5 = 19 = 7

The code I am using is

Public lstNo As Long
Public Sub ChangeSubject()
Dim objItem As Object
Dim oMail As Outlook.MailItem
Set objItem = GetCurrentItem()
Set oMail = Application.CreateItem(olMailItem)
oMail.Display
UserForm1.Show
' MsgBox "user chose " & lstNo & " from combo"
Select Case lstNo
Case -1
oMail.Subject = objItem.Subject
Case 0
oMail.Subject = "Chase 1"
Case 1
oMail.Subject = "Chase 2"
Case 2
oMail.Subject = "Complaint"
Case 3
oMail.Subject = "Technical Help"
Case 4
oMail.Subject = "Call Back"
End Select
End Sub
Function GetCurrentItem() As Object
Dim objApp As Outlook.Application
Set objApp = Application
On Error Resume Next
Select Case TypeName(objApp.ActiveWindow)
Case "Explorer"
Set GetCurrentItem = objApp.ActiveExplorer.Selection.item(1)
Case "Inspector"
Set GetCurrentItem = objApp.ActiveInspector.CurrentItem
End Select
Set objApp = Nothing
End Function
Yours hopefully

Richard

 

[Diane Poremsky]

Counting how many you sent out could be as simple as tallying the numbers as you use them - you can store the value in memory or, for safe keeping, write it to a text file or the registry. In each Case statement, add something like this (where aSub1 is different for each case).
aSub1 = aSub1 + 1

An example of saving a number in the registry is here - http://www.poremsky.com/office/sequential-numbers-random-character-keywords/ (text file example is linked under more information)

Counting how many were received is more difficult - you could use a run a script macro to check the subject field and add to the tally if one is found or walk the messages in the inbox and count them.