Solved: File New Email bassed on subject

[snoopy]

Hi all,



I know nothing about vb. I use Outlook 2003 at work and have around 9 PST for client emails etc. I spend the best part of a morning every day filing and move emails.

I have 100 of rules which I have to run manually. Somebody told me I could create a vb script to file the emails based on a subject field or the email address i.e. a mail could arrive from somebody@needhelplease.com and it would file it into the relevant pst folder contain that client.



Is this possible and if so how do it?



Snoopy

Thanks in advance

[Bob Phillips]

I have 100 of rules which I have to run manually. Somebody told me I could create a vb script to file the emails based on a subject field or the email address i.e. a mail could arrive from somebody@needhelplease.com and it would file it into the relevant pst folder contain that client.

You shouldn't need VBA for this, Outlook should take care of it.

Don't you move the emails to the folders within the rules definition?

[snoopy]

Yep it does. But I would like to have automatic script to do it. In 2003 you can only have about 20 rules that auto run. I would like a script that would do this once an email arrives (so when I am out at a clients site I dont come back to 100s of mails that overflow my mail box) and then files then based on the content or email address.

This would be a wonderful time saver.

Regards
Snoopy

[MOS MASTER]

Hi,

Xld is right that for normal Outlook use the Rules wizard should manage it for you. But it seams you don't have a normal Outlook set-up with 9 *Pst files connected.

So A few questions back:

• Can you browse to to all connected folders of the differnt *pst files? (love to see that set-up) Could you explain how you connect them all?
• If you can browse to them they are connected so you can move Mail to them.
• It's easy to check for a sendername on arival of a email and do a select case on it to determine in which folder it should go. (But it's gonna take a lot of work explaining to you how to implement this..because you're a novice)

So Let's try to to begin little on this.

We can make some code to check for one of you're emailaddresses. And have that mail moved to the right folder.

