Check the received email user name from outlook with the excel specific workbook colum "K".

Sharath,

Quick question - when you are going to check the Outlook for a specific name, are you going to look at the Inbox or do you have a particular folder to look at?

- Ardhendu

Hi pari,

I will have a particular Folder to check

CAn you supply a sample sheet and confirm if it is the email address that is being checked in column K.

Chris

Hi Chris
Attached the excel file
excel.xlsx

Hi Chris any views with this

Sorry missed the file!

MY apologies to broomee9 but I am going to object to give me some wiggle room whilst I try and answer.

Chris

OKay, I have an old copy of that file I can use but the fact is it's easy enough to understand now with a little bit of effort.

First off I am running something similar already but I want to change the functionality because of problems.

Essentially I am running a rule to trigger the macro which saves data BUT ...

It regularly fails because I am doing something in excel when a mail comes in and the end result is ther rule is turned off because of the conflict.  Unless you can guarantee you will not have excel open at all never mind the same file open then you will have the same problem.

I am thinking on a concept to use a rule to trigger a script that records data in a text file.  The concept now is I open the text file on start-up and update the sheet that way.  As such it's a one time operation and as long as I do not have the file open at the start of outlook and excel isn't running a macro then it will be okay.

Over to you for your thoughts!

Chris

Thanks Chris
I will have this excel open always
Can we have a macro when run checks the opened excel and specific colum and updates the results to another colum.

So this works only when this specific excel is opened

You can but if for example you have any macros running they will still interfere ... for example when changing a cell a macro runs on the change event handler.

Chris

But in my case i will need to update the excel thats my master excel.
That will always be open... Any ideas...

Two:

1. Take a chance it doesn't bother you.
2. Use a mechanism where the outlook data is asaved independantly and you update excel on demand, i.e. a button, on open or on startup / shutdown of excel ... or in the worst case a timer running in excel.

Chris

2. On demand is fine. When macro run..

OKay then I haven't done this for myself yet, though the mechanism is clear in my mind ;o)

Two stages, one to save the data to a file and the second to upload the data from the file.

Basically in terms of outlook stand alone requirement, create a text file with the sender email for every email received?

note the next stage after that is to open the file, and process the names within the file against the excel sheet.

Chris

I will need to query a specific folder in outlook
Everything else is fine

That changes my understanding, (still doable) I was assuming this was to occur on receipt of an email.  If it is run manually against a specific folder that can potentially be done by excel code.  If it's run when an item is added to that folder then it's similar to before and my understanding is unchanged, (just the required method changes slightly).

Chris

Say i email to a group of users asking them to fill mobile no's.

I get replies from 100 users and i want to get there names matched with my excel colym K where i have the full names.
The base is this
Running it by selecting the emails in the folder or the whole folder no problem

Okay, that does make it easier ... I think, i.e. it's a dynamic selection so no reason it cannot be automated from outlook, as per your original thoughts and i've wandered around the park until I got to understand.

Let me think a bit more whilst I work on other questions as well.

Chris

Ok Chris thanks

See attached as a sub in outlook.

1. If excel is not open or the workbook is not open it exits

2. Constants to change are:
strWorkbookPathandName
and
strWorkSheetName

3. It maybe that the sender email address is not suitable as the stored data in your worksheet may be processed from the actual sender addy, but we'll cross that bridge when appropriate.

Chris

