Creating an email from excel

haleem100

Board Regular
Joined
Jul 29, 2014
Messages
53
Hi
I am trying to create a macro from Excel to create email using GMail
When I use the code, it gives me an error, is there something wrong
Can any one advise
Sub CDO_Mail_Small_Text()
Dim iMsg As Object
Dim iConf As Object
Dim strbody As String
' Dim Flds As Variant


Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")


.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
' .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
' = "smtp.gmail.com"
' .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 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 = "person@email.com"
.CC = ""
.BCC = ""
.From = """Person"" < person@email.com >"
.Subject = "Subject Line"
.TextBody = strbody
.Send
End With
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
It looks like you got the code from Ron de Bruin's site. Since you do not include the error that you received, the only suggestion is to download the example workbook that is linked on the CDO page and look at the GMail code that is in there.

Sending mail from Excel with CDO
 
Upvote 0
The error I got is Invalid or unqualified reference
I had put the error on the bold
Sub CDO_Mail_Small_Text()
Dim iMsg As Object
Dim iConf As Object
Dim strbody As String
' Dim Flds As Variant


Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")


.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
' .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
' = "smtp.gmail.com"
' .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 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 = "person@email.com"
.CC = ""
.BCC = ""
.From = """Person"" < person@email.com >"
.Subject = "Subject Line"
.TextBody = strbody
.Send
End With
 
Upvote 0
In looking at the CDO code from the attachment on the website, and at your code, you are using <b>.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2</b> but not using the <b>With</b> and <b>End With</b>, which is why you are getting the error. The <b>.Item</b> portion means that it is part of something, in this case, it is part of <b>With Flds</b>, which you do not have. Below is the code from the CDO attachment, which might help.

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("http://schemas.microsoft.com/cdo/configuration/sendusername") = "Full GMail mail address"
'.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "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"" <Reply@something.nl>"

'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

    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")

    iConf.Load -1    ' CDO Source Defaults
    Set Flds = iConf.Fields
<b>    
    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
</b>
    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 = "Mail address receiver"
        .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 = "Reply@something.nl"
        .From = """YourName"" <Reply@something.nl>"
        .Subject = "Important message"
        .TextBody = strbody
        .Send
    End With

End Sub
 
Upvote 0
'If you get this error : The transport failed to connect to the server
'then try to change the SMTP port from 25 to 465

Hi,

I have changed the ports 25 to 465 but I still receive the error message. I entered my own user name and GMAIL password. No luck. Any suggestions? Thanks.
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

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