Outlook Macro (Mails Auto forward to specific folder within outlook itself)

Hi

I have saved 250 emails in my outlook Inbox. I would like to move these emails to 2 different folder named as >$100.00 & <$100.00. All emails contains same format. below are the setps which i follow.

1) open the emails
2) check the amount if it is >$100.00 move to >$100.00 folder & if it is <$100.00 then move to <$100.00 folder manualy
all this i have to do manualy every day daily i get almost 250 mails
For Example : TOTAL AMT 104.16 will move to >$100.00 folder
TOTAL AMT 10.00 Will move to <$100.00 folder
Can you help me with oultook macro to automate this or rules to automate this. I have attached examples of mails for your reference.

Thanks
---------------------------

To get you going a little routine that shows you how to extract the amount on which you decide where to put the email. First you select a mail and this routine will check the selected mail and show you the number and if it's <= 100.

Code:

---

Sub Amount_Order()
Dim mymessage As Outlook.MailItem
'the text of the message
Dim thebody As String
'mynumber is the amount of the order. Defined as double because
'we want to process the stuff after the decimal too
Dim mynumber As Double
'we are going to process the selected mailitem
'make sure it's a mailitem and nothing else (ie. read receipt or something else)
Set mymessage = ActiveExplorer.Selection.Item(1)
'store the messagebody to the variable
thebody = mymessage.Body
'display it
MsgBox thebody
'locate the start of the number in the message body and add 9 because
'we don't need the search string (TOTAL AMT) included
'then locate the end of the number you want. in this case I used VENDOR because
'that's the first word after the number you are after
'starting from the searchstring TOTAL AMT and we are going to
'substract 9 + the number where we found VENDOR (1st occurence after starting point
'from TOTAL AMT to determine the number
'
'check if email has TOTAL AMT phrase
If InStr(1, thebody, "TOTAL AMT") <> 0 Then
    mynumber = Mid(thebody, InStr(1, thebody, "TOTAL AMT") + 9, _
              InStr(InStr(1, thebody, "TOTAL AMT"), thebody, "VENDOR") - _
              InStr(1, thebody, "TOTAL AMT") - 9)
    If mynumber < 101 Then
        MsgBox mynumber & " <= 100"
    Else
        MsgBox mynumber & " > 100"
    End If
Else
    MsgBox "No mail with TOTAL AMT number."
End If
End Sub

---

Charlize

Hi Charlize

Thanks for your quick reply can you please add coding that if it is "<= 100" it will move folder name "less than $100" & if it is "> 100" then it will move to folder name "Greater than $100"
It will be great help

Thanks
----------------------------

The code depends where those folders are located. Subfolders of your inbox or not, different pst file, directory folder on your drive ...
Charlize

Hi charlize

Its sub folder of centralised inbox. We received mails in centralised inbox

Thanks
-------------------

After some testing, it seems that you need a corporate exchange account (online exchange or own mailserver of company) to use the move statement of messages to a folder. I tried it at home where I use the personal outlook.com mailbox. The code gives an error because I don't have ActiveSync Exchange. What I get from that errormessage is that you must be in an exchange environment, be it in the cloud or local exchange server. Anyway, here the coding. It processes the mailfolder that is selected. So you select the shared inbox that has the two subfolders (check if the names of those folders are correct, in top of the coding where I define the folders and there names)

Code:

---