Sub q_26346535()
Dim xlapp As Object
Dim xlwb As Object
Dim xlws As Object
Dim xlRange As Variant
Const strWorkbookPathandName As String = "C:\Users\Chris\Experts Exchange\Password-SEARCH.xls"
Const strWorkSheetName As String = "Sheet88"

    On Error Resume Next
    Set xlapp = GetObject(, "excel.application")
    If xlapp Is Nothing Then Exit Sub 'application not open
    For Each xlwb In xlapp.workbooks
        If LCase(xlwb.FullName) = LCase(strWorkbookPathandName) Then Set xlws = xlwb.sheets(strWorkSheetName)
    Next
    If xlws Is Nothing Then Exit Sub 'Workbook not open
    On Error GoTo 0
    For Each itm In Application.ActiveExplorer.Selection
        Set xlRange = xlws.Range("k:K").Find(What:=itm.sendereMailAddress, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
        If Not xlRange Is Nothing Then
            With xlRange.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = vbGreen '5296274
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
        End If
    Next
    
End Sub

Select allOpen in new window

Previously tested in excel due to issues with my emails being different to your senders.

There were some bugs which are hopefully fixed here.

Chris

Sub q_26346535()
Dim xlapp As Object
Dim xlwb As Object
Dim xlws As Object
Dim xlRange As Variant
Dim itm As Object
Const strWorkbookPathandName As String = "C:\Users\Chris\Experts Exchange\excel.xlsx"
Const strWorkSheetName As String = "desktops"
Const xlWhole As Integer = 1

    On Error Resume Next
    Set xlapp = GetObject(, "excel.application")
    If xlapp Is Nothing Then Exit Sub 'application not open
    For Each xlwb In xlapp.workbooks
        If LCase(xlwb.FullName) = LCase(strWorkbookPathandName) Then Set xlws = xlwb.sheets(strWorkSheetName)
    Next
    If xlws Is Nothing Then Exit Sub 'Workbook not open
    On Error GoTo 0
    For Each itm In Application.ActiveExplorer.Selection
        Set xlRange = xlws.Range("k:K").Find(What:=itm.sendereMailAddress, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
        If Not xlRange Is Nothing Then
            With xlRange.Interior
                .Pattern = 1 'xlSolid
                .PatternColorIndex = -4105 'xlAutomatic
                .Color = vbGreen '5296274
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
        End If
    Next
    
End Sub

Select allOpen in new window

Which colum will i get the results?

K

Which will it compare?

COmpare?

I thought the idea was to mark the column data if a mail is received from the same name

Chris

I have the exact names full names already in Colum K in the excel. That's what i want to compare. Only then i will know who have not emailed me back.
Results in any other colum as . Received.

Sorry if i got you in the wrong route till now.

SO when checking the mails

If the sender email matches a value in column K then an entry in another column is to be bade which simply says "Received" , no coluring of data?

ALso please define the column to be used as I recall how complex this workbook is!

Chris

yes Chris all fine
"DW" is the colum

Try this change then

Chris

Sub q_26346535()
Dim xlapp As Object
Dim xlwb As Object
Dim xlws As Object
Dim xlRange As Variant
Dim itm As Object
Const strWorkbookPathandName As String = "C:\Users\Chris\Experts Exchange\excel.xlsx"
Const strWorkSheetName As String = "desktops"
Const xlWhole As Integer = 1

    On Error Resume Next
    Set xlapp = GetObject(, "excel.application")
    If xlapp Is Nothing Then Exit Sub 'application not open
    For Each xlwb In xlapp.workbooks
        If LCase(xlwb.FullName) = LCase(strWorkbookPathandName) Then Set xlws = xlwb.sheets(strWorkSheetName)
    Next
    If xlws Is Nothing Then Exit Sub 'Workbook not open
    On Error GoTo 0
    For Each itm In Application.ActiveExplorer.Selection
        Set xlRange = xlws.Range("k:K").Find(What:=itm.sendereMailAddress, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
        If Not xlRange Is Nothing Then
            With xlws.Range("DW" & xlrange.row).Interior
                .Pattern = 1 'xlSolid
                .PatternColorIndex = -4105 'xlAutomatic
                .Color = vbGreen '5296274
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
        End If
    Next
    
End Sub

Select allOpen in new window

I selected a folder and ran the script and even selected all mails and ran. But no results in the "DW" colum
I have the xlsm file open and have editted the path in the code as well

Most likely was as I surmised earlier that the sender email addresses are for example exchange addresses.  Can you examine the mails in respect of sender email and see how they relate to the content of the worksheet?

Chris

Ya guess it wont work.
The exchange email email address will not maytch as they come in a different format
Can i match with the display name of the sender with my colum K data

Thanks a lot Chris