none
Search Outlook email with Excel VBA

    Question

  • Hi everyone,

    I have the below code which works perfectly in Outlook but I need to launch it from Excel VBA and I cannot seem to be able to get it to run. My biggest problem is with the variables since Excel VBA would not accept anything along the lines of Dim x As Outlook.Namespace or Outlook.Mailitem.

    Any ideas on how to solve this? I would very much appreciate any suggestions.

    Thank you,

    Alex

    Sub NEW_JULY()

        Dim olApp As Outlook.Application
        Dim olNs As NameSpace
        Dim Fldr As MAPIFolder
        Dim olMail As Variant
        Dim i As Integer

        Set olApp = New Outlook.Application
        Set olNs = olApp.GetNamespace("MAPI")
        Set Fldr = olNs.GetDefaultFolder(olFolderInbox)
        i = 1

       For Each olMail In Fldr.Items
            If InStr(olMail.Subject, "MACRO VAX 48634 REPORT") <> 0 Then
            olMail.Display
            i = i + 1
            End If
        Next olMail

    End Sub

    Tuesday, July 10, 2012 3:55 PM

Answers

  • A big thank you to Ken, Damian and Dmitry for helping me sort out this matter. For future reference I am posting the code for copying the contents of an Outlook email into an Excel sheet based on email subject (Macro controlled from Excel)

    Sub Work_with_Outlook()

     Set olApp = CreateObject("Outlook.Application")
     
        Dim olNs As Outlook.Namespace
        Dim Fldr As Outlook.MAPIFolder
        Dim olMail As Variant
        Dim sir() As String
      
        Set olApp = New Outlook.Application
        Set olNs = olApp.GetNamespace("MAPI")
        Set Fldr = olNs.GetDefaultFolder(olFolderInbox)
        Set myTasks = Fldr.Items

      Set olMail = myTasks.Find("[Subject] = ""*desired subject*""")
      If Not (olMail Is Nothing) Then
      sir = Split(olMail.Body, vbCrLf)
      For i = 1 To UBound(sir)
      ActiveWorkbook.Sheets("Sheet1").Cells(i, 1).Value = sir(i)
      Next i
      olMail.Delete
      End If

    End Sub

    Friday, July 13, 2012 7:22 AM

