gpt4 book ai didi

vba - 使用 Excel VBA 获取发件人的 SMTP 电子邮件地址

转载 作者:行者123 更新时间:2023-12-04 00:11:10 28 4
gpt4 key购买 nike

我使用以下代码提取主题、接收日期和发件人姓名:

Set InboxSelect = GetObject("", "Outlook.Application").GetNamespace("MAPI").PickFolder
i = 0: EmailCount = 0
EmailCount = InboxSelect.Items.Count
While i < EmailCount
i = i + 1
blastRow = Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
LastRow = Sheets("Body").Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
With InboxSelect.Items(i)
MsgBox (SenderEmailAddress)
'If .senderemailaddress = "*@somethingSpecific.co.uk" Then
'EmailCount = EmailCount + 1
Sheets("Import Data").Range("A" & blastRow).Formula = .SenderName
Sheets("Import Data").Range("B" & blastRow).Formula = Format(.ReceivedTime, "dd/mm/yyyy")
Sheets("Import Data").Range("C" & blastRow).Formula = .Subject
Sheets("Body").Range("A" & LastRow).Formula = .Body
'End If
End With
Wend

我现在想要实现的是一个 if 语句,它会说“如果发件人的电子邮件地址是 'anything@somethingSpecific.co.uk' 然后执行该代码。我试过 SenderEmailAddress 但在消息框中测试时它返回空白。

编辑:/O=*SET1*/OU=FIRST ADMINISTRATIVE GROUP/CN=RECIPIENTS/CN=*VARIABLE1* 现在每次都使用以下代码在即时窗口中返回:

Set InboxSelect = GetObject("", "Outlook.Application").GetNamespace("MAPI").PickFolder
i = 0: EmailCount = 0
EmailCount = InboxSelect.Items.Count
While i < EmailCount
For Each Item In InboxSelect.Items
Debug.Print Item.senderemailaddress
If Item.senderemailaddress = "/O=SET1/OU=FIRST ADMINISTRATIVE GROUP/CN=RECIPIENTS/CN=*" Then
i = i + 1
blastRow = Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
LastRow = Sheets("Body").Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
With InboxSelect.Items(i)
Sheets("Import Data").Range("A" & blastRow).Formula = .SenderName
Sheets("Import Data").Range("B" & blastRow).Formula = Format(.ReceivedTime, "dd/mm/yyyy")
Sheets("Import Data").Range("C" & blastRow).Formula = .Subject
'PASTING BODY IS SLOW
Sheets("Body").Range("A" & LastRow).Formula = .Body
'End If
End With
End If
Next Item
Wend

我试图做的是使用通配符(*)作为返回消息中的变体,但没有奏效,有没有更好的方法来做到这一点?

最佳答案

使用 SenderEmailAddress 属性的示例会根据需要返回电子邮件字符串。

Dim outlookApp As outlook.Application, oOutlook As Object
Dim oInbox As outlook.Folder, oMail As outlook.MailItem

Set outlookApp = New outlook.Application
Set oOutlook = outlookApp.GetNamespace("MAPI")
Set oInbox = oOutlook.GetDefaultFolder(olFolderInbox)

For Each oMail In oInbox.Items
Debug.Print oMail.SenderEmailAddress
Next oMail

编辑:

问题在于 .SenderEmailAddress 属性返回的是 EX 地址,而我们想要的是 SMTP 地址。对于任何内部电子邮件地址,它将返回 EX 类型的地址。

要从内部电子邮件中获取 SMTP 地址,您可以使用以下内容。

Dim outlookApp As Outlook.Application, oOutlook As Object
Dim oInbox As Outlook.Folder, oMail As Outlook.MailItem

Dim strAddress As String, strEntryId As String, getSmtpMailAddress As String
Dim objAddressentry As Outlook.AddressEntry, objExchangeUser As Outlook.ExchangeUser
Dim objReply As Outlook.MailItem, objRecipient As Outlook.Recipient

Set outlookApp = New Outlook.Application
Set oOutlook = outlookApp.GetNamespace("MAPI")
Set oInbox = oOutlook.GetDefaultFolder(olFolderInbox)

For Each oMail In oInbox.Items
If oMail.SenderEmailType = "SMTP" Then

strAddress = oMail.SenderEmailAddress

Else

Set objReply = oMail.Reply()
Set objRecipient = objReply.Recipients.Item(1)

strEntryId = objRecipient.EntryID

objReply.Close OlInspectorClose.olDiscard

strEntryId = objRecipient.EntryID

Set objAddressentry = oOutlook.GetAddressEntryFromID(strEntryId)
Set objExchangeUser = objAddressentry.GetExchangeUser()

strAddress = objExchangeUser.PrimarySmtpAddress()

End If

getSmtpMailAddress = strAddress
Debug.Print getSmtpMailAddress

Next oMail

如果电子邮件已经是 SMTP,它将只使用 .SenderEmailAddress 属性返回地址。如果邮件是EX,那么它会通过.GetAddressEntryFromID()方法找到SMTP地址。

以上是我在 this answer 上找到的修改后的代码. Here也是如何在 C# 中执行此操作的链接。

关于vba - 使用 Excel VBA 获取发件人的 SMTP 电子邮件地址,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/34922075/

28 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com