VBA Complex macro with body search

**andrispajula** · 05-31-2017, 12:25 AM

Hello,My skillset regarding VBA is very limited, so my knowledge has come from the threads I've been searching in forums.I need to create a macro that first of all searches the body of all the emails including the incoming(new) emails with a keyword. For an example, search all the emails with the word "help" in their body. If the words exists create a new sub-folder in the folder called "Helpdesk" and the name of the new sub-folder should be the name of the email and move that email to that folder.
To get a better understanding:
Search all the emails in my outlook inbox where there is a word "help" in its body. When it finds an email with the word "help" in its body, it should create a sub-folder under "Helpdesk" where the name of the sub-folder is the name of the email the word was in. Then move the email to that folder.I have found some seperate pieces of code, but i do not know how to add them up to one.

For some reason i cannot post the pieces of codes i have found.

**andrispajula** · 05-31-2017, 02:13 AM

Sub MailtoFolder()


    Dim myNameSpace As Outlook.NameSpace
    Dim myInbox As Outlook.MAPIFolder
    Dim myDestFolder As Outlook.MAPIFolder
    Dim Item As Outlook.MailItem


    
    
    Set myNameSpace = Application.GetNamespace("MAPI")
    Set myInbox = myNameSpace.GetDefaultFolder(olFolderInbox)
    Set myDestFolder = myInbox.Folders("Tasks")


    For Each MailItem In myDestFolder.Items
    
    If InStr(Item.Body, "doghouse") > 0 Then
        Item.Move myDestFolder


    End If
    
    Next MailItem


    Set myDestFolder = Nothing
    Set myInbox = Nothing
    Set myNameSpace = Nothing


End Sub

I tried to create this bit of code, but it doesnt seem to work in my Outlook 2010. Can someone explain what is the problem?

**gmayor** · 05-31-2017, 04:16 AM

You have your folders and items a little mixed up. If you want to search the inbox then you need something more like the following. If the process finds the word 'doghouse' then it checks if there is a subfolder called 'doghouse' and if not creates it, before moving the messages containing the word to that folder.

Option Explicit

Sub MailtoFolder()
Dim myNameSpace As Outlook.NameSpace
Dim myInbox As Outlook.MAPIFolder
Dim myDestFolder As Outlook.MAPIFolder
Dim olFolder As Outlook.MAPIFolder
Dim bFolder As Boolean
Dim myItem As Outlook.MailItem
    Set myNameSpace = Application.GetNamespace("MAPI")
    Set myInbox = myNameSpace.GetDefaultFolder(olFolderInbox)
    For Each myItem In myInbox.Items
        If InStr(myItem.Body, "doghouse") > 0 Then
            For Each olFolder In myInbox.folders
                If olFolder.Name = "doghouse" Then
                    bFolder = True
                    Exit For
                End If
            Next olFolder
            If Not bFolder Then myInbox.folders.Add ("doghouse")
            Set myDestFolder = myInbox.folders("doghouse")
            
            myItem.Move myDestFolder
        End If
    Next myItem
    Set myDestFolder = Nothing
    Set myInbox = Nothing
    Set olFolder = Nothing
    Set myNameSpace = Nothing
    Set myItem = Nothing
End Sub

**andrispajula** · 05-31-2017, 04:56 AM

Thank you very much gmayor for your answer!
This was very helpful. Although I have written a code with comments to understand what im trying to accomplish right now. Been trying to crack it for hours. Like I said im a rookie and just starting to learn VBA.

