1

I am looking to read my Outlook inbox for all emails received today that have "Cancel Alert" in the subject.

In the body of the these emails there are two rows of data with 6 columns. Fairly simple email body as seen below

Record ID    Date        Name   Date 2       Status     Note
R-99864      06-20-2019  Fonsi  06-19-2019   Canceled   Record was out of date range

I would like to extract the Record ID and place it into my excel worksheet.

I can identify my emails by searching for the email subject. However, I don't know how to extract the Record ID.

Can you please show me how to use Excel VBA to extract my Record ID?

Below is my code for determining my emails by the email subject.


Dim myOlApp As New Outlook.Application
Dim myNameSpace As Outlook.Namespace
Dim myInbox As Outlook.MAPIFolder
Dim myitems As Outlook.Items
Dim myitem As Object
Dim Found As Boolean

Set myNameSpace = myOlApp.GetNamespace("MAPI")
Set myInbox = myNameSpace.GetDefaultFolder(olFolderInbox)
Set myitems = myInbox.Items
Found = False

For Each myitem In myitems
    If myitem.Class = olMail Then
        If InStr(1, myitem.Subject, "Cancel Alert") > 0 Then
            Debug.Print "Found"
            MsgBox (myitem.Subject)
            Found = True
        End If
    End If
Next myitem

'If the subject isn't found:
If Not Found Then
    NoResults.Show
End If


'myOlApp.Quit
Set myOlApp = Nothing

End Sub
Computer_Nerd
  • 102
  • 11
  • Plain text mail or ? – Tim Williams Jun 20 '19 at 20:24
  • How do I determine if it is plain text mail? – Computer_Nerd Jun 20 '19 at 20:25
  • Open it up, click the "edit message" button, then go to the "format text" tab and see which option is highlighted (HTML, plain text or rich text) – Tim Williams Jun 20 '19 at 20:39
  • I clicked "Reply" to open up the email since there were no "edit message" buttons. On my reply, the "Aa HTML" was already highlighted so I'm guessing the text is in HTML format. – Computer_Nerd Jun 20 '19 at 20:42
  • You should be able to use `InStr(1, .htmlbody, "Record ID")`; with that knowledge, you can `Split()` your email based on line breaks (i think it's `Chr(58)`... this is a big assumption that you have breaks before and after your data), then take the section of your split that starts with the characters recorded from the `InStr` and paste it. – Cyril Jun 20 '19 at 20:48
  • @Cyril I don't think your instructions can help me. Your instructions are a bit unclear to me. – Computer_Nerd Jun 20 '19 at 21:30
  • This is how to split the body into lines, in an array. https://stackoverflow.com/questions/31717844/find-carriage-return-in-a-mail-body. Look through the elements of the array until you find the string "Record ID". In the next element, text before the first space will be the data you want. – niton Jul 26 '19 at 22:11

0 Answers0