Login to Gmail using excel VBA

[bennyys]

Hi All,
Gmail is not using one page format log in anymore so the macro for one page format log in is not valid for log in purpose.
I try to modify the code below for page by page step log in, but only success for input email address and then no further action afterwards.

Dim HTMLDoc As HTMLDocument
Dim oBrowser As InternetExplorer
Sub NewGmailLogin()


Dim oHTML_Element As IHTMLElement
Dim sURL As String


On Error GoTo Err_Clear
sURL = "https://accounts.google.com/ServiceLogin?service=mail&passive=true&rm=false&continue=https://mail.google.com/mail/&ss=1&scc=1&ltmpl=default&ltmplcache=2&emr=1&osid=1#"
Set oBrowser = New InternetExplorer
oBrowser.Silent = True
oBrowser.TimeOut = 60
oBrowser.navigate sURL
oBrowser.Visible = True


Do
' Wait till the Browser is loaded
Loop Until oBrowser.readyState = READYSTATE_COMPLETE


Set HTMLDoc = oBrowser.document


HTMLDoc.all.Email.Value = "my@gmail.com"


For Each oHTML_Element In HTMLDoc.getElementsByTagName("input")
If oHTML_Element.Type = "submit" Then oHTML_Element.Click: Exit For
Next
Do
' Wait till the Browser is loaded
Loop Until oBrowser.readyState = READYSTATE_COMPLETE


HTMLDoc.all.Passwd.Value = "mypassword"
For Each oHTML_Element In HTMLDoc.getElementsByTagName("input")
If oHTML_Element.Type = "submit" Then oHTML_Element.Click: Exit For
Next




' oBrowser.Refresh ' Refresh If Needed
Err_Clear:
If Err <> 0 Then
'Debug.Assert Err = 0
Err.Clear
Resume Next
End If
End Sub

Any idea what wrong with the code for input the password?

Regards,
Benny

 

[StephenCrump]

Not a direct answer to your question but a possible alternative ...

When Google started complicating the Gmail login process, I used the same single step login VBA code, but using an App Password generated from within Gmail.