Sub Amount_Order()
    'the folder you want to process
    Dim myfolder As Outlook.Folder, less As Outlook.MAPIFolder, greater As Outlook.MAPIFolder
    'the items in this folder
    Dim myitems As Outlook.Items
    'the item in the collection of items
    Dim myitem As Object
    'the no of the item in the loop
    Dim itemno As Long
    'the message in the loop
    Dim mymessage As Outlook.MailItem
    'the text of the message
    Dim thebody As String
    'mynumber is the amount of the order. Defined as double because
    'we want to process the stuff after the decimal too
    Dim mynumber As Double
    'set folder to current selected active folder
    Set myfolder = ActiveExplorer.CurrentFolder
    Set less = myfolder.Folders("less than $100")
    Set greater = myfolder.Folders("greater than $100")
    'store the items of this folder in a container
    Set myitems = myfolder.Items
    'if greater than zero, do something
    If myitems.Count = 0 Then
        MsgBox "No emails in this folder."
    Else
        'backwards count
        For itemno = myitems.Count To 1 Step -1
            'TypeName(Item) = "MailItem"
            'make sure it's a mailitem and nothing else (ie. read receipt or something else)
            If TypeName(myitems.Item(itemno)) = "MailItem" Then
                Set mymessage = myitems.Item(itemno)
                'store the messagebody to the variable
                thebody = mymessage.Body
                'display it
                'MsgBox thebody
                'locate the start of the number in the message body and add 9 because
                'we don't need the search string (TOTAL AMT) included
                'then locate the end of the number you want. in this case I used VENDOR because
                'that's the first word after the number you are after
                'starting from the searchstring TOTAL AMT and we are going to
                'substract 9 + the number where we found VENDOR (1st occurence after starting point
                'from TOTAL AMT to determine the number
                '
                'check if email has TOTAL AMT phrase
                If InStr(1, thebody, "TOTAL AMT") <> 0 Then
                    mynumber = Mid(thebody, InStr(1, thebody, "TOTAL AMT") + 9, _
                    InStr(InStr(1, thebody, "TOTAL AMT"), thebody, "VENDOR") - _
                    InStr(1, thebody, "TOTAL AMT") - 9)
                    If mynumber < 101 Then
                        'less than $100
                        'MsgBox mynumber & " <= 100"
                        Set myitem = myitems.Item(itemno)
                        myitem.Move less
                    Else
                        'greater than $100
                        'MsgBox mynumber & " > 100"
                        Set myitem = myitems.Item(itemno)
                        myitem.Move greater
                    End If
                Else
                    MsgBox "No mail with TOTAL AMT number."
                End If
            End If
        Next itemno
    End If
End Sub

---

Charlize

@charlize

Quote:

---

The code depends where those folders are located. Subfolders of your inbox or not,

---

i told him this in some other forum (http://www.vbforums.com/showthread.p...utlook-itself)), where he posted the same question, without reply, but i see he has now given that information

Quote:

---

I tried it at home where I use the personal outlook.com mailbox

---

i have no problem moving messages in earlier outlook version, i have no access to exchange shared folders

@ westconn1

Thx for letting me know we got a crossposter. Would you be so kind to mention (you already did, so thanks for that one) in that other thread that it's not very polite to put up his questions in all the forums he can find on the internet. He wasted my time since you gave a solution too.

Anyway, the error is maybe due to some setting I don't have activated at my home (exchange ActiveSync i don't have because exchange online is a bit to much for personal mailbox use). Maybe some reference. It does work with exchange icloud stuff version 2010. Tested and it works.

@ hemant.83son

If you feel the urge to post in multiple forums, please let us know it upfront before posting your question. At least I can check that other forum to see if someone did respond or not.


Charlize

Hi Charlize & Westconn1

This is arun (Hemant's friend) here i am extrimely sorry for all this mess. I have posted hemant's query without his concerns on this forum & didnt inform him also.
He is not at all guilty in this. I am once again extremly sorry for trouble will make sure will not happen again

Thanks for your support

-------------------------

Quote:

---

Originally Posted by hemant.83son

Hi Charlize & Westconn1

This is arun (Hemant's friend) here i am extrimely sorry for all this mess. I have posted hemant's query without his concerns on this forum & didnt inform him also.
He is not at all guilty in this. I am once again extremly sorry for trouble will make sure will not happen again

Thanks for your support

-------------------------

---

It's ok for me, no worries, don't do it 2nd time. Mark this as solved and give me some extra stars :) . You could also rate this thread as extremely helpfull cause it did solve your problem.

Charlize

Hi Charlize

I am getting runtime 5 error on below lines

Code:

---

mynumber = Mid(thebody, InStr(1, thebody, "TOTAL AMT") + 9, _
                    InStr(InStr(1, thebody, "TOTAL AMT"), thebody, "VENDOR") - _
                    InStr(1, thebody, "TOTAL AMT") - 9)
                    If mynumber < 101 Then


---

Also would like to know on below
1) if there are others emails also including these kind of mails then also it will work for these mails only (not on the other mails)
2) can this coding will work if mails on subfolders & have to move to the subfolders subfolder