Sub MailtoFolder()


    Dim myNameSpace As Outlook.NameSpace
    Dim myInbox As Outlook.MAPIFolder
    Dim myDestFolder As Outlook.MAPIFolder
    Dim Item As Outlook.MailItem
    Dim myFolder As Outlook.Folder
    Dim dataType As Variant
    Dim myNewFolder As Outlook.Folder


    
    Set myNameSpace = Application.GetNamespace("MAPI")
    Set myInbox = myNameSpace.GetDefaultFolder(olFolderInbox)
    Set myDestFolder = myInbox.Folders("Not qualified")
   




    For Each MailItem In myInbox.Items
    
    If InStr(MailItem.Body, "doghouse: ") > 0 Then
        ' Create sub-folder(if doesnt exist), the name of the sub-folder should be 6 characters
        ' after the search word which in my case is doghouse (I.E doghouse A54V4L)
        ' And the next step is to move that email to that folder which was created
        ' Set myDestFolder = myInbox.Folders("6charactersOfTheDoghouse") this should be sub-folder
            If InStr(MailItem.Body, "City") > 0 Then ' If the email also contains city name
            ' Creation of another sub-folder under the previously created folder (I.E A54V4L)
            Else
            MailItem.Move myDestFolder ' The doghouse is not qualified and moves to not qual. folder
            End If
    
    End If


    Set myDestFolder = Nothing
    Set myInbox = Nothing
    Set myNameSpace = Nothing
    
   
    Next MailItem


End Sub

This is the code im trying to accomplish, I added comments to make it more understandable. Any help would be hugely appriciated because if I get this script going, it will save me months of work!

**gmayor** · 05-31-2017, 05:42 AM

You should be able to work most of that out from my code, which shows how to check if the folder exists and to create it. However your comments don't make a lot of sense. VBA doesn't do guesswork. and your seem to have some of that in your searches and consequences of that search. I can't help with that unless I know EXACTLY what is in the message is it 'doghouse: ' or 'doghouse A54V4L' without the colon? I take it that 'doghouse' is a constant and 'A54V4L' changes?

code queries.jpg

**andrispajula** · 05-31-2017, 06:11 AM

Thank you Graham for your feedback!
Im sorry if my explanation is not very clear. I am not native English speaker as well, but I will try to do it better this time.
So later I will need to create new code lines which will create a folder with two spaces(7 or more characters) so I thought if i get some help how it works with 6 characters, I can figure out the part with more characters later.

Here is an explanation I tried to post earlier but I couldnt post it due to me being too new user.

