Got mail?

[malik641]

Hey guys,

I'm using the following code to login to gmail, which works perfectly. What I want to know is how to know if there are any new emails in your inbox.

Here's the code:

[vba]Sub GMAIL(MyLogin As String, MyPass As String)
Dim ie As Object
Dim ieForm As Variant
Dim WebPage As String

Set ie = CreateObject("InternetExplorer.Application")

WebPage = "http://mail.google.com"
ie.Visible = True
ie.Navigate WebPage
'Loop until IE pages is fully loaded
Do Until ie.ReadyState = 4 'READYSTATE_COMPLETE
Loop
'Look for userform Form by finding test "Userform"
For Each ieForm In ie.Document.forms
'Debug.Print ieForm.innertext
If InStr(ieForm.innertext, "Username") <> 0 Then
ieForm(0) = MyLogin
ieForm(1) = MyPass

ieForm.submit
End If

Next
Set ie = Nothing
End Sub[/vba]

Now, that may sound difficult....but all I need to do is be able to read the window's full caption and I'll be able to tell right away because the caption (when you have mail) is:

Gmail - Inbox(1) - Microsoft Internet Explorer

so I can just check out the caption for a number and find out how many new emails I have (if any).

Problem is, I can't find the right property to display the full window name (or seperately "Inbox(1)"). The only things I found were:

ie.Name - which turns up "Microsoft Internet Explorer"
ie.LocationName - which says "Gmail"

And others that point to website addresses or application paths.

Any ideas?
Thanks

[Ken Puls]

Joseph,

I'm curious why you're doing this... Gmail has two cool little apps available for this sort of thing:
There is the Desktop Notifier, which will installs on your PC and checks your gmail for you. Pops up an alert and changes colour when you get a notif.

There is also a plugin for Firefox called the gmail notifier which installs a similar thing in Firefox. You load up Firefox, and it will pop up a little box whenever someone sends you an email.

Lastly, you can also customize your own Googel homepage at www.google.com/ig to also show a snapshot from your inbox.

Lots of ways to get at it, so why from Excel?

[malik641]

Glad you asked Ken

First off, I'm going to look at those items you pointed out...they sound pretty nice.


Anyway, this is mostly outta curiosity...kinda like, "well, how does the Desktop Notifier do it?" type of thing.

I have a spreadsheet of my production and it has nice little buttons to it, like the one that executes the code I have above...and another just to open to my subscriptions page at this site....and others that have to do with my daily tasks (like hyperlinking my drawings, moving files, opening PDF files...etc)...so a good amount of my time is spent in this spreadsheet...and it's nice to have everything in one place. I'm sure this is the case with a lot of people here and I just want some more functionality.

Plus, I won't have an additional application running just to inform me I have mail (besides outlook, of course)...and I can check it whenever I want to....and it's just cool to know how to do in VBA

...Ever since this new job, I've been getting ideas left and right...automating everything and anything I can. I guess some of my ideas are getting a little silly, lol.

[Ken Puls]

Okay, no worries. I totally understand the research for the point of learning.

I won't have an additional application running just to inform me I have mail (besides outlook, of course)

See, this is where I say that it might actually be a better idea. The notifier apps are specifically built to do this. They take the overhead out of Excel (cause that's what you're doing is adding overhead), and put them where they are built to to work efficiently. The desktop notifier is pretty slick, and only takes up as much screen real estate as any other item in the system tray. That way, also, you don't need to be looking at your Excel worksheet. You could be in Outlook, IE, windows explorer, or whatever, and the notif still pops up.

[malik641]

I see your point.

The desktop notifier is pretty slick, and only takes up as much screen real estate as any other item in the system tray. That way, also, you don't need to be looking at your Excel worksheet. You could be in Outlook, IE, windows explorer, or whatever, and the notif still pops up.

It's funny you say this....I was thinking if there was a way I can do this too (not to interfere with any running program) but then I thought "now that's WAY outta my league!"

[Charlize]

I've noticed that when you select 'file - save as' internet explorer suggests a name to save it to. In my case he proposed "Gmail - postvak in (3)" = "Gmail - Inbox (3)". So if you copy the proposed name - paste this in some cell - read contents of cell to search for (). Just an idea.

I was wondering how did your code worked for you. Tried it but no luck. Do I have to set a reference to something ?

Charlize

[malik641]

Hey Charlize,

The code uses late binding, so no reference is necessary....what exactly is going wrong? Where's the error rising?

Unfortunately, I'm still not sure how the InternetExplorer object works (entirely) yet...as well as reading web pages through VBA...for example: I'm not sure why each "ieForm" is an array of other forms (ieForm(0) & ieForm(1))....and I haven't found decent information on how to program to web pages through VBA.


Anyway, about your idea, I like that idea...It sounds like there's underlying info about what IE would name the file (must be stored somewhere....). I'll see what I can come up with

[Bob Phillips]

Doesn't work for me either, doesn't log me in.

[malik641]

Does Gmail say "Incorrect Username and/or password" ???

It works fine for me....

I commented out the IF statement for the Submit method....

[vba]Sub GMAIL(MyLogin As String, MyPass As String)
Dim ie As Object
Dim ULogin As Boolean
Dim ieForm As Variant
Dim WebPage As String

Set ie = CreateObject("InternetExplorer.Application")