And I would like to give 5* to you can you let me know how to do this

Thanks
------------------------

Don't know, that snippet you posted says nothing about the rest of the code you are using or not using. Take screenshot of error message or sometimes pressing ctrl - enter when that error pops up can give extra info and post that if asking for clarification on an error message.

You keep changing the requirements, first only 1 level deep of a subfolder and now you want subfolders of subfolders.

Do you talk about attachments of such an email ?

... ???

On the left side of an answer of me in this thread there is a little star. You can upvote me.

The code I gave will check the folder that is selected. But that folder has to have those two subfolders you talked about at the start of this thread.

Messages will be moved according to the amount of AMT that is found in the email.

For the moment I'm gonna let this thread rest. Think and try something :) .

Charlize

Quote:

---

can this coding will work if mails on subfolders & have to move to the subfolders subfolder

---

yes it can, get it working correctly on one folder first

Quote:

---

if there are others emails also including these kind of mails then also it will work for these mails only (not on the other mails)

---

the original snippet i wrote would ignore any that did not contain TOTAL AMT in the body

as you have made several change to the original snippet, you need to post the entire code you are trying
VENDOR had nothing to do with the original question
using the nested instr functions is confusing, especially as you already have the result in a variable
where did thebody get assigned a value?

Hi Charlize

How are you? Hope everything fine. I have tried to work from my end but as i am new budy to VBA didnt understand it.

I have attached the error snapshot for your refrence where i am getting error
please help

Thanks
-------------------------------

make the picture much bigger so we can read it, or much better type (paste) the code and error message

Hi Westconn1

Thanks for your reply

On below line item i am getting error:

HTML Code:

---

mynumber = Mid(thebody, InStr(1, thebody, "TOTAL AMT") + 9, _
InStr(InStr(1, thebody, "TOTAL AMT"), thebody, "VENDOR") - _
InStr(1, thebody, "TOTAL AMT") - 9)

---

ERROR MASSAGE AS BELOW

Run time error '5:
Invalid procedure call or argument


Hope this will help...
Thanks
---------------------------

try changing like

Code:

---

tot = instr(1, thebody,"TOTAL AMT")
if tot > 0 then
  mynumber = mid(thebody, tot + 9, instr(tot, thebody, "VENDOR") - tot - 9)
  else
  msgbox "TOTAL AMT not found"
end if

---

if it still fails make sure that instr for Vendor is not = 0, though it may not matter if it is
this will also be quicker (though maybe not noticeable), than 3 calls to instr total amt

Hi Westconn1

I am getting same error on Line

HTML Code:

---

mynumber = Mid(thebody, tot + 9, InStr(tot, thebody, "VENDOR") - tot - 9)

---

.
I think there is something which i am doing worng. Below is the code with changes i have done.
Suggest changes

HTML Code:

---

