Outlook: Check if a recipient has an automatic reply with a specific text

[beatmaker]

Hi Fellas,

I have a Excel list with emails in a specific row. I'd like to test them if they have a specific text in their automatic respond messages. If yes, I have to change status in the Excel file to 'Y'.

I already tried it code it with python but I don't get access to the outlook MAPI. But I have no clue how to code it with VB.

Does someone know how code should look like? I have found some code snippets here in the forum, but they didn't worked out.

[beatmaker]

Sub CheckAutoReply()

Dim OL As Outlook.Application
Dim EM As Outlook.MailItem
Dim R As Outlook.Recipient

Set OL = New Outlook.Application
Set EM = CreateItem(olMailItem)

Creating a dummy mail? But how to extract the excel list names and check the response message?

[westconn1]

I have a Excel list with emails in a specific row. I'd like to test them if they have a specific text in their automatic respond messages. If yes, I have to change status in the Excel file to 'Y'.

is the list of emails the email addresses?

this is a basic sample to look at all emails within some folder in inbox and it's subfolders

set ol = createobject("outlook.application")
Set ns = ol.GetNamespace("mapi")
Set ofolder = ns.GetDefaultFolder(olFolderInbox).Folders("test")
For Each f In ofolder.Folders
    For Each msg In f.Items
        if msg.sendername = cells(3,1) then Debug.Print msg.subject, f.Name
    Next
Next

i just list out the subject lines and folder here, but you can do whatever you want, change the range address for cell to match email addess
you can use the restrict method of the messages collection to save looping through all messages in a folder

[beatmaker]

The list of emails is listed in a excel file not a inbox.

[westconn1]

The list of emails is listed in a excel file not a inbox.

so where are the auto responds

if every thing is in excel, why do you want to automate outlook
the description of your problem leaves a lot to the imagination

what exactly is in excel? and how is it arranged (what columns)?
what do you want to find from where?
where is the excel status to change to Y?

[beatmaker]

so where are the auto responds

if every thing is in excel, why do you want to automate outlook
the description of your problem leaves a lot to the imagination

what exactly is in excel? and how is it arranged (what columns)?
what do you want to find from where?
where is the excel status to change to Y?

The Auto respond message is from outlook itself, when you type in the email adresse in the "to" field, a message appears above the field.

For my automation it's not about Outlook. I want just to check if there is a specific auto respond message available for each email.

A2 - Yes/Leave Empty if not
B2:A700 - email adresses

I have to set a yes in the A column, when there is a respond message part with the text e.g "not in house ..." I have to look for some words.

[westconn1]

For my automation it's not about Outlook

the excel part is pretty easy, automating outlook refers to using code to operate outlook form some other application

The Auto respond message is from outlook itself, when you type in the email adresse in the "to" field, a message appears above the field.

i will have to look into this further as i do not think my version of outlook has this provision

[RobDog888]

The Auto respond message is from outlook itself, when you type in the email adresse in the "to" field, a message appears above the field.

For my automation it's not about Outlook. I want just to check if there is a specific auto respond message available for each email.

A2 - Yes/Leave Empty if not
B2:A700 - email adresses

I have to set a yes in the A column, when there is a respond message part with the text e.g "not in house ..." I have to look for some words.

If you are not automating Outlook at any point then where are you reading the emails from to check for an OOF reply?

[westconn1]

doesn't an out of office auto response go to all emails received, based on criteria as set up?

[RobDog888]

doesn't an out of office auto response go to all emails received, based on criteria as set up?

Correct, so if the user has some rule it may not apply nor reply with the automated message. Plus, there are web mail interfaces which can also produce an out of office reply. It doesnt seem like there is a concrete footprint to detecting an out of office reply.

[westconn1]

he really needs to clarify the question, and why some may not be set up to have an auto response

[beatmaker]

he really needs to clarify the question, and why some may not be set up to have an auto response

Because that's the only way to check if user is still working for us or not anymore.