WebPage = "http://mail.google.com"
ie.Visible = True
ie.Navigate WebPage
'Loop until IE pages is fully loaded
Do Until ie.ReadyState = 4 'READYSTATE_COMPLETE
Loop
'Look for password Form by finding test "Password"
For Each ieForm In ie.Document.forms
Debug.Print ieForm.innertext
' If InStr(ieForm.innertext, "Username") <> 0 Then
' ULogin = True
' ieForm(0) = MyLogin
' ieForm(1) = MyPass
'
' ieForm.submit
' End If
Next

'If ULogin = False Then MsgBox "User is aleady logged in"
Set ie = Nothing
End Sub[/vba]
And I end up with the following

Sign in to Gmail with your Account

Username:
Password:
Remember me on this computer.

I cannot access my account

And to my understanding, ieForm(0) is the Username form, and ieForm(1) is the Password form.....maybe it's different for you guys??? Try this out and could you post what it says?

Not sure if this matters...but you guys are in different countries too...

[Bob Phillips]

I get the same text, but if I uncomment, this line

ieForm(0) = MyLogin

errors.

[malik641]

Automation error or an error on the web page itself?

And what does it say?

[malik641]

Well this sucks. Now I'm getting the error


"Object doesn't support this action"

at ieForm(0) = MyLogin

...back to the drawing board....

(I should've just stuck with the SendKeys method I was using...)

[Charlize]

This is for the reading of the caption. When you've got mail the message box will show it. Had to set reference to "internet controls". This is inlogpage of gmail. Try another page to see if capturing is ok. Code must be in module.
[VBA]Option Explicit
Global ie As SHDocVw.InternetExplorer
Sub testmail()
Dim ie As Object
Dim WebPage As String
Dim saving_as As Variant
Set ie = CreateObject("InternetExplorer.Application")

WebPage = "http://mail.google.com"
ie.Visible = True
ie.Navigate WebPage
'Loop until IE pages is fully loaded
Do Until ie.ReadyState = 4 'READYSTATE_COMPLETE
Loop
saving_as = ie.Document.all.Item(2).innerHTML
MsgBox ("Caption of window : " & saving_as)
End Sub[/VBA]
Charlize

[malik641]

Nice one Charlize that'll be just fine

Now how to login (again...)

[malik641]

Hey, this also works for the caption of the window:

[VBA]
saving_as = ie.Document.Title
[/VBA]

[malik641]

Okay guys...please tell me if this works for you:

This was after testing and testing and testing (and research and research and research)...and it worked for me...sooo

[VBA]Sub GMAIL(MyLogin As String, MyPass As String)
Dim ie As Object, oDoc As Object
Dim ULogin As Boolean
Dim ieForm As Variant
Dim WebPage As String
Dim saving_as As String

Set ie = CreateObject("InternetExplorer.Application")

WebPage = "http://mail.google.com"

ie.Visible = True
'ie.visible = false

ie.Navigate WebPage

'Loop until IE pages is fully loaded
Do Until ie.ReadyState = 4 'READYSTATE_COMPLETE
Loop

Set oDoc = ie.Document
Set ieForm = oDoc.forms(0)

ieForm(6).innerText = MyLogin
ieForm(7).innerText = MyPass
ie.Document.forms(0).Submit

Do Until ie.ReadyState = 4 'READYSTATE_COMPLETE
Loop

Debug.Print ie.Document.Title
Set ie = Nothing
End Sub[/VBA]

[Bob Phillips]

Yeah, that worked for me also. But I don't see the point in logging in from VBA. And what happens if Google change the interface (which they will)?

[malik641]

Why from VBA? ...why not?

lol seriously I'm just curious on how to do it, really.

And what happens if Google change the interface (which they will)?

Yeah I thought about that too....this is just a small step for this procedure of logging in. I will expand it....once I learn more about it (and I'm trying...but I don't believe that I'm finding the best info for what I want to know).


And unfortunately at this line:
[VBA] Do Until ie.ReadyState = 4 'READYSTATE_COMPLETE
Loop
Debug.Print ie.Document.Title
[/VBA]

Doesn't show what I want ("Gmail - Inbox (1)")...so for now, I replaced it with this:
[VBA]Application.Wait (Now() + TimeSerial(0, 0, 3))

Debug.Print ie.Document.Title[/VBA]
But this is ugly coding to me...and I'll need to find an alternative method.

[malik641]

I'm going to have to continue this later, but I came up with a way to run though the forms collection in the "ie.Document.Forms(0)":

[vba]Sub GMAIL(MyLogin As String, MyPass As String)
Dim ie As Object, oDoc As Object
Dim ieForm As Variant, form As Variant
Dim WebPage As String
Dim saving_as As String
Dim i As Integer

Set ie = CreateObject("InternetExplorer.Application")

WebPage = "http://mail.google.com"

ie.Visible = True
'ie.Visible = False

ie.Navigate WebPage

'Loop until IE pages is fully loaded
Do Until ie.ReadyState = 4 'READYSTATE_COMPLETE
Loop

Set oDoc = ie.Document
Set ieForm = oDoc.forms(0)

For Each form In ieForm
Debug.Print form.outerHTML, i

If form.ID = "Email" Then
form.innertext = MyLogin
ElseIf form.ID = "Passwd" Then
form.innertext = MyPass
End If

i = i + 1
Next
ie.Document.forms(0).Submit

Application.Wait (Now() + TimeSerial(0, 0, 3))

Debug.Print ie.Document.Title

Set ie = Nothing
End Sub[/vba]
It's not the best...but it works for now.....

[Charlize]

This zip contains two modules. One with the gmail thing and one for using * when asking for username and password.
After saving the Title tag to a variable, a logout link is fired and ie is closed.

Run the macro "Acces_To_Gmail".

Charlize