从Excel按日期和主题(带通配符)搜索“已发送邮件"中的邮件 [英] Search for mail in Sent Items, by date and subject with wildcard, from Excel

问题描述

我需要搜索已发送邮件"中在当前日期发送且主题为任务已完成"的邮件.有时，主题可能还有其他文本，例如任务已在2017年7月1日完成"或任务已在2017年1月9日完成".

I need to search for mail in Sent Items, sent on current date and with subject as "Task Completed". Sometimes Subject may have additional text like Task Completed on 07/01/2017 or Task Completed 01/09/2017.

我找到了此Outlook VBA代码，该代码显示找到的邮件.我希望代码在具有通配符搜索选项的Excel中运行，并打开一个Excel文件.

I found this Outlook VBA code, which displays found mail. I want the code to run in Excel with wildcard search options and open an Excel file.

我尝试使用通配符"*"搜索主题，例如任务已完成*"和任务已在& Format(Date，"dd/mm/yyyy"))，语法错误/编译错误

I tried to search the subject with wildcard "*", like "Task Completed*" and "Task Completed on & Format(Date, "dd/mm/yyyy")" for which I got an syntax error/compile error

Sub Test()

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

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

For Each olMail In Fldr.Items
    If InStr(olMail.Subject, "Task Completed on 07/01/2017") <> 0 Then
        olMail.Display
        i = i + 1    
    End If    
Next olMail

End Sub

我正在使用Office 2010.

I am using Office 2010.

推荐答案

为了遍历 已发送邮件 文件夹中的所有项目，包括您可能拥有的日历事件，使用Dim olMail As Object(而不是AS Outlook.MailItem).

In order to loop through all items in Sent Items folder, including Calendar events you may have, use the Dim olMail As Object (instead of AS Outlook.MailItem).

要在电子邮件标题中的某个位置查找任务已完成"字符串，请使用If olMail.Subject Like "*Task Completed*" Then(在搜索到的字符串之前和之后添加通配符*).

To look for "Task Completed" string somewhere in the email's title, use If olMail.Subject Like "*Task Completed*" Then (adding the wildcard * before and after the searched string).

我添加了两行代码，这些代码将所有匹配的电子邮件输出到A列和B列中的工作表.

I've added 2 lines of code, that output all matching emails to your worksheet in Column A and Column B.

代码

Option Explicit

Sub Test()

Dim olApp As Outlook.Application
Dim olNs As Namespace
Dim Fldr As MAPIFolder
Dim olMail As Object
Dim i As Integer, j As Integer

Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
Set Fldr = olNs.GetDefaultFolder(olFolderSentMail)

i = 1
For Each olMail In Fldr.Items
    ' check if mail subject contains "Task Completed" in the email title
    If olMail.Subject Like "*Task Completed*" Then
        'Range("A" & i).Value = olMail.Subject ' <-- output email name to column A
        'Range("B" & i).Value = olMail.SentOn ' <-- output email sent date to column B
        olMail.Display ' show email through Excel
        i = i + 1
    End If
Next olMail

End Sub

这篇关于从Excel按日期和主题(带通配符)搜索“已发送邮件"中的邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！