Solved: sort emails by attachement name

[Danny]

I would like to know if there is a way to sort emails by what an attachment is named.
For instace there is an inbox for a team with 4 members. So if 2 emails were to come in both with an attachment. One titled Cliet123Info.xls and the other Client567Info.xls. I would want Client123Info.xls to go to team member 4, and Client567Info.xls to go to team member 1. :dunno

[JP2112]

When you say "sort" do you mean "forward", as in, you want to forward the email to a different recipient depending on the subject?

--JP


I would like to know if there is a way to sort emails by what an attachment is named.
For instace there is an inbox for a team with 4 members. So if 2 emails were to come in both with an attachment. One titled Cliet123Info.xls and the other Client567Info.xls. I would want Client123Info.xls to go to team member 4, and Client567Info.xls to go to team member 1. :dunno

[Danny]

Yes, forward would be correct. but i want to forward depending on the ame the attached file.

[JP2112]

How are they getting email sent to that inbox now? If you've got a profile or mailbox set up to receive mail for a virtual user, it would be easier to just give them access to the mailbox, so they can all read from it directly. Each user can add the profile to their Outlook setup and open the mailbox whenever they want to check mail sent to it. That's the way we did it at my last job.

Otherwise, the code would look something like this. I didn't test it but it should work as is.

Option Explicit

' register event handler for Items collection
Private WithEvents itm As Outlook.Items

Private Sub Application_Startup()

Dim olApp As Outlook.Application
Dim olNS As Outlook.NameSpace
Set olApp = Application
Set olNS = olApp.GetNamespace("MAPI")

' add event listener to default inbox
Set itm = olNS.GetDefaultFolder(olFolderInbox).Items

End Sub

Private Sub itm_ItemAdd(ByVal Item As Object)

' only act on messages with attachments
If TypeName(Item) = "MailItem" Then
If Item.attachments.Count > 0 Then
Dim Msg As Outlook.MailItem
Dim attachs As Outlook.attachments
Dim att As Outlook.Attachment
Set Msg = Item
Set attachs = Msg.attachments

For Each att In attachs
If att.DisplayName = "Client123Info.xls" Then
Call ForwardMsg(Msg, "team member 4 email address")
Exit For
ElseIf att.DisplayName = "Client567Info.xls" Then
Call ForwardMsg(Msg, "team member 1 email address")
Exit For
End If
Next att

End If
End If

ExitProc:
Set Msg = Nothing
End Sub

Private Function ForwardMsg(Msg As Outlook.MailItem, userEmail As String)
' takes a copy of the message and forwards to specified user

With Msg
.Forward
.To = userEmail
.Send
End With

End Function



Yes, forward would be correct. but i want to forward depending on the ame the attached file.

[Danny]

How are they getting email sent to that inbox now? If you've got a profile or mailbox set up to receive mail for a virtual user, it would be easier to just give them access to the mailbox, so they can all read from it directly. Each user can add the profile to their Outlook setup and open the mailbox whenever they want to check mail sent to it. That's the way we did it at my last job.

JP,
Thanks for the code that will give me something to work with.

I believe it is set up the way you described. There is a team inbox : (Team @ email.com) and also each member has an inbox: (Member "1 thru 4" @ email.com. So emails sent to a member address would be seen in only that members inbox, and emails sent to the team address would be seen in every members inbox. The problem is there can be more than 250 email in one day, and reading through all the emails just to see which ones are yours has become very time consuming. I would like to eventually forward or "sort" them by whether or not certain criteria appears in the subject, and/or header and/or attachment based on info possibly stored in an access database. Think anything like that may be feasible?
Thanks

[JP2112]

It sounds like you have a distribution list (team@email.com), not a profile. If that is the case, the code I gave you wouldn't work, since everyone is already receiving all of the emails, it would actually just increase the emails everyone gets. You would get a copy of the email, plus a second copy when someone's VBA code forwards it to you.

If you ask your Exchange administrator to set up a profile for the team, it would create a virtual user, with its own set of folders. Any emails sent to team@email.com would stay in Team's mailbox instead of being sent to everyone.

Then each team member would simply add the profile to their Outlook as described here: http://support.microsoft.com/kb/829918

At that point, you could use VBA to auto-route emails from the Team mailbox to other users as needed. I have sample code at Handling Multiple Inboxes (http://www.codeforexcelandoutlook.com/blog/2009/01/handling-multiple-inboxes/) that describes how to do that (in addition to what I provided you). Some companies might also set up a separate PC, logged in to the virtual user profile, just for managing that account (i.e. running VBA). Otherwise the VBA code would need to exist on the computer of someone who is on the team and has access to the account.

Everything you describe is possible, the link I provided above is a starting point.

HTH,
JP



JP,
Thanks for the code that will give me something to work with.

I believe it is set up the way you described. There is a team inbox : (Team @ email.com) and also each member has an inbox: (Member "1 thru 4" @ email.com. So emails sent to a member address would be seen in only that members inbox, and emails sent to the team address would be seen in every members inbox. The problem is there can be more than 250 email in one day, and reading through all the emails just to see which ones are yours has become very time consuming. I would like to eventually forward or "sort" them by whether or not certain criteria appears in the subject, and/or header and/or attachment based on info possibly stored in an access database. Think anything like that may be feasible?
Thanks