在没有 Outlook 的情况下通过 VBA 阅读 GMail 电子邮件

Reading GMail email messages via VBA without Outlook

提问by pio pio

How can I read GMail emails via VBA without having Outlook installed on the PC ?

如何在没有在 PC 上安装 Outlook 的情况下通过 VBA 阅读 GMail 电子邮件？

I googled it but I didn't find any solution which does not relies on Outlook.

我用谷歌搜索，但没有找到任何不依赖于 Outlook 的解决方案。

回答by P??

You can use the google api to archive this without outlook. There is VBA-Webon github that supports the gmail api.

您可以使用 google api 在没有 Outlook 的情况下将其存档。有VBA的Web支持Gmail的API在GitHub上。

Here is an example for gmail with vba-web:

以下是带有 vba-web 的 gmail 示例：

Attribute VB_Name = "Gmail"
' 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 CStr(Credentials.Values("Google")("id")), CStr(Credentials.Values("Google")("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

回答by user5995530

You can pull GMAIL email data into Excel by adding a custom reference library EAGetMail.

您可以通过添加自定义参考库 EAGetMail 将 GMAIL 电子邮件数据提取到 Excel 中。

You can find the install link and a demo macro here.

您可以在此处找到安装链接和演示宏。