• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to identify key word in latest e-mail

ThrottleWorks

ThrottleWorks

Excel Ninja
Hi,

To be honest I have 0 experience in coding related to outlook.

I am trying a macro which include below steps.

Find set of key words in latest e-mail. For example, Yamaha, Honda, Suzuki.

Suppose an e-mail is replied 5 times then I need to search these key words in the latest part of the e-mail.

I want to run this macro through excel.
Once I get the key word, I need to store it in an excel repository with e-mail details.

Storing key words in excel should not be problem for me. But I have no idea about how to search string in e-mail.

My sincere apologies for asking ready answers, can understand if this post goes un-replied.

Can anyone please guide me about how a string is searched in an e-mail.
How shall I proceed.

Also, one more doubt is, suppose if I am running this macro twice a day.
In the first run there were 100 e-mail which macro checked promptly.

In second run there are 200 e-mail (100 + 100). How do I check only 100 e-mails which were not checked earlier.

Thanks.
 
Hi @Chihiro , thanks a lot for the help. Really sorry for late reply. I was out of station for 2-3 days. Just returned. I am checking this link. Will revert in case of any issue. Have a nice day ahead. :)
 
Hi @Chihiro . The link provided by you is good. However I will not be able use it.

I forgot to mention earlier (my mistake) that macro is supposed to take further action based on the key word findings. Suppose key word is Yamaha then flag for today, if key word is Suzuki then apply category as green.

For this I need something which will work on Outlook itself. Could you please help if possible.

Kindly let me know if you require further details. Have a nice day ahead.
 
Wait, so you need operation in Outlook? As in email itself should be tagged or categorized based on criteria? Instead doing operation on data extracted to Excel.

In that case, you may be better off running VBA straight out of Outlook rather than using Excel macro.

I personally don't have much experience in Outlook coding outside of data extraction (my company uses Gmail and I use Google Script, GYB and GrepUtil for most of my email manipulation).

One of best resource when I need to look for Outlook code is below.
http://www.outlookcode.com/
 
Hi,

I am using below mentioned code, however getting a bug while running.
Can anyone please help me understanding the error.

I am getting error at below mentioned line. I do not know how to edit this line.
Set olNs = olApp.GetNamespace(”MAPI”)
Click to expand...

The error is Run Time Error 2147024809 (80070057)
Could not complete the operation. One or more parameter values are not valid.

Code is copied from http://www.ozgrid.com/forum/showthread.php?t=154641
Code: 
Sub GetFromInbox()
  
    Dim olApp As Outlook.Application
    Dim olNs As Outlook.Namespace
    Dim olFldr As Outlook.MAPIFolder
    Dim olItms As Outlook.Items
    Dim olMail As Variant
    Dim i As Long
  
    Set olApp = New Outlook.Application
    Set olNs = olApp.GetNamespace(”MAPI”)
    Set olFldr = olNs.GetDefaultFolder(olFolderInbox)
    Set olItms = olFldr.Items
  
    olItms.Sort “Subject”
  
    i = 1
  
    For Each olMail In olItms
        If InStr(olMail.Subject, "Criteria") > 0 Then
            ThisWorkbook.Sheets("Sheet1").Cells(i, 1).Value = outMail.Body
            i = i + 1
        End If
    Next olMail
  
    Set olFldr = Nothing
    Set olNs = Nothing
    Set olApp = Nothing
  
End Sub
 
Hi @Chihiro , thanks for the help. I will re-check it.

Could you please explain if you get time, what is 'Outlook.Namespace' and 'GetNamespace(”MAPI”)'. I tried Google but could not understand.

The reason I am asking is, I do not know what it is, and how do I edit this line.

Good night. :)
 
Outlook.Namespace is basically equivalent to Application.Session.
It's required to access Outlook Data Store.

Since Outlook.Namespace requires the application to be open (and default reference to object), you use Create Object and reference to Outlook library in VBA. Then using olApp.GetNamespace(”MAPI”) you are accessing the Data Store.

"MAPI" refers to Messaging Application Programming Interface. See link for details.
https://en.wikipedia.org/wiki/Messaging_Application_Programming_Interface
 
Last edited:
Hi,

I will try to explain what I am trying to do in detail.

This macro need to run on Outlook but will be stored in an excel file

It will check all the e-mails from Inbox folder only

Macro will check multiple strings in each e-mail, for example Yamaha, Suzuki, Honda etc.

Even if macro finds any of the string next action will trigger

For example, an e-mail contains Yamaha in first line, Suzuki in 2nd line.
Actions will get trigger for Yamaha.

The actions or events could be assigning a category or marking as important etc

Also, macro should check latest part of the e-mail. For example if an e-mail is replied 5 times, the macro will search string in latest reply of e-mail

The macro will be run manually by user for n number of times in a day.
Suppose the first run was in the morning, Inbox has 100 e-mails. All checked.

Next run is in evening, Inbox has 100+100 e-mail, macro should check only latest 100 e-mails which were not checked in morning

Now the excel part
Out of 100 e-mails, say we were able to find key words in 10 e-mails.
Details of these 10 e-mails will be copied in excel file.

Previously I have copied code from internet to export e-mail details in excel file, but this is new for me.

I can understand if this post goes un-answered, trying to post in details so that it will give clear picture to the experts before dedicating their time for this issue.

Thanks.
 
You must log in or register to reply here.
Back
Top