CODEWITHSUNDEEP
excelvbaoutlook
Mark harris
Mark harris

Reputation: 543

display email body of selected email in outlook as a message box in excel?

I am using excel 2010 and have created the following vba code which finds emails which contain the word test in their subject heading and then in excel it shows a message box with the body of that email:

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
    Set objItem = olApp.ActiveExplorer.Selection.Item(1)


    olItms.Sort "Subject"

    i = 1

    For Each olMail In olItms
     If InStr(olMail.Subject, "Test") > 0 Then
            MsgBox olMail.Body
            i = i + 1
        End If
    Next olMail

    Set olFldr = Nothing
    Set olNs = Nothing
    Set olApp = Nothing

End Sub

My ultimate aim here is to add more if conditions to the code, so that only the currently selected email or opened email in outlook has its email body displayed in my excel message box

An example of this is say we have several emails with the subject "test" as it currently stands the code will show the body of all these emails which have the subject "test" one after another in a different message box.

But there should only ever be one message box displayed for the currently selected/opened email in outlook if that emails subject is "test".

Please can someone show me how I can do this?

Upvotes: 2

Views: 5911

Answers (1)

Dick Kusleika
Dick Kusleika

Reputation: 33175

You can use the Application.ActiveInsepctor property to get the opened email or use the Application.ActiveExplorer property to get a selected, but unopened, email

Sub GetFromInbox()

    Dim olApp As Outlook.Application
    Dim olMail As Outlook.MailItem

    Set olApp = New Outlook.Application

    'If it's not an MailItem or there's no
    'ActiveInspector, error is ignored
    On Error Resume Next
        Set olMail = olApp.ActiveInspector.CurrentItem
    On Error GoTo 0

    'If nothing is open, see if a MailItem is selected
    If olMail Is Nothing Then
        On Error Resume Next
            Set olMail = olApp.ActiveExplorer.Selection.Item(1)
        On Error GoTo 0
    End If

    If Not olMail Is Nothing Then
        If InStr(olMail.Subject, "Test") > 0 Then
            MsgBox olMail.Body
        Else
            MsgBox "Selected/active email does not have correct subject"
        End If
    Else
        MsgBox "Active item is not an email or no email selected"
    End If

End Sub

First, it looks for the active, opened item. If that's not an email, then it looks at the first selected item in whatever "Explorer" is active. If that's not an email, then it gives you a message.

If the open, active item is an email, it uses that and tests the subject. In that case it doesn't look at any selected items. Only if there's nothing open or the open item is not a Mailitem (like it's a CalendarItem), does it look at what's selected.

If you're in, say, the calendar, then that's the active explorer and it's likely that any selection is not a MailItem. It also doesn't care if you're in the Inbox of another folder. It only cares if the first item selected is a MailItem. You could check olapp.ActiveExplorer.CurrentFolder to make sure you're in the Inbox if that's important to you.

You can have Items in your mail folders that are not MailItems. If it's not a MailItem that's selected, then you'll get a message.

Finally, you can select any number of items in your Inbox. This only looks at the first items selected. If you want to process all the items, you can look through them using .Selection.Count and .Selection.Item(i) to process each one in turn.

Upvotes: 4

Related Questions