So can you give me the name of one of those folder.
I need the total name of the folder and the foldernames of the parent folders. (higher level/ Inbox if it's in there) Like Sales folders path = "Public Folders/Company/Sales"

If you provide me with that we can work from there.

[snoopy]

Wow.. Great Okay here goes. I have the PST folders stored on a network drive (H :\)

I can view and open all the pst's at the same time. I open each PST in the following way...

File > Open > Outlook Data File

Then repeat for all the psts I need. Once added they always open when I start Outlook.



I have one PST file called Clients-05 and the directory structure is like this



Clients-05 > ClientA \ ClientB a ClientC etc...



Under each Client I have the following Structure



ClientA > Setup \ Contact \ Prices \ Docs \ Legal \ Gen



So an Email contain ?@ClientA.com? would arrive and then it would be filed under the ?Gen? folder under ClientA



Also if an Email arrived from one of my colleagues with ?Subject: ClientA? this would also be filled in the ?Gen? folder.



Hope this gives you an idea and I am very grateful for your help.



Regards

Snoopy

[MOS MASTER]

Hi Snoop,

Yes this will get me started.

I have limited time today so I'll post the code for this tommorow.
I'll try to code a function that you can easily expand like adding your own custom rules. (So you can add as many cases to handle as you like)

Till tomorrow..

[snoopy]

Hey M.O.S. Master

Thank you very much. This will be a great help.

Regards
Snoopy

[MOS MASTER]

Hey M.O.S. Master

Thank you very much. This will be a great help.

Regards
Snoopy

Hi Snoop,

Got it working over here but because you're a novice I'm trying to make it as simple as possible and I'm thinking about the correct way for you to make this work.

Meanwhile I have a question: which Outlook version are you running? (2002/2003/lower...)

Also you metioned if a emailaddress is like @ClientA.com?
Does this mean you want all addresses from a particular companie? (So I should strip of the first part of the mailaddress?

The same is for the subject line wil this always be titled ClientA? I mean is that the whole title or is it part of a title? If so I need to search the subjects for all the variations you give me....

The two last questions will make the sub harder to write and slower buth I'll do my best..

Till tomorrow.

[snoopy]

Hey M.O.S. Master



Sorry it seems I am making a lot of work for you and that was not my intension I really do appreciate the help you are giving me.



To answer your questions:



1. If you could make it so that it strips the ?name? parts off the email address that would be just the ticket.

2. It would a nice thing to have but don?t break your back trying, I am sure you have better things to do than write vb for me. And hopefully I will be able to build on what you send me. (Which will hopefully help me learn



Kind regards

Snoopy

[MOS MASTER]

Hi Snoopy,

Ok here we go please read everything carrefully and if you have problems read this again and the comments in the code to see if you changed everything acording to your personal situation!

Ok Let's start:

1. Open de Outlook Editor (Press ALT+F11)
2. Choose menu: Insert | module (You will see a white code pane to the right of the screen (Else Press F7))
3. Paste this code: (This part needs no modification)

[VBA]
Option Explicit
Option Compare Text
'#######################################
'Main program
'No modifications are needed in this Sub!
'#######################################
Sub MoveMail(oMail As Outlook.MailItem)
Dim oFolder As Outlook.MAPIFolder
Dim sSender As String
Dim sSubject As String
Dim sFolderPath As String
With oMail 'object represent incomming e-mail

'Get stript sender company address
sSender = StripAddress(.SenderEmailAddress)

'Check for client name in Subject line
sSubject = DetermineClient(.Subject)

'Get the folder path the output will look like:
'Clients-05/ClientA/Gen
'Remember each "/" stands for a subfolder of the previous one!
sFolderPath = ChooseFolder(sSender, sSubject)
If sFolderPath <> "Unknown" Then
'Set a handle to the folder specified by sFolderPath
Set oFolder = GetMAPIFolder(sFolderPath)
If TypeName(oFolder) <> "Nothing" Then
'Move mailitem to specified folder
oMail.Move oFolder
End If
Set oFolder = Nothing
End If
End With
End Sub
'#######################################
'End of Main program
'#######################################

'#######################################
'Support function StripAddress
'Function designed to return the last part
'of a e-mailaddress from the @ on
'#######################################
Private Function StripAddress(sSender As String) As String
StripAddress = Right(sSender, Len(sSender) - InStr(1, sSender, "@", vbTextCompare))
End Function
'#######################################
'End of StripAddress
'#######################################

[/VBA]

1. Paste the following Function at the end:
2. Be ware Adjustments need to be made!

[VBA]
'#######################################
'Support function DetermineClient
'Function designed to return client if the
'name is in the subject line
'#######################################
Private Function DetermineClient(sSubject As String) As String
Dim sArray As Variant
Dim iCnt As Integer
Dim sTmp As String
'Array with client names you can just add clients by adding a comma and
'your client between "" like: "clientc"
'BEWARE, put clientname in Lower case
sArray = Array("clienta", "clientb")

'Loop the array values
For iCnt = LBound(sArray) To UBound(sArray)
'sTmp is the subject in lower case
sTmp = LCase(sSubject)
'If the value of sArray(icnt) = in sTmp then we found a client
If InStr(1, sTmp, sArray(iCnt), vbTextCompare) <> 0 Then
'Return client
DetermineClient = sArray(iCnt)
'No need to stay so exit
Exit For
Else
DetermineClient = "Unknown"
End If
sTmp = ""
Next

Erase sArray
End Function
'#######################################
'End of DetermineClient
'#######################################
[/VBA]

1. Read the comments and you'll see you have to change: sArray = Array("clienta", "clientb")
2. Put all your clientnames here Beware NO TYPOS!
3. Paste the next function at the end

[VBA]
'#######################################
'Support function ChooseFolder
'Function designed to return the folderpath to the
'folder the e-mail must be moved. First we look for a specific emailaddress
'if not found look for a client that was retrieved from subject line
'#######################################
Private Function ChooseFolder(sSender As String, sSubject As String) As String
Dim bFound As Boolean

'look for a match in senderaddress
Select Case LCase(sSender)
Case Is = "clienta.com" 'Fill in client emailaddress
bFound = True
ChooseFolder = "Clients-05/ClientA/Gen"
Case Is = "clientb.com"
bFound = True
ChooseFolder = "Clients-05/ClientB/Gen"
Case Else
bFound = False
ChooseFolder = "Unknown"
End Select

'look for a matching client in subject line
If bFound = False Then
Select Case sSubject 'Subject used for internal mail
Case Is = "clienta" 'write lower case
ChooseFolder = "Clients-05/ClientA/Gen"
Case Is = "clientb"
ChooseFolder = "Clients-05/ClientB/Gen"
Case Else
ChooseFolder = "Unknown"
End Select
End If
End Function
'#######################################
'End of ChooseFolder
'#######################################
[/VBA]

1. No Pay attention read the above function very carefull! and change it to your specific needs.
2. Thinks to change:

[VBA]
Case Is = "clienta.com" 'Fill in client emailaddress
bFound = True
ChooseFolder = "Clients-05/ClientA/Gen"[/VBA]
This is the building block for creating Clients to check for.

So you could add a block for each client you want checked but make sure that:

1. Let's say the client address is every address from domain @Microsoft.com? Then fill in: Case Is = "microsoft.com" (Rember lower case)
2. Add the bFound = True rule
3. Now fill in the correct path for the folder to move the file.
4. Important: Go to the highest folder of your PST file (normal Personal Folders) in your case probably "Clients-05" Right-click it and make sure the name is correct.
5. No write the path to the folder with a "/" between them for each subfolder (Folder in the previous folder) until you reach the folder in which the mail should be moved. NO TYPOS!
6. If no match in mailaddress is found the code will look for a match in the subject line in the select case construction that will evaluate: sSubject like:
7. Case Is = "clienta" 'write lower case
   ChooseFolder = "Clients-05/ClientA/Gen"
8. You must change these values in the same manner as the changes for the emailaccounts
9. The above code means: If sSubject = clienta then the value of ChooseFolder = "Clients-05/ClientA/Gen"
10. Now the last part paste this code at the end:

[VBA]
'#######################################
'Support function GetMAPIFolder
'Function to return the Folder object in which
'the e-mail item will be moved
'No changes needed to the code
'#######################################
Private Function GetMAPIFolder(sFolderPath As String) As Outlook.MAPIFolder
Dim oNameSpace As Outlook.NameSpace
Dim oFolders As Outlook.Folders
Dim oFolder As Outlook.MAPIFolder
Dim sArray() As String
Dim iCnt As Integer
On Error Resume Next
'Split the folderpath in a array of strings
sArray() = Split(sFolderPath, "/")
'Set reference to the namespace of the app
Set oNameSpace = Application.GetNamespace("MAPI")
'Set a handle to the first folder in the folderpath
Set oFolder = oNameSpace.Folders.Item(sArray(0))
If TypeName(oFolder) <> "Nothing" Then
'Loop the array to set a handle to each folder in the folderpath
'until the last folder is set and that's the one we will move the
'mail to!
For iCnt = 1 To UBound(sArray)
Set oFolders = oFolder.Folders
Set oFolder = Nothing
Set oFolder = oFolders.Item(sArray(iCnt))
If TypeName(oFolder) = "Nothing" Then
Exit Function
End If
Next
End If
Set GetMAPIFolder = oFolder
Erase sArray

Set oFolders = Nothing
Set oNameSpace = Nothing
End Function
'#######################################
'End of GetMAPIFolder
'#######################################
[/VBA]

1. No modifications needed to the above function.
2. The function will return a folder object representing the folder the mail will be moved to

Ok done pasting!

Let's check the code:
Go to the Error Menu and press "Compile Project" if no error is trown then where ok for now!

If you do receive an error I have to see the code!
So Press CTRL+R and you'll see to the left the project Explorer (If it wasn't allready there) You see a node called "Module1"
Rightclick that node and choose Export
Export the module to your desktop and then zip it with Winzip!

The code by now has sensitive information to you I presume so you'd better send it to me directly so I can Examine the code for you. Mail to: joost@webforums.nl

Ok that was worst case scenario. But if no Error is there we can continue!

Now press the Save button on the Outlook Toolbar.
Close the Editor and Close Outlook Choose YES if you're asked to save your OTM-file!!!!

Restart Outlook
Let's first examine your Macro settings:

1. Go to menu: Tools|Macro's|Security
2. Set security to Medium and press OK

Now we need to set up an incoming rule that will start our script to run on the Emailitem!

Oh one thing! My Office is Dutch so terms to buttons can differ because I don't know there exact name so I'm translating to the best of my knowledge!

1. Go to menu: Tools|Rules wizard
2. Choose New Rule
3. Choose Start with a new rule
4. Select check mail on Arival
5. Choose Next
6. We will skip Step 1 because we want all incoming mail checkt. So Choose Next (Press yes to skip the warning)
7. Scroll to the bottem and Choose Run Script. Now in the bottom window you can press script and a dialog will pop-up in which you choose: "Project1.MoveMail"
8. Choose Next
9. We'll skip this step as well so choose Next
10. Now you have to name your Rule so give it a name e.g.: Client Emailrule
11. Make sure the checkbox is marked that the Rule is On. (So it will run)
12. No press Complete/finish (or something like that)
13. Again press Complete / finish and Ok
14. The rule will run on ALL incoming E-mail Now!
15. Close Outlook and Restart it!

You are done!

Good Advice!!:
The explanation tells you how to set things up for all of your clients but you should better set it up with only one emailaddress and folder to move for testing purposes.

I would suggest a address from your collegue or a personal address of your own so you can easily run a lot of test until you Totally understand what's going on.

Well nothing more to see than Good Luck to you! (And please read and read until you understand)

[MOS MASTER]

A bit more advice!

If you know anybody who has ever dealt with VBA even at the lowest level..have him join you. (Two no more as one)

But...follow the directions as stated above!

Btw you forgot to mention your Outlook version so my story can differ in your version!

[snoopy]

OMG... Wow
I will follow your instruction to the letter this looks very scary... Once I have got it all in I will post any comments etc.

Thank very very much for your help

I will be in touch after the weekend. Hava good one. I am using 2003

Snoopy

[MOS MASTER]

Hi Snoopy,

You're welcome and please take your time to absorb all that's given to you. (Because all has to be done as stated or else it will fail)

Glad you're on 2003 I'm aswell...(Dutch)

Have a nice weekend...

[snoopy]

M.O.S. Master

Hope you had a good weekend. Its Great! Works a treat thank you very much.

Snoopy

[MOS MASTER]

M.O.S. Master

Hope you had a good weekend. Its Great! Works a treat thank you very much.

Snoopy

Hi Snoopy,

Yepz..my weekend was grant!
Glad you have it working...You're welcome!