I assume that facility still exists (I don't use Gmail any more). It may be an easier alternative, provided you're happy with the security aspect?

 

[Domenic]

In the following code, you'll notice that I've replaced the URL with one that gets you straight to the login page. Also, it looks like TimeOut is not a member of the InternetExplorer object, as per the following link...

https://msdn.microsoft.com/en-us/library/aa752084(v=vs.85).aspx

And lastly, you'll notice that I've paused the macro a couple of times for a few seconds even though IE is supposedly ready. Actually, the first one isn't really necessary, but the second one seems to be needed to allow more time for the second page to be loaded. The pause time is currently set at 5 seconds. You can change this as required. Maybe 3 seconds might suffice, but I haven't tested it.

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Sub[/COLOR] GMAIL_Login()

    [COLOR=green]'Set a reference (VBE > Tools > References) to the following libraries:[/COLOR]
    [COLOR=green]'   1) Microsoft Internet Controls[/COLOR]
    [COLOR=green]'   2) Microsoft HTML Object Library[/COLOR]
    
    [COLOR=darkblue]Dim[/COLOR] IE [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]New[/COLOR] SHDocVw.InternetExplorer
    [COLOR=darkblue]Dim[/COLOR] HTMLDoc [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]New[/COLOR] MSHTML.HTMLDocument
    [COLOR=darkblue]Dim[/COLOR] HTMLElement [COLOR=darkblue]As[/COLOR] MSHTML.IHTMLElement
    
    [COLOR=darkblue]With[/COLOR] IE
        .Visible = [COLOR=darkblue]True[/COLOR]
        .Silent = [COLOR=darkblue]True[/COLOR]
        .navigate "https://accounts.google.com/signin/v2/identifier?continue=https%3A%2F%2Fmail.google.com%2Fmail%2F&service=mail&sacu=1&rip=1&flowName=GlifWebSignIn&flowEntry=ServiceLogin"
        [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]While[/COLOR] .Busy [COLOR=darkblue]Or[/COLOR] .readyState <> READYSTATE_COMPLETE
            DoEvents
        [COLOR=darkblue]Loop[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    [COLOR=darkblue]Call[/COLOR] WaitAFewSeconds
    
    [COLOR=darkblue]Set[/COLOR] HTMLDoc = IE.document
    
    HTMLDoc.all.identifier.Value = "mysecond@gmail.com"
    HTML[COLOR=darkblue]Do[/COLOR]c.all.identifierNext.Click
    
    [COLOR=darkblue]With[/COLOR] IE
        Do [COLOR=darkblue]While[/COLOR] .Busy [COLOR=darkblue]Or[/COLOR] .readyState <> READYSTATE_COMPLETE
            DoEvents
        [COLOR=darkblue]Loop[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    [COLOR=darkblue]Call[/COLOR] WaitAFewSeconds
    
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] HTMLElement [COLOR=darkblue]In[/COLOR] HTMLDoc.getElementsByName("password")
        [COLOR=darkblue]If[/COLOR] HTMLElement.getAttribute("type") = "password" [COLOR=darkblue]Then[/COLOR]
            HTMLElement.Value = "mypassword"
            [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]For[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] HTMLElement

    HTMLDoc.all.passwordNext.Click
    
    [COLOR=darkblue]Set[/COLOR] IE = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] HTML[COLOR=darkblue]Do[/COLOR]c = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] HTMLElement = [COLOR=darkblue]Nothing[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] WaitAFewSeconds()
    
    [COLOR=darkblue]Dim[/COLOR] sngStart [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Single[/COLOR]
    
    [COLOR=darkblue]Const[/COLOR] PAUSE_TIME [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR] = 5 [COLOR=green]'seconds[/COLOR]
    
    sngStart = Timer
    Do [COLOR=darkblue]Until[/COLOR] Timer - sngStart > PAUSE_TIME
        DoEvents
    [COLOR=darkblue]Loop[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

 

[bennyys]

Hi StephenCrump,
Thank you for your advice.I will google about "app password generated for gmail".
I will put your advice as another way to do the task with the level of security category as low.

Regards,
Benny

 

[bennyys]

Hi Domenic,
I try to run your code (set reference to Microsoft Internet Controls and Microsoft HTML Object Library)
It come up with error "run time error 438 object doesn't support this property or method" on line

HTMLDoc.all.identifier.Value = "myemail@gmail.com"

Regards,
Benny

 

[Domenic]

When I tested the macro, it ran fine without any errors. In any case, I should have used the ID, not the Name to identify the element. Does this help?

HTMLDoc.all.identifierId.Value = "mysecond@gmail.com"

 

[bennyys]

Domenic,
I modify a bit of your code to get it run on my side.
(please see the part mark with red and bold)

Option Explicit


Sub GMAIL_Login()


    'Set a reference (VBE > Tools > References) to the following libraries:
    '   1) Microsoft Internet Controls
    '   2) Microsoft HTML Object Library
    
    Dim IE As New SHDocVw.InternetExplorer
    Dim HTMLDoc As New MSHTML.HTMLDocument
    Dim HTMLElement As MSHTML.IHTMLElement
    
    With IE
        .Visible = True
        .Silent = True
        .navigate "https://accounts.google.com/signin/v2/identifier?continue=https%3A%2F%2Fmail.google.com%2Fmail%2F&service=mail&sacu=1&rip=1&flowName=GlifWebSignIn&flowEntry=ServiceLogin"
        Do While .Busy Or .readyState <> READYSTATE_COMPLETE
            DoEvents
        Loop
    End With
    
    Call WaitAFewSeconds
    
    Set HTMLDoc = IE.document
    
[B][COLOR=#FF0000]    HTMLDoc.all.Email.Value = "myemail@gmail.com"[/COLOR][/B]
[B][COLOR=#FF0000]    HTMLDoc.all.SignIn.Click[/COLOR][/B]
    
    With IE
        Do While .Busy Or .readyState <> READYSTATE_COMPLETE
            DoEvents
        Loop
    End With
    
    Call WaitAFewSeconds
    
[B][COLOR=#FF0000]    For Each HTMLElement In HTMLDoc.getElementsByName("Passwd")[/COLOR][/B]
        If HTMLElement.getAttribute("type") = "password" Then
            HTMLElement.Value = "mypassword"
            Exit For
        End If
    Next HTMLElement


[B][COLOR=#FF0000]    HTMLDoc.all.SignIn.Click[/COLOR][/B]
    
    Set IE = Nothing
    Set HTMLDoc = Nothing
    Set HTMLElement = Nothing
    
End Sub


Private Sub WaitAFewSeconds()
    
    Dim sngStart As Single
    
    Const PAUSE_TIME As Integer = 5 'seconds
    
    sngStart = Timer
    Do Until Timer - sngStart > PAUSE_TIME
        DoEvents
    Loop
    
End Sub

Regards,
Benny

 

[Domenic]

It looks like what may be happening is that a different page gets loaded depending on one's location because when I run your code I get an error on the line that enters the email address. And when I inspect the HTML code, I don't see an element whose "id" or "name" attribute is "Email".

In any case, I'm glad you've got it working for you. And thanks for your feedback.

Cheers!

 

[bennyys]

Domenic,
You are right that "It looks like what may be happening is that a different page gets loaded depending on one's location because when I run your code I get an error on the line that enters the email address".
Thank you again for your advice so far and it was an interesting thing to discuss with you about this topic. Hope we will meet again in another topics.

Regards,
Benny