Results 1 to 3 of 3

Thread: Code to read Outlook mail with specific subject

  1. #1

    Code to read Outlook mail with specific subject


    I have posted this on Mr Excel too Code to read Outlook mail with specific subject | MrExcel Message Board
    So, apologies for cross posting!
    Admins, Please delete it, if this should not be done.

    I have this working code which reads the mail and its attachment into a local folder. It is working well.
    But a new requirement is to read the mails with subject containing the words, Customer ID 1000 - Smith, JOHN P, where the word Customer ID is always static but the number and name is dynamic.
    I am not sure how to resolve this as my modified code does not read the mail which contains the word, Customer ID.
    Private Sub cmdOutlook_Click()
    Dim olApp As Object
    Dim MYFOLDER As Object
    Dim OlItems As Object
    Dim olMail As Object
    Dim x As Integer
    Dim subject As String
    Dim strFile As String
    Dim strFolderpath As String
    Dim objDestfolder As Object
    Dim filterKeywords As String
    Dim filter As String
    Set olApp = GetObject(, "Outlook.Application")
    If Err.Number = 429 Then
        Set olApp = CreateObject("Outlook.Application")
    End If
    strFolderpath = "C:\Users\Testing"
    'On Error Resume Next
    ' Set the Attachment folder.
    strFolderpath = strFolderpath & "\Attachments\"
    Set MYFOLDER = olApp.GetNamespace("MAPI").Folders("Customer Mailbox").Folders("Inbox")
     Set OlItems = MYFOLDER.Items
      'Working code
      'For Each OlMail In OlItems
            'strFile = OlMail & ".XML"
            'strFile = strFolderpath & strFile
            'If OlMail.Attachments.Count > 0 Then
                'For x = 1 To OlMail.Attachments.Count
                    'OlMail.Attachments.item(x).SaveAsFile strFile
                'Next x
            'End If
    'Modified code
     For i = OlItems.Items.Count To 1 Step -1
            If TypeOf OlItems.Items(i) Is MailItem Then
                Set olMail = OlItems.Items(i)
                If InStr(olMail.subject, "Customer ID") > 0 Then ' how to use the Like condition here?
                    strFile = strFolderpath & strFile
                    If olMail.Attachments.Count > 0 Then
                        For x = 1 To olMail.Attachments.Count
                                olMail.Attachments.Item(x).SaveAsFile strFile
                        Next x
                    End If
            End if
        End if
    Set MYFOLDER = Nothing
    Set olMail = Nothing
    Set OlItems = Nothing
    Set olApp = Nothing
    End Sub

  2. #2
    VBAX Mentor
    Nov 2022
    The Great Land
    The InStr() condition should work. Have you step debugged? Is the subject pulled?

    If olMail.Subject LIKE "*Customer ID*" Then
    How to attach file: Reading and Posting Messages (, click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    That code If olMail.subject Like "*Customer ID*" Then worked!

    Last edited by rolling_zep; 08-17-2023 at 06:24 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts