By default, you need to enable ” Allowing less secure apps” in Gmail/G Suite, then you can retrieve email with user/password IMAP4 authentication.
However Google will disable traditional user authentication in the future, switching to Google OAuth is strongly recommended now.
Sections:
Before you can use the following sample codes, you should download the EAGetMail Installer and install it on your machine at first. Full sample projects are included in this installer.
To use EAGetMail POP3 & IMAP4 ActiveX Object in your project,
the first step is Add reference of EAGetMail to your project
.
Please go to menu
-> Project
-> References
-> and select EAGetMailObj ActiveX Object
, click OK
,
the reference will be added to your project, and you can start to use it to retrieve email and parse email in your project.
Open VBA IDE by press Alt+F11
, Please select menu -> Tools
-> References
-> and select EASendMailObj ActiveX Object
,
click OK, the reference will be added to current VBA project, and you can start to use it to retrieve email and parse email in your VBA project.
To better demonstrate how to retrieve email and parse email, let’s create a VB 6.0 Standard EXE project at first, then add a CommandButton on the Form, double-click this button. It is like this
Normal OAuth requires user input user/password in Web Browser. Obviously, it is not suitable for background service. In this case, you should use google service account to access G Suite email service without user interaction. Service account only works for G Suite user, it doesn’t work for personal Gmail account.
To use “G Suite Service Account OAuth” in your application, you should create a project in Google Developers Console at first.
Important
You can use any google user to create service account, it doesn’t require service account owner is a user in G Suite. But G Suite administrator must authorize service account in G Suite Admin Console to access user mailbox.
Open Google Developer Console, create a new project by https://console.developers.google.com/projectcreate.
After you created the project, select it from projects list as current project.
Click "Credentials"
-> "Manage service accounts"
Click "CREATE SERVICE ACCOUNT"
Input a name for your service account, click "CREATE"
In "Service account permissions"
, select "Project"
-> "Owner"
as role
In "Grant users access to this service account"
, keep everything default and click "DONE"
After service account is created, you should enable "Domain-wide delegation"
and create service key pair
to access G Suite user mailbox.
Go back to your service account, click "Edit"
-> "SHOW DOMAIN-WIDE DELEGATION"
,
check "Enable G Suite Domain-wide Delegation"
, input a name for product oauth consent, click "Save"
.
Go back to your service account again, click "Create Key"
, you can select "p12"
or "json"
key type,
both can work well, then you will get a file which contains private key, save the file to local disk.
Now you have created service account with key pair successfully. You can use created private key in your
codes to request "access token"
impersonating a user in G Suite.
To access user data in G Suite, you must get authorization from G Suite administrator.
You should go to service accounts list, click "View Client ID"
like this:
Then copy your “Client ID” and service account email address, forward it to G Suite administrator for authorization.
Enable Gmail API in "Library"
-> Search "Gmail"
, then click "Gmail API"
and enable it.
If you use Gmail API protocol to send email, you should enable this API, if you use SMTP protocol, you don’t have to enable it.
To use service account to access user mailbox in G Suite, G Suite Administrator should authorize specified service account at first.
Important
Important Notice: You can use any google user to create service account, it doesn’t require service account owner is a user in G Suite. But G Suite administrator must authorize service account in G Suite Admin Console to access user mailbox.
G Suite Administrator should open admin.google.com, go to Admin Console, click "Security"
> API Control
;
Click Add new and enter your service account client ID.
Enter the client ID
of the service account or OAuth2 client ID of the app.
In OAuth Scopes, add each scope that the application can access (should be appropriately narrow).
and input https://mail.google.com/
, email
, profile
in One or More API Scopes, click "Authorize"
.
After G Suite administrator authorized service account, you can use it to access any users mailbox in G Suite domain.
Because HttpWebRequest is used to get access token from web service.
If you’re using legacy .NET framework (.NET 2.0 - .NET 3.5 and .NET 4.0 - 4.6.1),
you need to enable Strong Encryption Algorithms
to request access token:
Put the following content to a file named NetStrongEncrypt.reg
, right-click this file -> Merge
-> Yes
.
You can also download it from https://www.emailarchitect.net/webapp/download/NetStrongEncrypt.zip.
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v2.0.50727]
"SystemDefaultTlsVersions"=dword:00000001
"SchUseStrongCrypto"=dword:00000001
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v2.0.50727]
"SystemDefaultTlsVersions"=dword:00000001
"SchUseStrongCrypto"=dword:00000001
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v4.0.30319]
"SystemDefaultTlsVersions"=dword:00000001
"SchUseStrongCrypto"=dword:00000001
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v4.0.30319]
"SystemDefaultTlsVersions"=dword:00000001
"SchUseStrongCrypto"=dword:00000001
You don’t have to request access token
every time. By default,
access token
expiration time is 3600 seconds, you can reuse the access token
repeatedly before it is expired.
Option Explicit
Const MailServerPop3 = 0
Const MailServerImap4 = 1
Const MailServerEWS = 2
Const MailServerDAV = 3
Const MailServerMsGraph = 4
Const MailServerAuthLogin = 0
Const MailServerAuthCRAM5 = 1
Const MailServerAuthNTLM = 2
Const MailServerAuthXOAUTH2 = 3
Const GetMailInfos_All = 1
Const GetMailInfos_NewOnly = 2
Const GetMailInfos_ReadOnly = 4
Const GetMailInfos_SeqRange = 8
Const GetMailInfos_UIDRange = 16
Const GetMailInfos_PR_ENTRYID = 32
Const GetMailInfos_DateRange = 64
Const GetMailInfos_OrderByDateTime = 128
Const CRYPT_MACHINE_KEYSET = 32
Const CRYPT_USER_KEYSET = 4096
Private Function GenerateRequestData(GsuiteUser)
GenerateRequestData = ""
' service account email address
Const serviceAccount = "xxxxxx@xxxxxx.iam.gserviceaccount.com"
Const scope = "https://mail.google.com/"
Const aud = "https://oauth2.googleapis.com/token"
Dim jwt As New EAGetMailObjLib.SimpleJsonParser
Dim header, playload
header = jwt.JwtBase64UrlEncode("{""alg"":""RS256"",""typ"":""JWT""}")
Dim iat, exp
' token request timestamp
iat = jwt.GetCurrentIAT()
' token expiration time
exp = iat + 3600
playload = "{"
playload = playload & """iss"":""" & serviceAccount & ""","
playload = playload & """scope"":""" & scope & ""","
playload = playload & """aud"":""" & aud & ""","
playload = playload & """exp"":" & exp & ","
playload = playload & """iat"":" & iat & ","
playload = playload & """sub"":""" & GsuiteUser & """"
playload = playload & "}"
playload = jwt.JwtBase64UrlEncode(playload)
Dim cert As New EAGetMailObjLib.Certificate
' In web application, use CRYPT_MACHINE_KEYSET
If Not cert.LoadPFXFromFile("D:\myfolder\myoauth-77dec4d192ec.p12", "notasecret", CRYPT_USER_KEYSET) Then
Debug.Print "Failed to load service account certificate!"
Exit Function
End If
Dim signature
signature = jwt.SignRs256(cert, header & "." & playload)
If signature = "" Then
Debug.Print "Failed to sign request data!"
Exit Function
End If
Dim dataToPost
dataToPost = header & "." & playload & "." & signature
GenerateRequestData = dataToPost
End Function
Private Function RequestAccessToken(requestData)
RequestAccessToken = ""
If requestData = "" Then
Exit Function
End If
On Error GoTo ErrorHandle
Dim httpRequest
Set httpRequest = CreateObject("MSXML2.ServerXMLHTTP")
requestData = "grant_type=urn%3Aietf%3Aparams%3Aoauth%3Agrant-type%3Ajwt-bearer&assertion=" & requestData
httpRequest.setOption 2, 13056
httpRequest.Open "POST", "https://oauth2.googleapis.com/token", True
httpRequest.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
httpRequest.Send requestData
Do While httpRequest.ReadyState <> 4
DoEvents
httpRequest.waitForResponse (1)
Loop
Dim Status
Status = httpRequest.Status
If Status < 200 Or Status >= 300 Then
Debug.Print "Failed to get access token from server."
Debug.Print httpRequest.responseText
Exit Function
End If
Dim result
result = httpRequest.responseText
Dim oauthParser As New EAGetMailObjLib.OAuthResponseParser
oauthParser.Load result
Dim accessToken
accessToken = oauthParser.accessToken
If accessToken = "" Then
Debug.Print "Failed to parse access token from server response."
Exit Function
End If
RequestAccessToken = accessToken
Exit Function
ErrorHandle:
Debug.Print "Failed to request access token." & Err.Description
End Function
Public Sub RetrieveEmail()
' GsuiteUser is the full email address of the user in GSuite, user@gsuitedomain
Dim GsuiteUser As String
GsuiteUser = "user@mydomainingsuit.com"
Dim access_token As String
' request access token from Google server by service account
' withou user interaction
access_token = RequestAccessToken(GenerateRequestData(GsuiteUser))
If access_token = "" Then
Exit Sub
End If
Dim curpath As String
Dim mailbox As String
Dim oTools As New EAGetMailObjLib.Tools
' Create a folder named "inbox" under current directory
' to save the email retrieved.
curpath = App.Path
mailbox = curpath & "\inbox"
oTools.CreateFolder mailbox
Dim oServer As New EAGetMailObjLib.MailServer
' Gmail IMAP4 Server address
oServer.Server = "imap.gmail.com"
' Use OAUTH 2.0
oServer.AuthType = MailServerAuthXOAUTH2
oServer.user = GsuiteUser
' Use access token as password
oServer.Password = access_token
oServer.Protocol = MailServerImap4
' Enable SSL/TLS connection
oServer.SSLConnection = True
' Set IMAP SSL Port
oServer.Port = 993
On Error GoTo ErrorHandle:
Dim oClient As New EAGetMailObjLib.MailClient
oClient.LicenseCode = "TryIt"
Debug.Print "Connecting " & oServer.Server & "..."
oClient.Connect oServer
Debug.Print "Connected"
' Get new email only, if you want to get all emails, please remove this line
oClient.GetMailInfosParam.GetMailInfosOptions = GetMailInfos_NewOnly
Dim infos As EAGetMailObjLib.MailInfoCollection
Set infos = oClient.GetMailInfoList()
Debug.Print infos.Count & " emails"
Dim i As Long
For i = 0 To infos.Count - 1
Dim info As EAGetMailObjLib.MailInfo
Set info = infos.Item(i)
Debug.Print "Index: " & info.Index & "; Size: " & info.Size & _
"; UIDL: " & info.UIDL
' Receive email from IMAP4 server
Dim oMail As EAGetMailObjLib.Mail
Set oMail = oClient.GetMail(info)
Debug.Print "From: " & oMail.From.Address & _
vbCrLf & "Subject: " & oMail.Subject
Dim fileName As String
' Generate a random file name by current local datetime,
' You can use your method to generate the filename if you do not like it
fileName = mailbox & "\" & oTools.GenFileName(i) & ".eml"
' Save email to local disk
oMail.SaveAs fileName, True
' Mark email as read to prevent retrieving this email again.
oClient.MarkAsRead info, True
' If you want to delete current email, please use Delete method instead of MarkAsRead
' oClient.Delete info
Next
' Quit and expunge emails marked as deleted from IMAP4 server.
oClient.Quit
Exit Sub
ErrorHandle:
Debug.Print Err.Description
End Sub
TLS is the successor of SSL, more and more SMTP servers require TLS 1.2
encryption now.
If your operating system is Windows XP/Vista/Windows 7/Windows 2003/2008/2008 R2/2012/2012 R2
, you need to
enable TLS 1.2 protocol in your operating system like this:
Enable TLS 1.2 on Windows XP/Vista/7/10/Windows 2008/2008 R2/2012
Appendix
Comments
If you have any comments or questions about above example codes, please click here to add your comments.