# Excel VBA to open Gmail



## daniels012 (Feb 13, 2007)

What code can I write to just open gmail?
I have a button now with VBA that saves the file as a pdf. I would like to add code to the button that would open gmail for me.

Any ideas?
Michael


----------



## OBP (Mar 8, 2005)

Michael, have you looked on the gmail website help on VBA code.
I have the code for using gmail with Access, but not Excel.


----------



## DoubleHelix (Dec 10, 2004)

You asked the same question before.

http://forums.techguy.org/business-applications/1024145-change-windows-email-client.html

OBP, do you have an actual link to a process that does this? Gmail is accessed through a web browser. Do they have code to available to actually open a new message with an attached file?


----------



## OBP (Mar 8, 2005)

On Error GoTo Err_Command19_Click
Dim attach As String
attach = "C:\Access\A-JCF-JCMRE.jpg"
Set cdomsg = CreateObject("CDO.message")
Set iConf = CreateObject("CDO.Configuration")

With cdomsg.Configuration.Fields
.Item("http://schemas.microsoft.com/cdo/con...tion/sendusing") = 2 'NTLM method
.Item("http://schemas.microsoft.com/cdo/con...ion/smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/con...smptserverport") = 587
.Item("http://schemas.microsoft.com/cdo/con...tpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/con...ion/smtpusessl") = True
.Item("http://schemas.microsoft.com/cdo/con...nectiontimeout") = 60
.Item("http://schemas.microsoft.com/cdo/con...n/sendusername") = "Email goes here"
.Item("http://schemas.microsoft.com/cdo/con...n/sendpassword") = "password here"
.Update
End With
' build email parts

With cdomsg
Set .Configuration = iConf

.To = "Email goes here"
.BCC = "Email goes here"
.From = "Email goes here"
.Subject = "the email subject"
.Addattachment attach
.TextBody = "read attachment"
.Send
End With
Set cdomsg = Nothing
Exit_Command19_Click:
Exit Sub

Err_Command19_Click:
MsgBox Err.Description
Resume Exit_Command19_Click


----------



## Jimmy the Hand (Jul 28, 2006)

Came across this one just today. A knowledge base item from VBAX:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=973
Not sure if it works, as it's 2 years old code. Gmail's page might have changed since.
Jimmy

Update:
It should work. The used control IDs on gmail login page are still the same.


----------



## Keebellah (Mar 27, 2008)

You will have to add two references in the VBA project

Microsoft Internet Controls
Microsoft HTML Object Library

Afetr I added these two references the compilation no longer thew an error, the code it'sself I haven't tested (yet)


----------



## Keebellah (Mar 27, 2008)

And yes, it opened Gmail, my account was lgged in so no password asked but the code is correct


----------



## Rollin_Again (Sep 4, 2003)

The question has pretty much been answered but I thought I would just add that the control names used in the macro code can be obtained by viewing the webpage source code. I've been using a similar macro for years with no issues.

Rollin


----------



## Zack Barresse (Jul 25, 2004)

Didn't realize this was cross-posted...

http://www.vbaexpress.com/forum/showthread.php?t=36692


----------



## daniels012 (Feb 13, 2007)

This is using IE
What about opening Chrome?


----------



## Keebellah (Mar 27, 2008)

No idea but Google does wonders
Maybe this link will help you further
http://wiki.imacros.net/Sample_Code


----------



## Zack Barresse (Jul 25, 2004)

OBP's method doesn't use IE, thus no browser overhead. It uses CDO to send via Gmail. I'd recommend using that.


----------



## daniels012 (Feb 13, 2007)

Thank You Zack!
I am going to go try this and this may be a dunb question but?
The .... in the middle do I need something here?


> .Item("http://schemas.microsoft.com/cdo/con...ion/smtpserver") = "smtp.gmail.com"


Mciahel D


----------



## daniels012 (Feb 13, 2007)

I get an error on this line:
Set cdomsg = CreateObject("CDO.message")
It says variable not defined!

Michael


----------



## OBP (Mar 8, 2005)

See Keebellah's post at #6 about setting VBA References.


----------



## daniels012 (Feb 13, 2007)

Tried to find both of these! No luck in my preferences in Excel 2007?
Microsoft Internet Controls
Microsoft HTML Object Library
Michael


----------



## daniels012 (Feb 13, 2007)

I did!
I don't have these as References to choose?
Is it because I am using Excel 2007?

Michael


----------



## Keebellah (Mar 27, 2008)

Have you looked? You have to scoll all the way down (in the VBA Project window under Tools
I imagine 2007 is no different rom 2010


----------



## Zack Barresse (Jul 25, 2004)

It's still the same, albeit VBA7, it's widely considered the same.


----------



## Zack Barresse (Jul 25, 2004)

daniels012 said:


> Thank You Zack!
> I am going to go try this and this may be a dunb question but?
> The .... in the middle do I need something here?
> 
> Mciahel D


Btw, this is because of the forum software here (vBulletin) has shortened the link, and it was pasted as text (hence no links). The actual URL is "http://schemas.microsoft.com/cdo/configuration/". So it was almost complete.


----------



## Rollin_Again (Sep 4, 2003)

The error that is being thrown (variable not defined) has nothing to do with project references since late binding is being used. You either need to declare your objects at the top of the subroutine as shown below or remove the words "Option Explicit" from the very top of the code module so that your variables don't have to be declared.


```
Dim cdomsg As Object
Dim iConf as Object
```
Rollin


----------



## daniels012 (Feb 13, 2007)

OK, I had a chance to work on the code given.
I added the DIM that you requested (Rollin_Again)...
Now I RUN the code and I get an error on the Send line?

Ideas? Anyone?
Michael


----------



## daniels012 (Feb 13, 2007)

By the way....
Gmail does not open when all the other code has been going through to that point?


----------



## Zack Barresse (Jul 25, 2004)

What code are you using? The CDO method? If so, it won't 'open' gmail. It will send in the background using CDO as its implementation. 

Also, if you have an error, we would need to know what the error states in addition to what line it errors out on.


----------



## daniels012 (Feb 13, 2007)

Zack Thank you,
Can I open Chrome and then my Gmail account using something like this:
Shell ("C:\*\Google\Chrome\Application\Chrome.exe -url https://mail.google.com/mail/")
I saw something like this in the past! Just something to open chrome browser... open gmail... and let me enter all the information myself is fine.



Thank You,
Michael


----------



## daniels012 (Feb 13, 2007)

OK,
I guess not!
Then back to my original, Can I use the past code given to visibly see my gmail filled in with attachment and info in the subject and body text, etc. and let me send it if all is correct?

Thank You,
Michael


----------



## Zack Barresse (Jul 25, 2004)

Have you tried the IE code from previous links? If you want to actually see it, you need to open a web browser. I don't have Chrome, nor will I install it, so I can't test that for you. The link to the VBAX KB item from Jimmy points out how to do it with IE: http://www.vbaexpress.com/kb/getarticle.php?kb_id=973

The code supplied by OBP is utilizing Gmail settings and the Windows CDO method to send the email. Either should work for you, depending on what you need. Have you tried them both?


----------



## daniels012 (Feb 13, 2007)

This seems to work!


> Sub Button1_Click()
> Shell ("C:\Users\user\AppData\Local\Google\Chrome\Application\chrome.exe -url http:mail.google.com/mail/?ui=2&shva=1#inbox")
> End Sub


Searched and Searched and found something out there. Persistence helps!


----------