[westconn1]

Because that's the only way to check if user is still working for us or not anymore.

lol!!

[RobDog888]

Because that's the only way to check if user is still working for us or not anymore.

The best sure fire way to know if a user is still employed with your company is to validate their windows login account against your companies Active Directory. If they dont have a windows logon and only company email then querying against the exchange server or webhost for a valid account would be best vs sending emails to see if its out of office etc

[beatmaker]

Not possible because I don't have server access That's the only way to check via outlook. That's I thought to automate reading the responses.

[westconn1]

so if the user is no longer working there, you get some automated response to your inbox, for your email query to him/her?

do you want to send an email to all email addresses on your list, to see if you get a vaild (human) response or the automated one?
if this is what you want to do it is relatively simple, though any response to an email may take some time to come and may not come in the order of the sent queries

[beatmaker]

so if the user is no longer working there, you get some automated response to your inbox, for your email query to him/her?

do you want to send an email to all email addresses on your list, to see if you get a vaild (human) response or the automated one?
if this is what you want to do it is relatively simple, though any response to an email may take some time to come and may not come in the order of the sent queries

No, I do not want to send emails neither do I get mails. At this moment I copy about 50 emails from my list and paste them in to the "send to" field. Outlook fetch the automatic reply and it pops out (see image from internet as example). And when there is a specific text e.g. "I'm not working here", I'm editing the list and add a "yes" next to this email adress.

[westconn1]

now i understand better, try like

Set ol = CreateObject("outlook.application")
Dim r As Recipient, n As NameSpace, t As String
t = "I'm not working here"
Set n = ol.GetNamespace("mapi")
For rw = 2 To 700
    Set r = n.CreateRecipient(cells(rw, 2))
    If InStr(r.AutoResponse, t) > 0 Then cells(rw, 1) = "YES"
Next
ol.quit

i can not test this, as no one in my recipients list would have any autoresponse (i am not on an exchange server) set up

edit: to check if the recipient is a valid email address in your address book you would need to check that each recipient has an addressentry.address

[beatmaker]

In excel or outlook?

I've tried in outlook but it's closing my outlook app (ol.Quit) and blocked authorization of excel file.

For your information: The auto replay is different each time because autoreplay contains their names at the beginning ... "I'm out of work Name Surename. Visit us at website."

[westconn1]

For your information: The auto replay is different each time because autoreplay contains their names at the beginning ... "I'm out of work Name Surename. Visit us at website."

i used instr to do a partial match, but obviously you will need to have some common text

In excel or outlook?

i assumed you would be working within excel and automated outlook, but it could be easily modified to work from outlook and automate excel

if outlook is already open remove the ol.quit, it does not matter anyway, if outlook is left open after the code has completed, i was just tidying up

[beatmaker]

i used instr to do a partial match, but obviously you will need to have some common text

With python you can match parts of a text. Is it not possible to regex in vb? e.g.:

Dim foo = "I'm out of work Name Surename. Visit us at website."

Dim match = Regex.Match(foo, "X", RegexOptions.IgnoreCase)

I've tested the code but nothing is happen.

Public Const ressourcePlanFile_ As String = "C:\.xls"
Public Const worksheet_ As String = "worksheet"

Sub CheckData()

 Dim xlApp As Excel.Application
 Dim sourceWB As Excel.Workbook
 Dim sourceWS As Excel.Worksheet
 
 Set xlApp = CreateObject("Excel.Application")
 
  With xlApp
 .Visible = False
 .EnableEvents = False
 End With

 Set sourceWB = Workbooks.Open(ressourcePlanFile_, , False, , , , , , , True)
 Set sourceWH = sourceWB.Worksheets(worksheet_)

Set ol = CreateObject("outlook.application")
Dim r As Recipient, n As NameSpace, t As String
t = "I'm out of work"
Set n = ol.GetNamespace("mapi")
For rw = 2 To 100
    Set r = n.CreateRecipient(Cells(rw, 13))
    If InStr(r.AutoResponse, t) > 0 Then Cells(rw, 3) = "Yes"
