Count First Instance Of a Name

[hmltnangel]

Ok, I should never have started playing with VBA - now everyone comes to me for things ....

Normally excel is where Ido my VBA but now I need some help with Outlook.

We use a team mailbox where a number of users send emails out to other areas each day. The emails do not have a not of who sent them - they all show as being sent by the mailbox name.

I therefore need a piece of VBA, that will check the emails in a particular folder and count the first instance of a range of names in that folder.

Problem is - the emails may have come back and forward a number of times in their history but I just need numbers of the last person to deal with that particular email in my team.

Can this be done?

[westconn1]

that will check the emails in a particular folder and count the first instance of a range of names in that folder.please explain with more detail


Can this be done?most likely

[hmltnangel]

Ok,

an email will be sent from this mailbox by one of 5 memvbers of staff. This email might go back and forward a few times between external people (these are of no concern) but it might be dealt with by a different one of the 5 staff members each time it gets sent from here.

What I need is a quick way of ascertaining in a given folder, how many were last dealt with by Jim, how many by Ed and so on. It needs to be the last person who dealt with it though and not count each instance of a name within the email. hence I wanted it to check the email body and find the first instance when one of a range of names appears in it.

[Aussiebear]

Would the date be a simple way of determining when the email was last handled?

[westconn1]

you can try like

Dim pos() As Integer
listofnames = Array("Fred", "Jim", "Syd") ' get array of names from whereever
ReDim pos(UBound(listofnames))
Set folderitems = ActiveExplorer.CurrentFolder.Items ' change folder to suit, if it not the active one
For Each mitem In folderitems
strt = Len(mitem.body)
For i = 0 To UBound(pos)
pos(i) = InStrRev(mitem.Body, listofnames(i), strt)
If pos(i) > 0 Then strt = pos(i): firstfnd = listofnames(i)
Next
MsgBox "first name in email " & mitem.subject & " is " & firstfound 'for testing only, you could add each to an array for later processing
Nextthis is untested and may contain errors or produce wrong results, so test thoroughly

[hmltnangel]

Its good that I'm starting tounderstand the language of this now.

This works ok, yes it needs a bit of tweaking but thats fine.

Is there anyway of having it total each name for you rather than displaying a msgbox for each email - an individual msgbox would be tedious if there were 200 emails.

[westconn1]

Dim pos() As Integer , cnt() as integer
listofnames = Array("Fred", "Jim", "Syd") ' get array of names from whereever
Redim pos(UBound(listofnames))
redim cnt(ubound(listofnames))
Set folderitems = ActiveExplorer.CurrentFolder.Items ' change folder to suit, if it not the active one
For Each mitem In folderitems
strt = Len(mitem.body)
For i = 0 To UBound(pos)
pos(i) = InStrRev(mitem.Body, listofnames(i), strt)
If pos(i) > 0 Then strt = pos(i): firstfnd = listofnames(i)
Next
for i = 0 to ubound(listofnames)
if listofnames(i) = cnt(i) = cnt(i) + 1: exit for
next

Next
for i = 0 to ubound(listofnames)
msg = msg & listofnames(i) & " count of emails = " & cnt(i) & vbnewline
next
MsgBox msg 'for testing only, you could add each to an array for later processing
this is again untested, you can use the arrays for whatever you like

[hmltnangel]

Again - getting close.

There was a syntax error here


listofnames(i) = cnt(i) = cnt(i) + 1: exit

Which I changed to

If listofnames(i) = cnt(i) = cnt(i) + 1 Then exit

However - it now shows

Number of emails - fred = 0, jim = 0 etc. Its not physically counting them.

Normally with excel things I can spot the mistake and fix it but with outlook things i'm way past my normal comfort zone hmmmm.

[westconn1]

should be

If listofnames(i) = firstfnd then cnt(i) = cnt(i) + 1: exit For

[hmltnangel]

Brilliant - I always find it amazing how even simple errors lkke that make such massive differences. I knew there should be a 'Then' in it somewhere but popped it in the wrong plce.

:)

[hmltnangel]

Just an fterthought (and because someone asked me)

Can it check for the last occurence of a name? I know it will be this command "InStrRev" that needs amended but, to what?

[westconn1]

try

instr(strt, mitem.body, listofnames(i)) > 0 then

strt would need initially = 1
and
strt = pos(i) +1 when found, else it will keep finding the some string

if you want to run both together, that would work, but you would need a 2nd strt variable and 2 sets of counters