Sub Amount_Order()
    'the folder you want to process
    Dim myfolder As Outlook.Folder, less As Outlook.MAPIFolder, greater As Outlook.MAPIFolder
    'the items in this folder
    Dim myitems As Outlook.Items
    'the item in the collection of items
    Dim myitem As Object
    'the no of the item in the loop
    Dim itemno As Long
    'the message in the loop
    Dim mymessage As Outlook.MailItem
    'the text of the message
    Dim thebody As String
    'mynumber is the amount of the order. Defined as double because
    'we want to process the stuff after the decimal too
    Dim mynumber As Double
    'set folder to current selected active folder
    Set myfolder = ActiveExplorer.CurrentFolder
    Set less = myfolder.Folders("less than $100")
    Set greater = myfolder.Folders("greater than $100")
    'store the items of this folder in a container
    Set myitems = myfolder.Items
    'if greater than zero, do something
    If myitems.Count = 0 Then
        MsgBox "No emails in this folder."
    Else
        'backwards count
        For itemno = myitems.Count To 1 Step -1
            'TypeName(Item) = "MailItem"
            'make sure it's a mailitem and nothing else (ie. read receipt or something else)
            If TypeName(myitems.Item(itemno)) = "MailItem" Then
                Set mymessage = myitems.Item(itemno)
                'store the messagebody to the variable
                thebody = mymessage.Body
                'display it
                'MsgBox thebody
                'locate the start of the number in the message body and add 9 because
                'we don't need the search string (TOTAL AMT) included
                'then locate the end of the number you want. in this case I used VENDOR because
                'that's the first word after the number you are after
                'starting from the searchstring TOTAL AMT and we are going to
                'substract 9 + the number where we found VENDOR (1st occurence after starting point
                'from TOTAL AMT to determine the number
                '
                'check if email has TOTAL AMT phrase
                'If InStr(1, thebody, "TOTAL AMT") <> 0 Then
                tot = InStr(1, thebody, "TOTAL AMT")
                If tot > 0 Then
                mynumber = Mid(thebody, tot + 9, InStr(tot, thebody, "VENDOR") - tot - 9)
                Else
                MsgBox "TOTAL AMT not found"
                End If
                    If mynumber < 101 Then
                        'less than $100
                        'MsgBox mynumber & " <= 100"
                        Set myitem = myitems.Item(itemno)
                        myitem.Move less
                    Else
                        'greater than $100
                        'MsgBox mynumber & " > 100"
                        Set myitem = myitems.Item(itemno)
                        myitem.Move greater
                    End If
                Else
                    MsgBox "No mail with TOTAL AMT number."
                  End If
            Next itemno
    End If
End Sub
 


---

Thanks
------------------------------------

post a sample body
i can not open the emails posted previously

Hi Westconn1

Attached here the samples of mails for your reference.

Thanks
------------------------

Hi

can some one help me :help: pray2:: pray2::help:help

Thanks
------------------------

as i can not open any of the posted emails, hard to test

Hi Westconn 1

Below is the mail example for your reference.

HTML Code:

---


-----Original Message-----
From: Central.Disbursements@supervalu.com [mailto:Central.Disbursements@supervalu.com]
Sent: Friday, June 13, 2014 7:25 AM
Subject: Dr/Cr Memos, Loc = 021011, 21212629
 
LOCATION 021011              SUPERVALU INC.
                          VENDOR CHARGEBACK
 
DOCUMENT  21212629              VENDOR  0325266      P.O.
TOTAL AMT          25.00
                                                      CM FROZEN 6-8 MERCH ALLOW
                                      CREATED BY:                  Belinda Derr
 
 GENERAL MILLS  MINNEAPOLIS                    SUPERVALU - EASTERN REGION
 ATTN DANETTE CONOBOY                          P.O. BOX 9340
 PO BOX 714                                    MINNEAPOLIS      MN 55440-9340
 MINNEAPOLIS      MN 55440-0714            CENTRAL.DISBURSEMENTS@SUPERVALU.COM
 
                                  DOCUMENT DATE  06/12/14  DUE DATE  06/12/14
 
DESCRIPTION                                      QUANTITY    PRICE      AMOUNT
  UPC                          INT ITEM CD    PACK/SIZE
------------------------------ ---------------- ------------ -------- ----------
 
            VEGETABL/RICE BLND                        1.00    25.0000    25.00
  0000000-20000-27797          00719-4347    12/      12OZ
 
 
 
 
 
                  AMOUNT DEDUCTED SUPERVALU - EASTERN REGION              25.00

---

Thanks
-----------------------

as vendor is before total amount, instr will return 0, which will cause error in MID

Hi westconn1

Can you please correct it

Thanks
-----------------------

Quote:

---

Originally Posted by westconn1

as vendor is before total amount, instr will return 0, which will cause error in MID

---

try
mynumber = val(mid(thebody, tot))
see if it gives the correct result for you