VBA import gmail emails into Excel

VBA import gmail emails into Excel

This Excel Macro will allow you to connect to your google email (gmail) account via pop/imap and then you can pull all your gmail emails into Excel.  You have to change a few settings in GMAIL before the macro will work, 1) Allow pop/imap and 2) Allow less secure apps.   You also have to install reference library eagetmail: EAgetmail (316 downloads)

If the macro is able to successfully connect to your GMAIL account it will display “Connected to server: success”.  If you get this error message “[AUTH] Web login required: https://support.google.com/mail/bin/answer.py?answer=78754” it likely means your GMAIL security settings are blocking the sign in attempt from the macro and you will need to change your GMAIL settings before the macro will work.

 

Download Files:

Pull GMAIL Data V2 (155 downloads)

EAgetmail (316 downloads)

Change your Google  GMAIL Security Settings:
  1. Setting allow pop/imap – click gear icon -> settings -> Forwarding and POP/IMAP -> Select enable POP for all mail and Enable IMAP.
  2. Allow less secure apps – Click square in top right corner -> My Account -> Sign-in & Security -> Flip Allow less secure apps: to ON.  (This will allow the macro to connect to your gmail account)  I would recommend flipping this setting back to OFF after your have pulled all your emails.
  3. Also make sure dual authentication is disabled.
EAGetMail library:

Install eagetmail.exe and then Enable Reference Library: EAGetMailObj ActiveX Object 1.0 Type Library

Open Excel -> hit Alt F11 -> Click Tools -> References -> Click EAGetMailObj ActiveX Object 1.0 Type Library -> hit Ok.

VBA GMAIL EMAIL IMPORT

Sub GetEmails()
Application.DisplayStatusBar = False
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

'https://www.emailarchitect.net/eagetmail/ex/b/7.aspx

Sheets(1).Select
Cells.ClearContents



Dim curpath As String
 Dim mailbox As String
 Dim oTools As New EAGetMailObjLib.Tools

Const MailServerPop3 = 0
 Const MailServerImap4 = 1
 Const MailServerEWS = 2
 Const MailServerDAV = 3
Dim usern, pw As String

usern = Sheets("Login").Cells(1, 2).Value
pw = Sheets("Login").Cells(2, 2).Value

Dim oServer As New EAGetMailObjLib.MailServer
 oServer.Server = "pop.gmail.com"
 oServer.User = usern
 oServer.Password = pw
 
 oServer.Protocol = MailServerPop3
 oServer.SSLConnection = True
 oServer.Port = 995
 
 ' If your POP3 requires SSL connection
 ' Please add the following codes
 Sheets(1).Select
 Cells(1, 1).Value = "Subject"
 Cells(1, 2).Value = "From"
 Cells(1, 3).Value = "Recieved"
 Cells(1, 4).Value = "CC"
 Cells(1, 5).Value = "Body"
 Cells(1, 6).Value = "Size"



On Error GoTo ErrorHandle:
 Dim oClient As New EAGetMailObjLib.MailClient
 oClient.LicenseCode = "TryIt"

oClient.Connect oServer
 MsgBox "Connected to server: success"
 
 Dim infos
 'oClient.GetMailInfosParam.GetMailInfosOptions = GetMailInfosOptionType.All
 
 infos = oClient.GetMailInfos()
 MsgBox UBound(infos) + 1 & " emails"
 Dim i As Integer
 
 i = 1
 
 For i = LBound(infos) To UBound(infos)
 
 Dim info As EAGetMailObjLib.MailInfo
 Set info = infos(i)
 ' MsgBox "Index: " & info.Index & "; Size: " & info.Size & _
 ' "; UIDL: " & info.UIDL

' Receive email from POP3 server
 Dim oMail As EAGetMailObjLib.Mail
 Set oMail = oClient.GetMail(info)
 Dim subJ, txtBody, emlBody, recFrom As String
 Dim recDate, ccList As Variant
 
 subJ = oMail.Subject
 recFrom = oMail.From.Address
 emlBody = oMail.HtmlBody
 recDate = oMail.ReceivedDate
 txtBody = oMail.TextBody
 ccList = oMail.Cc
 
 Sheets(1).Select
 Cells(i + 2, 1).Value = subJ
 Cells(i + 2, 2).Value = recFrom
 Cells(i + 2, 3).Value = recDate
 Cells(i + 2, 4).Value = ccList
 Cells(i + 2, 5).Value = txtBody
 Cells(i + 2, 6).Value = info.Size

Next


 Cells.Select
 Selection.RowHeight = 18.75
 
 Exit Sub
 
Application.DisplayStatusBar = True
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

ErrorHandle:
 MsgBox Err.Description
End Sub

Interested in learning new skills?