Moving emails to folders based on SenderName

Question

I have the following Visual Basic script that should move emails in my Inbox to specific folders but when I run it, nothing happens. I am very new to VBA so am a little confused as to why. Does anything stick out, or do you have any suggestions as how to find out why this is('nt) happening? Thanks!

Code:

Sub Move_Emails()
Set myNameSpace = Application.GetNamespace("MAPI")
Set myInbox = myNameSpace.GetDefaultFolder(6)
Set myItems = myInbox.Items
Dim myItem As Outlook.MailItem
Dim MailItem As Object
Dim sn As String

For Each MailItem In myInbox.Items
    sn = MailItem.SenderName
    If sn = "John Doe" Then
        Set myDestFolder = myInbox.Folders("Folder1")
    ElseIf sn = "Jane Smith" Then
        Set myDestFolder = myInbox.Folders("Folder2")
    ElseIf sn = "Bob Jones" Then
        Set myDestFolder = myInbox.Folders("Folder3")
    End If
    Set myItem = myItems.Find("[SenderName] = sn")
    While TypeName(myItem) <> "Nothing"
        myItem.Move myDestFolder
        Set myItem = myItems.FindNext

    Wend
Next
End Sub

Kazimierz Jawor · Accepted Answer · 2013-07-01T21:18:57.820

1

You need to change the way you set your myItem variable. In your code sn is a variable and if you put it inside quotation marks it's not converted to real sender name. So, instead of this line:

Set myItem = myItems.Find("[SenderName] = sn")

use this line:

Set myItem = myItems.Find("[SenderName]='" & sn & "'")

Edit regarding possible problem according to comments below... When you check for the name in this way:

If sn = "John Doe" Then

you check for exact name of John Doe including upper/lower cases. I suggest to change it in this way:

If Ucase(sn) = "JOHN DOE" Then

to avoid possible problems with names spelling. Do it for all checks in If statement.

Edit 2nd... I have just realised that you use incorrect loop for moving elements. If you move one element to other folder as a result you change the order of your looping when using For each loop. Therefore I suggest some more changes as described below in new complete code:

Sub Move_Emails_improved()
Dim myNamespace, myInbox, myItems ', myDestFolder- NEW CHANGED MOVED TO SEPARATE LINE BELOW
Set myNamespace = Application.GetNamespace("MAPI")
Set myInbox = myNamespace.GetDefaultFolder(6)   
Set myItems = myInbox.items
Dim myItem As Outlook.MailItem
Dim MailItem As Object
Dim sn As String

'NEW LINE BELOW
Dim myDestFolder As Folder
'here you need different kind of loop
Dim i as integer
For i = myInbox.items.Count To 1 Step -1   'loop goes from last to first element
    sn = myInbox.items(i).SenderName

    'first possible problem
    If Ucase(sn) = "JOHN DOE" Then
        Set myDestFolder = myInbox.folders("Folder1")

    'alternatively you could check name in this way
    ElseIf UCase(sn) Like "*JANE SMITH*" Then
        Set myDestFolder = myInbox.folders("Folder2")
    ElseIf sn = "Bob Jones" Then
        Set myDestFolder = myInbox.folders("Folder3")
    End If
    Set myItem = myItems.Find("[SenderName]='" & sn & "'")

    'here we need to check if folder is not set
    'NEW- THIS LINE IMPROVED
    While TypeName(myItem) <> "Nothing" And And Not myDestFolder Is Nothing
        myItem.Move myDestFolder
        Set myItem = myItems.FindNext
        'NEW LINE BELOW
        i = i - 1

    Wend
    'and set destination folder to nothing to eliminate all problems
    Set myDestFolder = Nothing
Next
End Sub

Hope it will work now.

edited Jul 01 '13 at 21:18

answered Jul 01 '13 at 19:57

Kazimierz Jawor

18,861
7
35
55

Thanks, I tried replacing my code like you said, but now I get: `Run-time error '424': Object required`, any thoughts? – Dryden Long Jul 01 '13 at 20:09
Sorry, I spaced that. It's on the `myItem.Move myDestFolder` line – Dryden Long Jul 01 '13 at 20:11
which means that any of your `if statement` meet criteria and as a result your `myDestinationFolder` is not created (variable is set to nothing). Check it by hovering mouse over this variable during debugging after error. Are you sure that you have any of the sender's name e-mail in your Inbox? is it exactly 'John Doe' or ' John Doe ' or 'John Doe priv'?... – Kazimierz Jawor Jul 01 '13 at 20:31
Now I get `Type mismatch` error on the line `Set myItem = myItems.Find("[SenderName]='" & sn & "'")` – Dryden Long Jul 01 '13 at 21:09
while searching possible problems I've made some more simulation by adding additional test e-mails which I'll try to move. As a result I recognised some more changes which will improve the code. All (four) are added to my full answer code with `'NEW...` comment. So, please add it to your code. However I could not replicate your `Type mismatch` error... – Kazimierz Jawor Jul 01 '13 at 21:20
I changed the `Like` line of code to match exact and now I am getting `Array index out of bounds` on the line `sn = myInbox.Items(i).SenderName` where the value of `sn` is the `SenderName` of the first email in my inbox. It did however move all the emails from "Bob Jones" to the correct folder. This is before your updated code roughly 2 minutes ago (2:22PM PST) – Dryden Long Jul 01 '13 at 21:23
exactly, add all NEW lines added by me quite recently as described above in previous comment... – Kazimierz Jawor Jul 01 '13 at 21:24
Still getting the `Type mismatch` error on the same line as above. `sn` is coming across as the `SenderName` but `myItem` is coming across as `Nothing` – Dryden Long Jul 01 '13 at 21:30
what is `sn value` when error occurs? as I said I cant replicate this error therefore I don't know what to suggest... – Kazimierz Jawor Jul 01 '13 at 21:32
Thanks for all your help on this. I think we may be at a dead-end here so I'm going to attack this from another angle. – Dryden Long Jul 02 '13 at 18:32
You're welcome! but to make it clear- this code, final version from my answer, worked for me correct. so, maybe don't give up?! Try to run this code with F8 key (debugging option) to check which way your code is going and to find any problems in it. – Kazimierz Jawor Jul 02 '13 at 18:47
Will do! Thanks! I'm going to mark this as accepted since you have it working and ultimately you did solve my original issue with the loop being incorrect. – Dryden Long Jul 02 '13 at 18:58

score -1 · Answer 2 · edited Feb 29 '16 at 15:58

-1

You can use also this:

If myitem.Sender Like "*" & sn & "*" Then
    ' your code

edited Feb 29 '16 at 15:58

trincot

317,000
35
244
286

answered Feb 03 '15 at 17:16

Ranjeet Srivastav

1

Moving emails to folders based on SenderName

2 Answers2