Next

End Sub

[Arnoutdv]

Nothing happens...

You are writing the "Yes" in Cells(rw, 3)
What is Cells??
It's not part of your xlApp, sourceWB or sourceWS

[RobDog888]

With python you can match parts of a text. Is it not possible to regex in vb? e.g.:

Dim foo = "I'm out of work Name Surename. Visit us at website."

Dim match = Regex.Match(foo, "X", RegexOptions.IgnoreCase)

I've tested the code but nothing is happen.

Public Const ressourcePlanFile_ As String = "C:\.xls"
Public Const worksheet_ As String = "worksheet"

Sub CheckData()

 Dim xlApp As Excel.Application
 Dim sourceWB As Excel.Workbook
 Dim sourceWS As Excel.Worksheet
 
 Set xlApp = CreateObject("Excel.Application")
 
  With xlApp
 .Visible = False
 .EnableEvents = False
 End With

 Set sourceWB = Workbooks.Open(ressourcePlanFile_, , False, , , , , , , True)
 Set sourceWH = sourceWB.Worksheets(worksheet_)

Set ol = CreateObject("outlook.application")
Dim r As Recipient, n As NameSpace, t As String
t = "I'm out of work"
Set n = ol.GetNamespace("mapi")
For rw = 2 To 100
    Set r = n.CreateRecipient(Cells(rw, 13))
    If InStr(r.AutoResponse, t) > 0 Then Cells(rw, 3) = "Yes"
Next

End Sub

Yes you can use RegEx in VBA. Add a project reference to "Microsoft VBScript Regular Expressions 5.5" and then you will have it.

[RobDog888]

Nothing happens...

You are writing the "Yes" in Cells(rw, 3)
What is Cells??
It's not part of your xlApp, sourceWB or sourceWS

Cells is the WorkSheet cells object. Should be referencing it derrived off of an object variable

[Arnoutdv]

I know it's the worksheets Cells object if you are programming in Excel VBA, but if this is the case then why have a reference to Excel.Application.
Or TS is using Cells without the complete reference like sourceWS.Cells()

[RobDog888]

I know it's the worksheets Cells object if you are programming in Excel VBA, but if this is the case then why have a reference to Excel.Application.
Or TS is using Cells without the complete reference like sourceWS.Cells()

I'd say the excel object variables are used for referencing the other aka source workbook from within the active workbook and the OP is not doing the same by using the active objects. Its always good practice to create and reference object variables instead of the "active" sheet or ranges etc. While the code is running if the user clicks on a sheet it could change the active sheet thus throwing off which sheet data is being written etc

[Arnoutdv]

And you risk Excel not close when terminating all objects because of these implicit references.

Id say the excel object variables are used for referencing the other aka source workbook from within the active workbook and the OP is not doing the same by using the active objects

Automating an instance of Excel from within Excel itself? Sounds ... odd.

[westconn1]

if you are more familiar with python you could adapt the vba to python easily enough, just createobjects for both excel and outlook as you are doing

if you are working in vba, within 1 of those applications, you co not need to createobject for the application you are working in, as its object model is already available

i assumed you were working within excel, so the code i posted would read and write to the activesheet, not that that is good practice, much better to use full qualified ranges, like

Set r = n.CreateRecipient(sourceWH.Cells(rw, 13).value)

as mentioned above by rob, regex will work fine in vba, i really left it up to you how you matched your strings, just providing a basic example, based on information supplied

if you have an email address that you know has an autoresponse, you can test that the code returns the correct result
within outlook vba

Dim r As Recipient, n As NameSpace
Set n = application.GetNamespace("mapi")
    mytestemail = "someemailaddress"
    Set r = n.CreateRecipient(mytestemail)
    msgbox r.AutoResponse

then just make sure your string matching works correctly