The script searches all through my mailbox for emails that contain "doghouse"
in its body. When it finds an email that contains the word doghouse in its body, it will need to
create a new sub-folder under the "main" folder, lets say for an example the folder I have in my mailbox is called Orders. The name of the sub-folder which will be created under "Orders" should be named after the 6 characters that come after doghouse in that email body. (I.E when we have doghouse A54V4L in the body, the folder looks like this Orders -> A54V4L(this is subfolder) and when the script finds another email with doghouse in its name, it again takes 6 characters after and creates a new sub-folder under the folder called Orders(I.E doghouse B54V4L). So after 2 emails we have a mailbox that looks like this:

Inbox
---Orders
-----A54V4L
-----B54V4L

then move the email to that folder. Next step is to do another body check to find the word "city".
And then the proccess is the same as before, but now it creates another sub-folder under the name A54V4L. And lets say that the email with B45V4L does not have city in its body. If it does not find city in the body of the email, it will move the email to the folder called "not qualified".

So it shoud look something like this:

Inbox
---Orders
-----A54V4L
-------London
---Not Qualified
-----B54V4L

**gmayor** · 05-31-2017, 06:57 AM

Hmmm. I understand the theory of what you are trying to do, but if we are going to look for 'doghouse' and then grab the six digit text string after it 'e.g. A54V4L' we need to be certain what comes between them. Your latest message implies a space, but your previous message hinted at a colon and a space i.e. doghouse: A54V4L.

If you can clarify what EXACTLY we are dealing with, I'll pick it up tomorrow, if no-one has jumped in the meantime.

**andrispajula** · 05-31-2017, 09:15 AM

Hey Graham, thanks for your feedback once again.

We are dealing with 'doghouse: '

That means that there will be a colon and a space. Thanks a lot once again! I will start fresh tomorrow aswell.

**gmayor** · 05-31-2017, 08:34 PM

I guess that means 'city' will have a colon and be followed by the name of a city? This creates a problem as city names are often more than one word e.g. Los Angeles, so it can be more difficult to determine what the City name is. How are the messages laid out? Are the searched terms each in their own paragraph e.g.

doghouse: A54V4L¶
somethingelse: text¶
city: London¶

or are they mixed in with the text e.g.

doghouse: A54V4L somethingelse: text city: London

If the latter how is the end of the city name determined?

**andrispajula** · 05-31-2017, 11:27 PM

Good morning,

I did some digging in these emails(there are tons of them). And I will draw a picture to make it more understandable.
Both of the options are used in these emails(mixed and paragraph) but most of them are paragraphed. We can choose only the paragraphed styled and I will move the folders that are in text later manually if it creates problems.

picture.jpg

**gmayor** · 06-01-2017, 01:50 AM

Without access to the actual messages, and assuming that there is only one DOGHOUSE line per message and not the three shown, the following should work for messages where the text is in paragraphs as shown in the example mock-up.

Option Explicit

Sub MailtoFolder()
Dim myNameSpace As Outlook.NameSpace
Dim myInbox As Outlook.MAPIFolder
Dim myDestFolder As Outlook.MAPIFolder
Dim olFolder As Outlook.MAPIFolder
Dim bFolder As Boolean
Dim myItem As Outlook.MailItem
Dim sText As String
Dim vText As Variant, vItem As Variant
Dim strCity As String, strNumber As String
Dim i As Long
    Set myNameSpace = Application.GetNamespace("MAPI")
    Set myInbox = myNameSpace.GetDefaultFolder(olFolderInbox)
    For Each myItem In myInbox.Items
        If InStr(UCase(myItem.Body), "DOGHOUSE") > 0 Then
            For Each olFolder In myInbox.folders
                If olFolder.Name = "Orders" Then
                    bFolder = True
                    Exit For
                End If
            Next olFolder
            If Not bFolder Then myInbox.folders.Add "Orders"
            Set myInbox = myInbox.folders("Orders")

            sText = myItem.Body
            vText = Split(sText, Chr(13))

            'Check each line of text in the message body
            For i = 1 To UBound(vText)
                If InStr(1, UCase(vText(i)), "[CITY") > 0 Then
                    vItem = Split(vText(i), Chr(58))
                    strCity = Trim(vItem(1))
                End If
                If InStr(1, UCase(vText(i)), "DOGHOUSE") > 0 Then
                    vItem = Split(vText(i), Chr(58))
                    strNumber = Trim(vItem(1))
                End If
            Next i
            If Not strNumber = "" Then
                bFolder = False
                For Each olFolder In myInbox.folders
                    If olFolder.Name = strNumber Then
                        bFolder = True
                        Exit For
                    End If
                Next olFolder
                If Not bFolder Then
                    myInbox.folders.Add strNumber
                End If
                Set myDestFolder = myInbox.folders(strNumber)

                If Not strCity = "" Then
                    bFolder = False
                    For Each olFolder In myDestFolder.folders
                        If olFolder.Name = strCity Then
                            bFolder = True
                            Exit For
                        End If
                    Next olFolder
                    If Not bFolder Then
                        Set myDestFolder = myDestFolder.folders.Add(strCity)
                    End If
                Else
                    bFolder = False
                    For Each olFolder In myDestFolder.folders
                        If olFolder.Name = "Not Qualified" Then
                            bFolder = True
                            Exit For
                        End If
                    Next olFolder
                    If Not bFolder Then
                        Set myDestFolder = myDestFolder.folders.Add("Not Qualified")
                    End If
                End If
                myItem.Move myDestFolder
            End If
        End If
    Next myItem
    Set myDestFolder = Nothing
    Set myInbox = Nothing
    Set olFolder = Nothing
    Set myNameSpace = Nothing
    Set myItem = Nothing
End Sub

**andrispajula** · 06-01-2017, 02:16 AM

Wow! Amazing! This is hugely appriciated! I will try it out in a second. One more problem. I get the type mismatch error when im trying to run this code. Im guessing its because there are different types of emails in my inbox as well(for an example meetings etc.)
Can you give me some tips how to fix that Graham?

Once again, im really grateful that you are helping me this much!!

**gmayor** · 06-01-2017, 03:48 AM

Have you replaced the original code so there are no conflicts? It works fine here based on what you have said.
Which line causes the error?
I don't think we can take this any further without access to one or two original messages.

**andrispajula** · 06-01-2017, 03:51 AM

Are you saying that when I have meeting invitations/accepts etc in my mailbox, it should work with the code written? The error line is Next myItem

What I have read is that type mismatch implies that its not returning a Outlook.Mailitem

picture2.jpg

This is an example email. In this one its not in paragraphs, how would it work with text in line like shown above?

Is there a opportunity to communicate with you faster i.e Skype standartchat?

**gmayor** · 06-01-2017, 05:21 AM

The message you have just posted bears no relationship to the mock-up you posted earlier and will not work with the code I posted. Furthermore I have no idea what you want from this message as it does not fit the criteria you have posted earlier. It could however explain the error if you have tried to process it. I do not feel inclined to spend the time working out yet more methods when you keep moving the goalposts.

I do not provide free private consultancy nor communicate with users other than in the forum.

**andrispajula** · 06-01-2017, 05:47 AM

Hello gmayor,

I am very sorry! You have definitely helped me out a lot and I understand your frustation regarding the earlier mock-up. The word REGISTRY: represents the DOGHOUSE: in the picture. It seems I copied wrong word in there.
picture2.jpg
Also it seems that the emails I have to deal with are some in paragraphs and some in the email as shown above.

I did fix my error and the code works thanks to you. Although there are few minor bugs I would like to fix. I understand if you do not wish to spend any of your valuable time to this matter.

First, it works with emails that are paragraphed but not lined as I have stated. The ones with lined wont be affected with the code.
Secondly, at the moment it creates a sub-folder under "Orders" with the name of the doghouse. I created a pictures to show what I mean because my I couldnt explain it with words well enough...

This is how it shoud look like. At the moment, it correctly created the folders that had all the parameters filled. But for an example it did not move the emails that i.e did not have a DOGHOUSE: NUMBER.

picture7.PNG
Also ALL the not qualified should go to the folder above orders not inside the folder "Orders".

**gmayor** · 06-01-2017, 11:19 PM

I'm sorry but this is like herding cats. We now have four different types of message layout, and who knows how many more there are, which makes it very difficult to differentiate between the different types all of which require different processing.

If this is going to be an ongoing future project, you need to standardise your form or it is going to provide endless frustration keeping control of it. I don't feel I can waste any more of my time on this.

**andrispajula** · 06-01-2017, 11:49 PM

Hello gmayor,
I understand.
This is the most commonly used email layout(80% of emails):

asd123asd.jpg
I really appriciate if you could help me with this one, but I also understand if you do not feel like it. Thanks a lot for previous help!

**gmayor** · 06-02-2017, 03:58 AM

Again you have produced another format, that is not even similar to the previous version and yet it accounts for 80% of the messages?

I can understand commercial sensitivity, but the parts you have blacked out are important to the identification of the required parts to ensure that only the required parts are identified. The more variability in the message formats (you have now described 5) means that this is virtually impossible. VBA doesn't do guesswork. It requires strict adherence to rules.

If you want to employ me at a commercial rate to do this for you then you will need to contact me via my web site, but I will need to see a selection of the original messages and not your interpretation of them and even then, if there is even more variety than you have so far indicated, I cannot guarantee success.

**andrispajula** · 06-02-2017, 04:53 AM

Thank you for your feedback.

Did I understand correctly that if the DOGHOUSE:A54V4L position is different in every email, it requires different code every time(basically)?

Thread: VBA Complex macro with body search

Thread Tools

Display

VBA Complex macro with body search

Posting Permissions