我想根据区域A:A中的单元格值搜索Outlook共享邮箱,然后根据是否找到某些内容将"Y“或"N”写入B:B。
我也想在正文和主题上进行搜索。
例如:在单元格A1中,有一个要在共享邮箱中搜索的数字1111123。
如果找到匹配,则将"Y“写入单元格B1,如果未找到,则写入"N”。
然后转到单元格A2、A3、A4等,直到区域A:A中的最后一个单元格,并将结果写入B2、B3、B4等。
此代码在Outlook中搜索活动单元格中的值,并将"Y“或"N”写入范围B1。
Option Explicit
Public Sub Search_Outlook_Emails()
Dim outApp As Outlook.Application
Dim outNs As Outlook.Namespace
Dim outStartFolder As Outlook.MAPIFolder
Dim foundEmail As Outlook.MailItem
Set outApp = New Outlook.Application
Set outNs = outApp.GetNamespace("MAPI")
Set outStartFolder = outNs.GetDefaultFolder(Outlook.olFolderInbox).Parent
'Set outStartFolder = outNs.PickFolder
If Not outStartFolder Is Nothing Then
Set foundEmail = Find_Email_In_Folder(outStartFolder, ActiveCell.Value)
If Not foundEmail Is Nothing Then
Range("B1").Select
ActiveCell.FormulaR1C1 = "Y"
End If
Else
Range("B1").Select
ActiveCell.FormulaR1C1 = "N"
End If
End Sub
Private Function Find_Email_In_Folder(outFolder As Outlook.MAPIFolder, findText As String) As Outlook.MailItem
Dim outItem As Object
Dim outMail As Outlook.MailItem
Dim outSubFolder As Outlook.MAPIFolder
Dim i As Long
Debug.Print outFolder.FolderPath
Set Find_Email_In_Folder = Nothing
'Search emails in this folder
i = 1
While i <= outFolder.Items.Count And Find_Email_In_Folder Is Nothing
Set outItem = outFolder.Items(i)
If outItem.Class = Outlook.OlObjectClass.olMail Then
'Does the findText occur in this email's body text?
Set outMail = outItem
If InStr(1, outMail.Body, findText, vbTextCompare) > 0 Then Set Find_Email_In_Folder = outMail
End If
i = i + 1
Wend
DoEvents
'If not found, search emails in subfolders
i = 1
While i <= outFolder.Folders.Count And Find_Email_In_Folder Is Nothing
Set outSubFolder = outFolder.Folders(i)
'Only check mail item folders
If outSubFolder.DefaultItemType = Outlook.olMailItem Then Set Find_Email_In_Folder = Find_Email_In_Folder(outSubFolder, findText)
i = i + 1
Wend
End Function
发布于 2019-05-23 07:26:33
切勿循环访问文件夹中的所有项目,始终使用Items.Find/FindNext
或Items.Restrict
。在您的示例中,查询将为
@SQL="http://schemas.microsoft.com/mapi/proptag/0x1000001F" LIKE '%Some value%'
上面的DASL名称对应于OOM属性(您不能在查询中使用Body
PR_BODY_W
名称)。
如果希望匹配多个值,则需要使用"OR“和/或"AND”运算符创建适当的查询。
https://stackoverflow.com/questions/56258704
复制相似问题