在Outlook中将选定电子邮件的电子邮件正文显示为Excel中的消息框? [英] display email body of selected email in outlook as a message box in excel?

问题描述

我正在使用excel 2010，并创建了以下vba代码，该代码查找在主题标题中包含单词test的电子邮件，然后在excel中显示带有该电子邮件正文的消息框:

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

我的最终目的是在代码中添加更多条件，以便只有当前选择的电子邮件或在Outlook中打开的电子邮件的电子邮件正文才会显示在我的excel消息框中

例如，假设我们有几封电子邮件的主题为"test"(按目前的样子)，该代码将在不同的消息框中依次显示所有主题为"test"的电子邮件的正文. /p>

但是，如果Outlook中当前选定/打开的电子邮件主题为测试"，则应该只显示一个消息框.

请有人能告诉我我该怎么做吗?

解决方案

您可以使用Application.ActiveInsepctor属性获取打开的电子邮件，也可以使用Application.ActiveExplorer属性获取选定但未打开的电子邮件

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

首先，它查找活动的，已打开的项目.如果这不是电子邮件，那么它将在资源管理器"处于活动状态的情况下查看第一个选定的项目.如果那不是电子邮件，那么它会给您消息.

如果打开的活动项目是电子邮件，它将使用该电子邮件并测试主题.在这种情况下，它不会查看任何选定的项目.仅当没有未打开的内容或未打开的项目不是Mailitem(例如，它是CalendarItem)时，它才会查看所选内容.

例如，如果您在日历中，则它是活动的浏览器，很可能任何选择都不是MailItem.它也不在乎您是否在另一个文件夹的收件箱中.它只关心所选的第一个项目是否是MailItem.如果这对您很重要，您可以检查olapp.ActiveExplorer.CurrentFolder以确保您位于收件箱中.

您可以在邮件文件夹中拥有不是MailItems的项目.如果未选择MailItem，则会收到一条消息.

最后，您可以在收件箱"中选择任意数量的项目.这只会查看选择的前几项.如果要处理所有项目，可以使用.Selection.Count和.Selection.Item(i)浏览它们以依次处理每个项目.

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?

解决方案

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.

这篇关于在Outlook中将选定电子邮件的电子邮件正文显示为Excel中的消息框?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！