Compose mail in gmail VBA

E

ExcelTheCell

Board Regular
Joined
Nov 14, 2010
Messages
158
Code: 
IE.Navigate "[URL]https://mail.google.com/mail/u/0/#inbox[/URL]"
I'm allways sign in. All i need is code that will click on Compose button and insert text in fileds TO,BODY and send to recipient, and possible to sign out from account
Any easy way...
 
Last edited:

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
Yes, I have tested that code. But then i get Error "Transport failed to connect to the server"
I have used some of his code to implemet to mine so i get problem with .Send line and error message
Code: 
the "sendUsing" configuration value is invalid
[TABLE="width: 48"]
<TBODY>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]
Here is the code:

Code: 
Function AddIEFRAME()
'Add referance from library named Microsoft internet contol
On Error GoTo 1
ThisWorkbook.VBProject.References.AddFromFile ("C:\WINDOWS\System32\ieframe.dll")
1:
End Function
Function AddMsHtml()
'Add referance from library named Microsoft HTML object library
On Error GoTo 1
ThisWorkbook.VBProject.References.AddFromFile ("C:\WINDOWS\System32\MsHtml.tlb")
1:
End Function
Function AddCDOWIN()
'Add referance from library named Microsoft CDO for WIn 2000 library
On Error GoTo 1
ThisWorkbook.VBProject.References.AddFromFile ("C:\WINDOWS\System32\cdosys.dll")
1:
End Function
Sub CDO_Mail_Small_Text()
Dim iMsg As Object
Dim iConf As Object
Dim strbody As String
Dim IE As InternetExplorer
Call AddIEFRAME
Call AddMsHtml
Call AddCDOWIN

Set IE = New InternetExplorer
IE.Navigate "[URL]https://mail.google.com/mail/u/0/#compose[/URL]"
IE.Visible = True

' Dim Flds As Variant
Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
strbody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2" & vbNewLine & _
"This is line 3" & vbNewLine & _
"This is line 4"
With iMsg
Set .Configuration = iConf
.To = "[EMAIL="ron@debruin.nl"]ron@debruin.nl[/EMAIL]"
.CC = ""
.BCC = ""
.From = """Ron"" <[EMAIL="ron@something.nl"]ron@something.nl[/EMAIL]>"
.Subject = "New figures"
.TextBody = strbody
.Send
End With
End Sub
 
Upvote 0
You are missing:

Rich (BB code): 
    iConf.Load -1    ' CDO Source Defaults
    Set Flds = iConf.Fields
    With Flds
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "Full GMail mail address"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "GMail password"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
        .Update
    End With

which you need instead of your original code.
 
Upvote 0
Well I created one open account so that we can test it. And as I was telling the code doesn't work it has Error with >> the "sendUsing" configuration value is invalid
I found topic on that error >> LINK << still the provided code is not functional also.. I'm using at the moment WinXP.

Can you tryout the code below.

Code: 
Sub CDO_Mail_Small_Text()
       Dim iMsg As Object
    Dim iConf As Object
    Dim strbody As String
    Dim Flds As Variant
' add reference
    Call AddIEFRAME
    Call AddMsHtml
    Call AddCDOWIN
    
    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")
    Set Flds = iConf.Fields
   iConf.Load -1    ' CDO Source Defaults
    Set Flds = iConf.Fields
    With Flds
        .Item("[URL]http://schemas.microsoft.com/cdo/con...ion/smtpusessl[/URL]") = True
        .Item("[URL]http://schemas.microsoft.com/cdo/con...tpauthenticate[/URL]") = 1
        .Item("[URL]http://schemas.microsoft.com/cdo/con...n/sendusername[/URL]") = "[EMAIL="mrexceltest@gmail.com"]mrexceltest@gmail.com[/EMAIL]"
        .Item("[URL]http://schemas.microsoft.com/cdo/con...n/sendpassword[/URL]") = "mrexceltest321"
        .Item("[URL]http://schemas.microsoft.com/cdo/con...ion/smtpserver[/URL]") = "smtp.gmail.com"
        .Item("[URL]http://schemas.microsoft.com/cdo/con...tion/sendusing[/URL]") = 2
        .Item("[URL]http://schemas.microsoft.com/cdo/con...smtpserverport[/URL]") = 25
        .Update
    End With

    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "This is line 1" & vbNewLine & _
              "This is line 2" & vbNewLine & _
              "This is line 3" & vbNewLine & _
              "This is line 4"
    With iMsg
        Set .Configuration = iConf
        .To = "[EMAIL="mrexceltest@gmail.com"]mrexceltest@gmail.com[/EMAIL]"
        .CC = ""
        .BCC = ""
        .From = "[EMAIL="mrexceltest@gmail.comm"]mrexceltest@gmail.comm[/EMAIL]"
        .Subject = "New figures"
        .TextBody = strbody
        .Send
    End With

End Sub


'-------------------------------------- ACTIVATE REFERECES ---------------------------------------
Function AddIEFRAME()
'Add referance from library named Microsoft internet contol
On Error GoTo 1
ThisWorkbook.VBProject.References.AddFromFile ("C:\WINDOWS\System32\ieframe.dll")
1:
End Function
Function AddMsHtml()
'Add referance from library named Microsoft HTML object library
On Error GoTo 1
ThisWorkbook.VBProject.References.AddFromFile ("C:\WINDOWS\System32\MsHtml.tlb")
1:
End Function
Function AddCDOWIN()
'Add referance from library named Microsoft CDO for WIn 2000 library
On Error GoTo 1
ThisWorkbook.VBProject.References.AddFromFile ("C:\WINDOWS\System32\cdosys.dll")
1:
End Function
 
Upvote 0
No You can use existing one that is in code... It contains email adress and password.. it's for testing pourpuse after that I will shut down that email.

Here is rewriten part so that you can use it.



Code: 
Option Explicit
'If you have a GMail account then you can try this example to use the GMail smtp server
'The example will send a small text message
'You must change four code lines before you can test the code
'.Item("[URL]http://schemas.microsoft.com/cdo/configuration/sendusername[/URL]") = "Full GMail mail address"
'.Item("[URL]http://schemas.microsoft.com/cdo/configuration/sendpassword[/URL]") = "GMail password"
'Use your own mail address to test the code in this line
'.To = "Mail address receiver"
'Change YourName to the From name you want to use
'.From = """YourName"" <[EMAIL="Reply@something.nl"]Reply@something.nl[/EMAIL]>"
'If you get this error : The transport failed to connect to the server
'then try to change the SMTP port from 25 to 465
 
