Automatic color category according to variable domains

Status
Not open for further replies.

Joffrey_K

New Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server 2010
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

upload_2017-7-13_10-16-8.png


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

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
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

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
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

New Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server 2010
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

New Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server 2010
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
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
B resend if no reply and send an automatic reminder Outlook VBA and Custom Forms 0
B Removing Recipients from an automatic response Outlook VBA and Custom Forms 2
N automatic response with an attachment based on the subject line Outlook VBA and Custom Forms 1
E How to send automatic emails in outlook 2010 Using Outlook 1
N VBA Script to Send Automatic Emails from Outlook 2010 Outlook VBA and Custom Forms 1
M Outlook 2016 Desktop - Automatic Rule Processing Using Outlook 3
P Threat to being a spammer while sending automatic Emails through VBA Using Outlook 3
reza Macro to automatic reply using orignal mail Outlook VBA and Custom Forms 10
B Automatic picture download and changing email addresses Using Outlook 3
J why won't the automatic send/receive work Using Outlook 7
S rule, automatic replies, restricting them to within the company Using Outlook 1
I Automatic reply to an email Using Outlook 0
P Daily Automatic Reply Using Outlook 1
P Automatic reply on incoming messages in outlook using VBA(only Specifi person) Using Outlook 14
R automatic signature Using Outlook 0
B Looking for an add-in or a way to send automatic replies based off a list Using Outlook 2
H Automatic excel data added to Certain Emails that are received Using Outlook 3
M Automatic Bcc Using Outlook 2
C starting Outlook 2003 sp3 sets Junk Email setting to 'No automatic filtering' Using Outlook 3
K automatic cleanup folder Using Outlook 3
B How to automatic save Sending or replying contacts e-mails into contacts? Using Outlook 0
J Form design - how do I insert an automatic date/time field? Using Outlook 2
C Text reply mail in Outlook 2010 has automatic margin indent Using Outlook 9
T Automatic forwarding rule strips attachment Using Outlook 3
C How do I cancel automatic e-mail forwarding? Using Outlook 1
F Schedule an automatic send/receive every 1 minute Using Outlook 1
O I suddenly can't open Outlook after getting a microsoft automatic update. Using Outlook 2
M Outlook email not working since last automatic update. Using Outlook 2
A How do I stop automatic e-mail forwarding? Using Outlook 2
Z How to disable Automatic Wrap Text for sent email? Using Outlook 3
M Exchange 2010 Services are not starting automatic on startup Exchange Server Administration 39
J Automatic Send Receive not working Using Outlook 2
P Automatic personalized reply Outlook VBA and Custom Forms 1
D Stop automatic opening BCM (Business Contact Manager) 1
P propagate automatic formatting to other Outlook folder Outlook VBA and Custom Forms 1
D Re: Propogate Automatic Formatting to Other Folder in Outlook 2007 Outlook VBA and Custom Forms 1
S Sending automatic email from Access using Outlook Outlook VBA and Custom Forms 1
T Code to import address book in automatic mode Outlook VBA and Custom Forms 1
D Inbox column color coding Using Outlook 2
J Outlook 2013 Change color of text in data fields of contacts in Outlook 2013? Using Outlook 9
A How to get body of all emails in outlook 2016 to view in blue color Using Outlook 1
B Change row background color of selected item Using Outlook 1
R Setting font and color used when replying to an email Outlook VBA and Custom Forms 3
Mark Foley Color Categories on IMAP mail lost when installing new Windows 7 workstation Using Outlook 12
M Emails sorted by conversation including replies and categorized by color. Using Outlook 1
C Color Code New Calendar Items Using Outlook 2
S Mark as Unread unless Categorized by Color Using Outlook 0
Diane Poremsky Category Color doesn't Display in Inbox New Slipstick.com Articles 0
Diane Poremsky Printing Calendars with Color Categories New Slipstick.com Articles 0
E Outlook 2016 Default Blue Color Using Outlook 3

Similar threads

Top