通过vba excel中的CDO获取邮件收件箱邮件 [英] Fetching gmail inbox mail messages via CDO in vba excel
问题描述
我想使用VBA中的CDO访问gmail帐户中的收件箱。
我已经设法发送邮件,但不知道如何将收件箱邮件提取到Excel表中。
如果可能,我也希望能够识别每条消息的标签。
I would like to access the inbox in a gmail account using CDO in VBA. I have already managed to send a mail message but do not know how to fetch the inbox messages into an excel sheet. If possible I would like to be able to identify the tags of each message as well.
推荐答案
虽然这个问题要求CDO,特别是从这个类似的SO问题它看起来不像CDO那样可以直接进行。
While the question asks for CDO in particular, from this similar SO question it doesn't look like this is possible directly with CDO.
作为获取收件箱邮件的替代方法,Google最近发布了可以与Excel一起使用的Gmail API。以下是使用 VBA-Web 的示例:
As an alternative approach to fetching inbox mail messages, Google has recently released a Gmail API that could be consumed with Excel. Below is an example using VBA-Web:
' Setup client and authenticator (cached between requests)
Private pGmailClient As WebClient
Private Property Get GmailClient() As WebClient
If pGmailClient Is Nothing Then
' Create client with base url that is appended to all requests
Set pGmailClient = New WebClient
pGmailClient.BaseUrl = "https://www.googleapis.com/gmail/v1/"
' Use the pre-made GoogleAuthenticator found in authenticators/ folder
' - Automatically uses Google's OAuth approach including login screen
' - Get API client id and secret from https://console.developers.google.com/
' - https://github.com/timhall/Excel-REST/wiki/Google-APIs for more info
Dim Auth As New GoogleAuthenticator
Auth.Setup "Your client id", "Your client secret"
Auth.AddScope "https://www.googleapis.com/auth/gmail.readonly"
Auth.Login
Set pGmailClient.Authenticator = Auth
End If
Set GmailClient = pGmailClient
End Property
' Load messages for inbox
Function LoadInbox() As Collection
Set LoadInbox = New Collection
' Create inbox request with userId and querystring for inbox label
Dim Request As New WebRequest
Request.Resource = "users/{userId}/messages"
Request.AddUrlSegment "userId", "me"
Request.AddQuerystringParam "q", "label:inbox"
Dim Response As WebResponse
Set Response = GmailClient.Execute(Request)
If Response.StatusCode = WebStatusCode.Ok Then
Dim MessageInfo As Dictionary
Dim Message As Dictionary
For Each MessageInfo In Response.Data("messages")
' Load full messages for each id
Set Message = LoadMessage(MessageInfo("id"))
If Not Message Is Nothing Then
LoadInbox.Add Message
End If
Next MessageInfo
End If
End Function
' Load message details
Function LoadMessage(MessageId As String) As Dictionary
Dim Request As New WebRequest
Request.Resource = "users/{userId}/messages/{messageId}"
Request.AddUrlSegment "userId", "me"
Request.AddUrlSegment "messageId", MessageId
Dim Response As WebResponse
Set Response = GmailClient.Execute(Request)
If Response.StatusCode = WebStatusCode.Ok Then
Set LoadMessage = New Dictionary
' Pull out relevant parts of message (from, to, and subject from headers)
LoadMessage.Add "snippet", Response.Data("snippet")
Dim Header As Dictionary
For Each Header In Response.Data("payload")("headers")
Select Case Header("name")
Case "From"
LoadMessage.Add "from", Header("value")
Case "To"
LoadMessage.Add "to", Header("value")
Case "Subject"
LoadMessage.Add "subject", Header("value")
End Select
Next Header
End If
End Function
Sub Test()
Dim Message As Dictionary
For Each Message In LoadInbox
Debug.Print "From: " & Message("from") & ", Subject: " & Message("subject")
Debug.Print Message("snippet") & vbNewLine
Next Message
End Sub
这篇关于通过vba excel中的CDO获取邮件收件箱邮件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!