Sub CDO_Mail_Small_Text_2()
    Dim iMsg As Object
    Dim iConf As Object
    Dim strbody As String
    Dim Flds As Variant
    
' add reference
    Call AddIEFRAME
    Call AddMsHtml
    Call AddCDOWIN
    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")
    iConf.Load -1    ' CDO Source Defaults
    Set Flds = iConf.Fields
    With Flds
        .Item("[URL]http://schemas.microsoft.com/cdo/configuration/smtpusessl[/URL]") = True
        .Item("[URL]http://schemas.microsoft.com/cdo/configuration/smtpauthenticate[/URL]") = 1
        .Item("[URL]http://schemas.microsoft.com/cdo/configuration/sendusername[/URL]") = "[EMAIL="mrexceltest@gmail.com"]mrexceltest@gmail.com[/EMAIL]"
        .Item("[URL]http://schemas.microsoft.com/cdo/configuration/sendpassword[/URL]") = "mrexceltest321"
        .Item("[URL]http://schemas.microsoft.com/cdo/configuration/smtpserver[/URL]") = "smtp.gmail.com"
        .Item("[URL]http://schemas.microsoft.com/cdo/configuration/sendusing[/URL]") = 2
        .Item("[URL]http://schemas.microsoft.com/cdo/configuration/smtpserverport[/URL]") = 465
        .Update
    End With
    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "This is line 1" & vbNewLine & _
              "This is line 2" & vbNewLine & _
              "This is line 3" & vbNewLine & _
              "This is line 4"
    With iMsg
        Set .Configuration = iConf
        .To = "[EMAIL="mrexceltest@gmail.com"]mrexceltest@gmail.com[/EMAIL]"
        .CC = ""
        .BCC = ""
        ' Note: The reply address is not working if you use this Gmail example
        ' It will use your Gmail address automatic. But you can add this line
        ' to change the reply address  .ReplyTo = "[EMAIL="Reply@something.nl"]Reply@something.nl[/EMAIL]"
        .From = "[EMAIL="mrexceltest@gmail.com"]mrexceltest@gmail.com[/EMAIL]"
        .Subject = "Important message"
        .TextBody = strbody
        .Send
    End With
End Sub
 
'-------------------------------------- ACTIVATE REFERECES ---------------------------------------
Function AddIEFRAME()
'Add referance from library named Microsoft internet contol
On Error GoTo 1
ThisWorkbook.VBProject.References.AddFromFile ("C:\WINDOWS\System32\ieframe.dll")
1:
End Function
Function AddMsHtml()
'Add referance from library named Microsoft HTML object library
On Error GoTo 1
ThisWorkbook.VBProject.References.AddFromFile ("C:\WINDOWS\System32\MsHtml.tlb")
1:
End Function
Function AddCDOWIN()
'Add referance from library named Microsoft CDO for WIn 2000 library
On Error GoTo 1
ThisWorkbook.VBProject.References.AddFromFile ("C:\WINDOWS\System32\cdosys.dll")
1:
End Function
 
Last edited:
Upvote 0
You are missing bits here:

HTML: 
    With Flds
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "mrexceltest@gmail.com"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "mrexceltest321"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
        .Update
    End With

It got truncated by html in my previous post.
 
Upvote 0
Yes, yes i saw that two. after I have apply changes, I'm still getting the Error "The transport failed to connect to the server"... It seams that this will be hard to crack.
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
VBA Send a formatted mail without Outlook
Replies
4
Views
684
Forsberg
F
M
  • Solved
bypassing hyperlink() maximum characters (many links on sheet)
Replies
7
Views
370
NoSparks
N
M
  • Solved
Optimizing my rows to send e-mail to different address
Replies
2
Views
163
MasterBash
M
StephenC101
  • Question Question
Mac Excel macro help needed please
Replies
1
Views
719
jkpieterse
jkpieterse
R
  • Solved
Control text formatting (e.g. font, color, etc.) when using Excel VBA to automate emails via Outlook
Replies
2
Views
264
Roland Hoelscher
R

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top