All replies

  • Add Outlook to your project refernces in Excel.

    Also, do not just loop through all messages in the Inbox - use Items.Find.


    Dmitry Streblechenko (MVP)
    http://www.dimastr.com/redemption
    Redemption - what the Outlook
    Object Model should have been
    Version 5.3 is now available!


    Tuesday, July 10, 2012 4:50 PM
  • Hi Dmitry,

    Many thanks for having the patience to point out such a basic mistake on my part :)

    I tried using Items.Find with the above code instead of InStr as below but I am getting an error saying that my condition is not recognised or that I am missing an object declaration. If it's not too much to ask, would you be able to tell me what is the correct use of the function?

    Thank you very  much,

    Alex

     Fldr.Items.Find (Items.Subject = "MACRO VAX 48634 REPORT")
     Item.Display

     OR

     Fldr.Items.Find ( "MACRO VAX 48634 REPORT")
     Item.Display

    Wednesday, July 11, 2012 8:58 AM
  • whole condiction must be a string value, something like:

    Items.Find("[Subject] = 'MACRO VAX 48634 REPORT' ") 

    Wednesday, July 11, 2012 1:46 PM
  • Hi Damian,

    Many thanks for your reply. I have made some progress since this morning and the following is working for me

     Fldr.Find("[Subject] = ""MACRO VAX 48634 REPORT""")

    However, although the search works (tested with if and MsgBox) neither Find nor Restrict allow me to open the item that is found as a result of the search. Any idea on how I can do that?

    Kind regards,

    Alex

    Wednesday, July 11, 2012 1:58 PM
  • set olMail = Fldr.Find("[Subject] = ""MACRO VAX 48634 REPORT""")

    if Not (olMail Is Nothing) Then

      olMail.Display

    End If


    Dmitry Streblechenko (MVP)
    http://www.dimastr.com/redemption
    Redemption - what the Outlook
    Object Model should have been
    Version 5.3 is now available!

    Wednesday, July 11, 2012 2:01 PM
  • Thank you very much Dmitry, I used your suggestion and the below sub works for finding and opening an email with a particular subject.

    Following the opening of the email, I am also trying to copy paste its body into Excel and the following works to the point where I am getting the entire email body pasted in a single Excel cell (if I use any other range nothing happens) which makes any following formatting impossible. Have you tried doing this before also and, if so, how did you solve this problem?

    Kind regards,

    Alex

    Sub Outlook_Trial()

    Set olApp = CreateObject("Outlook.Application")
     
        Dim olNs As Outlook.Namespace
        Dim Fldr As Outlook.MAPIFolder
        Dim olMail As Variant
      
        Set olApp = New Outlook.Application
        Set olNs = olApp.GetNamespace("MAPI")
        Set Fldr = olNs.GetDefaultFolder(olFolderInbox)
        Set myTasks = Fldr.Items


      Set olMail = myTasks.Find("[Subject] = ""MACRO VAX 48634 REPORT""")
      If Not (olMail Is Nothing) Then
      olMail.Display
     
      'copy paste into Excel
     
      vBody = olMail.Body
      ActiveWorkbook.Sheets("Sheet1").Range("A1").Value = vBody


      End If

    End Sub

    Wednesday, July 11, 2012 2:32 PM
  • Body is a plain text property. If you want to preserve any formatting in the message you probably should be using HTMLBody, which is the raw HTML for an HTML format email.

    --
    Ken Slovak
    [MVP-Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    "Alex11 CS" <=?utf-8?B?QWxleDExIENT?=> wrote in message news:d2a1ce6b-29c7-41ee-b0c3-8e06cae047c4...

    Thank you very much Dmitry, I used your suggestion and the below sub works for finding and opening an email with a particular subject.

    Following the opening of the email, I am also trying to copy paste its body into Excel and the following works to the point where I am getting the entire email body pasted in a single Excel cell (if I use any other range nothing happens) which makes any following formatting impossible. Have you tried doing this before also and, if so, how did you solve this problem?

    Kind regards,

    Alex

    Sub Outlook_Trial()

    Set olApp = CreateObject("Outlook.Application")
     
        Dim olNs As Outlook.Namespace
        Dim Fldr As Outlook.MAPIFolder
        Dim olMail As Variant
      
        Set olApp = New Outlook.Application
        Set olNs = olApp.GetNamespace("MAPI")
        Set Fldr = olNs.GetDefaultFolder(olFolderInbox)
        Set myTasks = Fldr.Items


      Set olMail = myTasks.Find("[Subject] = ""MACRO VAX 48634 REPORT""")
      If Not (olMail Is Nothing) Then
      olMail.Display
     
      'copy paste into Excel
     
      vBody = olMail.Body
      ActiveWorkbook.Sheets("Sheet1").Range("A1").Value = vBody


      End If

    End Sub


    Ken Slovak MVP - Outlook
    Wednesday, July 11, 2012 3:05 PM
    Moderator
  • Hi Ken and thank you very much for taking the time to reply!

    I am not interested in keeping the formatting but in being able to further use the data (for example applying Text to Columns in Excel to do some analysis of the report I receive via Outlook). If I use HTMLBody I still get the entire report in one cell and I am not able to do any further work on it.

    I have done some reading and maybe I should use the Split function to get the report line by line into Excel, but I am not sure on how to do that yet.

    Kind regards,

    Alex

    Wednesday, July 11, 2012 3:12 PM
  • In VBA you'd split on a newline, Chr(13) & Chr(10). That's a combination of a carriage return (13) and line feed (10).
     
    In many cases you're also going to see other ASCII characters that will be parsed incorrectly by Excel or cause other problems, such as any unprintable characters such as anything less than a space or such as vbTab. You'll need ot experiment to remove those, usually by replacing them with a "" or a printable character.

    --
    Ken Slovak
    [MVP-Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    "Alex11 CS" <=?utf-8?B?QWxleDExIENT?=> wrote in message news:2a0bbb54-1775-4369-8d6a-aeeba166278c...

    Hi Ken and thank you very much for taking the time to reply!

    I am not interested in keeping the formatting but in being able to further use the data (for example applying Text to Columns in Excel to do some analysis of the report I receive via Outlook). If I use HTMLBody I still get the entire report in one cell and I am not able to do any further work on it.

    I have done some reading and maybe I should use the Split function to get the report line by line into Excel, but I am not sure on how to do that yet.

    Kind regards,

    Alex


    Ken Slovak MVP - Outlook
    Wednesday, July 11, 2012 3:25 PM
    Moderator
  • Hi Ken,

    I am so happy, the following simple piece of code works! :)

    Dim sir() As String

    For i = 1 To 281
      sir = Split(olMail.Body, vbCrLf)
      ActiveWorkbook.Sheets("Sheet1").Cells(i, 1).Value = sir(i)
    Next i

    My one and only problem is now how to find the last row of the selected mail item  - I am using 281 now because otherwise I will get a "Subscript out of Range" error, but the number of report rows will change every day. Do you think you could point me in the right direction one last time?

    Thank you so much,

    Alex

    Wednesday, July 11, 2012 4:07 PM
  • After you split the text into an array you can use the LBound() and UBound() functions to find the lower and upper boundaries of your array.

    --
    Ken Slovak
    [MVP-Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    "Alex11 CS" <=?utf-8?B?QWxleDExIENT?=> wrote in message news:07bdbef6-5a4c-4a09-bde7-a26daba1fc2b...

    Hi Ken,

    I am so happy, the following simple piece of code works! :)

    Dim sir() As String

    For i = 1 To 281
      sir = Split(olMail.Body, vbCrLf)
      ActiveWorkbook.Sheets("Sheet1").Cells(i, 1).Value = sir(i)
    Next i

    My one and only problem is now how to find the last row of the selected mail item  - I am using 281 now because otherwise I will get a "Subscript out of Range" error, but the number of report rows will change every day. Do you think you could point me in the right direction one last time?

    Thank you so much,

    Alex


    Ken Slovak MVP - Outlook
    Wednesday, July 11, 2012 4:53 PM
    Moderator
  • A big thank you to Ken, Damian and Dmitry for helping me sort out this matter. For future reference I am posting the code for copying the contents of an Outlook email into an Excel sheet based on email subject (Macro controlled from Excel)

    Sub Work_with_Outlook()

     Set olApp = CreateObject("Outlook.Application")
     
        Dim olNs As Outlook.Namespace
        Dim Fldr As Outlook.MAPIFolder
        Dim olMail As Variant
        Dim sir() As String
      
        Set olApp = New Outlook.Application
        Set olNs = olApp.GetNamespace("MAPI")
        Set Fldr = olNs.GetDefaultFolder(olFolderInbox)
        Set myTasks = Fldr.Items

      Set olMail = myTasks.Find("[Subject] = ""*desired subject*""")
      If Not (olMail Is Nothing) Then
      sir = Split(olMail.Body, vbCrLf)
      For i = 1 To UBound(sir)
      ActiveWorkbook.Sheets("Sheet1").Cells(i, 1).Value = sir(i)
      Next i
      olMail.Delete
      End If

    End Sub

    Friday, July 13, 2012 7:22 AM
  • Hi,

    I’m glad to hear that you got it working to resolve your question.

    Thank you for sharing your solution here, it will be very beneficial for other community members who have similar questions.

    Best Regards,


    Leo_Gao [MSFT]
    MSDN Community Support | Feedback to us

    Monday, July 16, 2012 1:32 AM
    Moderator
  • HI,

    But when i run this code i gets a Compile Error

    Please help to resolve.

    Thursday, December 1, 2016 1:30 PM
  • What line in the macro code fires the error? Run the macro in debug mode and step the code (F8) until you hit the error.

    Did you add a reference to Outlook to your Excel project?


    Ken Slovak MVP - Outlook

    Thursday, December 1, 2016 2:49 PM
    Moderator