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

[hemant.83son]

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
---------------------------

[Charlize]

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.

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 SubCharlize

[hemant.83son]

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
----------------------------

[Charlize]

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

[hemant.83son]

Hi charlize

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

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

[Charlize]

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)

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

[westconn1]

@charlize


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.php?767293-Urgent-Outlook-Macro-(Mails-Auto-forward-to-specific-folder-within-outlook-itself)), where he posted the same question, without reply, but i see he has now given that information


I tried it at home where I use the personal outlook.com mailboxi have no problem moving messages in earlier outlook version, i have no access to exchange shared folders

[Charlize]

@ 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

[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

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

[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

-------------------------
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

[hemant.83son]

Hi Charlize

I am getting runtime 5 error on below lines



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
------------------------

[Charlize]

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

[westconn1]

can this coding will work if mails on subfolders & have to move to the subfolders subfolderyes it can, get it working correctly on one folder first

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?

[hemant.83son]

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
-------------------------------

[westconn1]

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

[hemant.83son]

Hi Westconn1

Thanks for your reply

On below line item i am getting error:


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
---------------------------

[westconn1]

try changing like

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 ifif 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

[hemant.83son]

Hi Westconn1

I am getting same error on Line
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


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
------------------------------------

[westconn1]

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

[hemant.83son]

Hi Westconn1

Attached here the samples of mails for your reference.

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

[hemant.83son]

Hi

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

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

[westconn1]

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

[hemant.83son]

Hi Westconn 1

Below is the mail example for your reference.



-----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
-----------------------

[westconn1]

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

[hemant.83son]

Hi westconn1

Can you please correct it

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


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

[westconn1]

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