Automatic color category according to variable domains

[Joffrey_K]

Hi All,

Sorry in advance if this question has been asked already, I just can't seem to find anything online that I can understand.

My experience with VBA is limited to its applications with excel.

Everyday, I receive hundreds of emails from variable external persons.
I would like to assigned these emails to a specific category.

I have made a test with one specific domain (see below my process) using "Rules and Alerts"
step 1: Select condition(s)
- with specific words in the sender's address
==> @myselfasatest.com
step 1: Select action(s)
- assign it to the category category
==> (red) "before to go"
Click "Finish"

My rule works well, however I have thousands of different senders and I want to automatize the process and keep the database for an easier update.
I extracted all of them on Excel and worked out my database, see below an example

Would it be possible to create a Macro linked to this "database"?
The macro would work as below
if specific words in the sender's address are into column B
If true, apply category color from column D to this email
Then do nothing.

I hope I gave you a clear explanation about my issue.
I maybe took the problem in the wrong way, if you have another solution, i'll be more than happy to take it

Thanks in advance for the help!

**Just a quick edit, I do not have a coding background at all and everything I know about excel has been learned through reading online guides extensively. I see a lot of code for Outlook and it is all foreign to me, if you could please explain what is happening in the code with comments that would be so greatly appreciated! Thanks again!

Joffrey_K

 

[Diane Poremsky]

you could do a lookup (i'd probably use CSV) but would only need the domain and the category name - the color comes from the category on the master category list. I don't think i have code that does it, but it wouldn't be hard to do.

I have a macro that looks up contacts and categorizes the email based on the category contact. That code is at Assign Categories to Messages using Contact Category

 

[Michael Bauer]

I`d keep it all in Outlook. Group the view of the contacts folder by categories, then you can quickly assign the categories by dragging a contact into the appropraite group. If you press the ctrl button while dropping a contact, the new category will be added, otherwise it would replace all categories that were previously assigned to that contact.

This way you can use Diane´s macro without the need to change it. If your contacts have more categories assigned than you want to assign, you either need to adapt Diane´s macro, or use this Addin, which also looks up the contact and allows to determine categories that should never be assigned:
Category-Manager - VBOffice

 

[Joffrey_K]

Good afternoon Diane and Michael,

Sorry for the delay but I was trying to understand and to do it on my side follow your tips and Internet.

Indeed if I import all the contacts adding the category which has been set up, Diane's Macro works very well (many thanks for that Diane).
The issue is I have to update the list each time I receive an email from another user even if they have the same domain.

During the weekend I tried to understand how VBA for Outlook works.
To be honest, I didn't go far :-S, my understanding is still very limited.

I understand the function LookUp, but how to integrate it in Diane's Macro?

Many thanks for your help.

Kindest regards

Joffrey_K

 

[Joffrey_K]

Hi All,

I hope you are keeping well.

I have tried to create the wished macro follow internet instructions.

Unfortunately my Macro doesn't work (at all :-( )

Please find below the coding

Private Sub olInboxItems_ItemAdd(ByVal Item As Object)

Dim sDomain As String
Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object
Dim oSender

Set sDomain = Mid(oMsg.SenderEmailAddress, InStr(oMsg.SenderEmailAddress, "@") + 1)
'Set oSender = SenderAddress

'If Not oSender Is Nothing Then

Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.Workbooks.Open("L:\..............\Supplierlisting.xlsx")
Set xlWS = xlWB.Worksheets(Category)

Debug.Print
For Each c In xlWS.Range("E2:E15000")
Proj = c.Offset(0, 1).Value
Debug.Print c & Proj
Set sDomain = Nothing

If Not sDomain Is Nothing Then
Item.Categories = sDomain.Categories

Item.Save

Set Item = Nothing
End If
End If

'Set oSender = Nothing
Set sDomain = Nothing


End Sub

I really try to solve my problem by doing myself and making research. unfortunately without success.
Many thanks for your help

Warmest regards