How to Send an Email using Excel Macro from Gmail or Yahoo

.

Dear Readers,

In this article, we are going to learn how to send an email automatically by Excel Excel VBA. While executing the Excel Macro, sometimes you may need to send some Details or Results directly to your email ID or any other ID using Excel Macro. If you have some report or details which you want to send in a mail without log-in to your email account.
This you are going to achieve by using CDO Object.

What is CDO (Collaboration Data Objects)??

.
This Object is basically used for Active Messaging, Enables user to Access Global Server Objects and Address List For more about this read this Microsoft Help.

In Microsoft Excel, to use this Object you need to Add CDO Reference.

Add CDO Reference

Add CDO Reference

Note: Without adding this reference you can Run the below Code. For running this Code, you can use below line to create Object runtime:


Set myMail = CreateObject("CDO.Message")

In this article, i will explain you 2 simple methods, to send an email by Excel VBA.

1. Excel VBA: Send email from Gmail



Sub SendEmailUsingGmail()
    
    On Error GoTo Err
    
    Dim NewMail As Object
    Dim mailConfig As Object
    Dim fields As Variant
    Dim msConfigURL As String
    
    Set NewMail = CreateObject("CDO.Message")
    Set mailConfig = CreateObject("CDO.Configuration")
    
    ' load all default configurations
    mailConfig.Load -1
    
    Set fields = mailConfig.fields
    
'Set All Email Properties
    
    With NewMail
        .Subject = "Test Mail from LearnExcelMacro.com"
        .From = "email@gmail.com"
        .To = "email2@gmail.com;email3@gmail.com"
        .CC = "email4@gmail.com"
        .BCC = ""
        .textbody = ""
    End With
    
    msConfigURL = "http://schemas.microsoft.com/cdo/configuration"
    
    With fields
        'Enable SSL Authentication
        .Item(msConfigURL & "/smtpusessl") = True
        
        'Make SMTP authentication Enabled=true (1)
        .Item(msConfigURL & "/smtpauthenticate") = 1
        
        'Set the SMTP server and port Details
        'To get these details you can get on Settings Page of your Gmail Account
        .Item(msConfigURL & "/smtpserver") = "smtp.gmail.com"
        .Item(msConfigURL & "/smtpserverport") = 465
        .Item(msConfigURL & "/sendusing") = 2
        
        'Set your credentials of your Gmail Account
        .Item(msConfigURL & "/sendusername") = "email@gmail.com"
        .Item(msConfigURL & "/sendpassword") = "********"
        
        'Update the configuration fields
        .Update
        
    End With
    NewMail.Configuration = mailConfig
    NewMail.Send
    MsgBox ("Mail has been Sent")
    
Exit_Err:
    
    Set NewMail = Nothing
    Set mailConfig = Nothing
    End
    
Err:
    Select Case Err.Number
    
    Case -2147220973  'Could be because of Internet Connection
        MsgBox " Could be no Internet Connection !!  -- " & Err.Description
        
    Case -2147220975  'Incorrect credentials User ID or password
        MsgBox "Incorrect Credentials !!  -- " & Err.Description
        
    Case Else   'Rest other errors
        MsgBox "Error occured while sending the email !!  -- " & Err.Description
    End Select
    
    Resume Exit_Err
    
End Sub

2. Excel VBA: Send email from Yahoo



Sub SendEmailUsingYahoo()
    
    On Error GoTo Err
    
    Dim NewMail As Object
    Dim mailConfig As Object
    Dim fields As Variant
    Dim msConfigURL As String
    
    Set NewMail = CreateObject("CDO.Message")
    Set mailConfig = CreateObject("CDO.Configuration")
    
    ' load all default configurations
    mailConfig.Load -1
    
    Set fields = mailConfig.fields
    
'Set All Email Properties
    
    With NewMail
        .Subject = "Test Mail from LearnExcelMacro.com"
        .From = "email@yahoo.co.in"
        .To = "email1@gmail.com;email2@yahoo.com"
        .CC = "email@email.com"
        .BCC = ""
        .textbody = ""
    End With
    
    msConfigURL = "http://schemas.microsoft.com/cdo/configuration"
    
    With fields
        'Enable SSL Authentication
        .Item(msConfigURL & "/smtpusessl") = True
        
        'Make SMTP authentication Enabled=true (1)
        .Item(msConfigURL & "/smtpauthenticate") = 1
        
        'Set the SMTP server and port Details
        'To get these details you can get on Settings Page of your yahoo Account
        .Item(msConfigURL & "/smtpserver") = "smtp.mail.yahoo.com"
        .Item(msConfigURL & "/smtpserverport") = 465
        .Item(msConfigURL & "/sendusing") = 2
        
        'Set your credentials of your yahoo Account
        .Item(msConfigURL & "/sendusername") = "email@yahoo.co.in"
        .Item(msConfigURL & "/sendpassword") = "******"
        
        'Update the configuration fields
        .Update
        
    End With
    NewMail.Configuration = mailConfig
    NewMail.Send
    MsgBox ("Mail has been Sent")
    
Exit_Err:
    
    Set NewMail = Nothing
    Set mailConfig = Nothing
    End
    
Err:
    Select Case Err.Number
    
    Case -2147220973  'Could be because of Internet Connection
        MsgBox " Could be no Internet Connection !!  -- " & Err.Description
        
    Case -2147220975  'Incorrect credentials User ID or password
        MsgBox "Incorrect Credentials !!  -- " & Err.Description
        
    Case Else   'Rest other errors
        MsgBox "Error occured while sending the email !!  -- " & Err.Description
    End Select
    
    Resume Exit_Err
    
End Sub
 

Excel VBA to Send Email with HTML Body

For sending HTML Body you just need to Change the Email Properties which you are setting above



With NewMail
  .Subject = "Test Mail from LearnExcelMacro.com"
  .From = "email@yahoo.com"
  .To = "email2@gmail.com;email3@yahoo.com"
  .CC = "email@email.com"
  .BCC = ""
  .HTMLBody = "Write your complete HTML Page"
End With

Excel VBA to Send Email with an Attachment


With NewMail
  .Subject = "Test Mail from LearnExcelMacro.com"
  .From = "vishwamitra01@yahoo.com"
  .To = "vishwamitra02@gmail.com;info@learnexcelmacro.com"
  .CC = "vishwamitra01@gmail.com"
  .BCC = ""
  .HTMLBody = "Write your complete HTML Page"

' For multiple Attachment you can add below lines as many times

  .AddAttachment "C:\ExcelMacro-help.xls"
  .AddAttachment "C:\ExcelMacro-help2.xls"
End With

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

130 Comments

  1. kakada

    Hi. The code sending email from yahoo is not work.

    Error message : Object required (Error 424)

    Please help.

    Thanks in advance.

    Reply
    • Vishwamitra Mishra

      Hi Kakada,

      Can you please copy paste your code here so that i can see or can you send it to Info@learnexcelmacro.com. I will fix it and send it to you.

      Thanks,

      Vish

      Reply
      • Rizal

        hi mishra,,

        please help my code.

        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
        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”) = “xxxx@gmail.com”
        .Item(“http://schemas.microsoft.com/cdo/configuration/sendpassword”) = “********”
        .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

        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 = “xxxx@gmail.com”
        .CC = “”
        .BCC = “”

        .From = “xxxx@gmail.com”
        .Subject = “Important message”
        .TextBody = strbody
        .Send
        End With

        End Sub

        Reply
      • ANURAG SHARMA

        Will this code be working with “webmail.sbilife.co”.in or different codes are required for sending mail through webmail.sbilife.co.in. Can you please help.

        Reply
  2. kakada

    Thank Vishwamitra Mishra for your reply.

    My sending yahoo email code is copied from above code,

    and now I have find the solution.

    I have to change myMail to NewMail for three lines.

    myMail.Configuration.Fields.Item _ 'NewMail.Configuration…..

    ("http://schemas.microsoft.com/cdo/configuration/smtpserver&quot😉 = "smtp.mail.yahoo.com"

    myMail.Configuration.Fields.Item _ 'NewMail.Configuration…..

    ("http://schemas.microsoft.com/cdo/configuration/smtpserverport&quot😉 = 465

    myMail.Configuration.Fields.Item _ 'NewMail.Configuration…..

    ("http://schemas.microsoft.com/cdo/configuration/sendusing&quot😉 = 2

    Reply
    • Vishwamitra Mishra

      So is it working now?

      Reply
      • kakada

        Yes, it is working now. Thanks.

        Reply
        • Vishwamitra Mishra

          You are Welcome 🙂

          Reply
      • Marcos Barboza

        Hi, I need some help. I trying to first convert a excel spreadsheet to pdf and send it as an attachment via gmail. Please help!

        Sub sendReminderMail()
        ChDir “C:\Users\mbarboza\Desktop\KW Reports”
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Sheet1.Range(“P1”), _
        OpenAfterPublish:=True

        Dim myMail As Object
        Dim mailconfig As Object
        Dim fields As Variant
        Dim msconfigURL As String

        Set myMail = CreateObject(“CDO.Message”)
        Set mailconfig = CreateObject(“CDO.Configuration”)

        myMail.Configuration.fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpusessl”) = True

        myMail.Configuration.fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpauthenticate”) = 1

        myMail.Configuration.fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserver”) = “smtp.gmail.com”

        myMail.Configuration.fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 465

        myMail.Configuration.fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendusing”) = 2

        myMail.Configuration.fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendusername”) = “mtbarboza1@gmail.com”

        myMail.Configuration.fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendpassword”) = “*******”

        myMail.Configuration.fields.Update

        With myMail
        .Subject = “Test Email from Marcos”
        .From = “mtbarboza1@gmail.com”
        .To = “mbarboza@miamiherald.com”
        .CC = “”
        .BCC = “”
        .TextBody = “”

        End With

        On Error Resume Next
        myMail.Send
        MsgBox (“Mail has been Sent”)
        Set myMail = Nothing

        End Sub

        Reply
  3. Julien

    Hi,

    Thanks for your VBA code.

    It also worked on my computer but unfortunately when I send an email, even if the email is well received and sent, it doesn't go to my sent items folder. (for a Gmail and Yahoo account)

    Do you know why?

    I would to keep tracks of the email sent.

    Thanks

    Julien

    Reply
    • Vishwamitra Mishra

      Hi Julien,

      It is not possible, that mail will not be there in the sent items. Can you please check you code again and see if you are checking the sent items of the same account which you have mentioned in the code.

      I would like to see your code, can you copy paste here?

      Thanks,

      Vish

      Reply
      • Julien

        Thanks Vish for your answer.

        In fact it worked for Gmail but not for Yahoo (I checked my yahoo settings and I checked the box to have copy of my emails sent to my sent folder

        This is my code (it's yours with some modification)

        Public Function SendEmailUsingOther(strSendTo As String, strSubject As String, MailType As String, fld As String, Optional tempfile, Optional attch1, Optional attch2, Optional attch3)

        MailSent = False

        Dim NewMail As CDO.Message

        Set NewMail = New CDO.Message

        'Set myMail = CreateObject("CDO.Message")

        'Enable SSL Authentication

        NewMail.Configuration.Fields.Item _

        ("http://schemas.microsoft.com/cdo/configuration/smtpusessl&quot😉 = True

        'Make SMTP authentication Enabled=true (1)

        NewMail.Configuration.Fields.Item _

        ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate&quot😉 = 1

        'Set the SMTP server and port Details

        'To get these details you can get on Settings Page of your Gmail Account

        Select Case MailType

        Case "yahoo"

        NewMail.Configuration.Fields.Item _

        ("http://schemas.microsoft.com/cdo/configuration/smtpserverport&quot😉 = 465

        NewMail.Configuration.Fields.Item _

        ("http://schemas.microsoft.com/cdo/configuration/smtpserver&quot😉 = "smtp.mail.yahoo.com"

        Case "gmail"

        NewMail.Configuration.Fields.Item _

        ("http://schemas.microsoft.com/cdo/configuration/smtpserverport&quot😉 = 465

        NewMail.Configuration.Fields.Item _

        ("http://schemas.microsoft.com/cdo/configuration/smtpserver&quot😉 = "smtp.gmail.com"

        End Select

        NewMail.Configuration.Fields.Item _

        ("http://schemas.microsoft.com/cdo/configuration/sendusing&quot😉 = 2

        'Set your credentials of your Gmail Account

        SenderEmail = InputBox("Please enter your email address")

        NewMail.Configuration.Fields.Item _

        ("http://schemas.microsoft.com/cdo/configuration/sendusername&quot😉 = SenderEmail

        NewMail.Configuration.Fields.Item _

        ("http://schemas.microsoft.com/cdo/configuration/sendpassword&quot😉 = InputBox("Please enter your password")

        'Update the configuration fields

        NewMail.Configuration.Fields.Update

        NewMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl&quot😉 = "true"

        'Set All Email Properties

        '##########################""

        'Open the HTML file using the FilesystemObject into a TextStream object

        Set FSObj = New Scripting.FileSystemObject

        Set TStream = FSObj.OpenTextFile(fld, ForReading)

        'Now set the HTMLBody property of the message to the text contained in the TextStream object

        strHTML = TStream.ReadAll

        '################################

        With NewMail

        .subject = strSubject

        .From = SenderEmail

        .To = strSendTo

        '.CC = ""

        .BCC = ""

        '.TextBody = ""

        .HTMLBody = strHTML

        If tempfile "" Then

        .AddAttachment tempfile

        End If

        If Trim(attch1) "" Then

        .AddAttachment attch1

        End If

        If Trim(attch2) "" Then

        .AddAttachment attch2

        End If

        If Trim(attch3) "" Then

        .AddAttachment attch3

        End If

        End With

        'MsgBox (strHTML)

        NewMail.Send

        'MsgBox ("Mail has been Sent")

        MailSent = True

        pbsend:

        If MailSent = False Then

        MsgBox ("Due to an issue (password, email address,attachments…), the email hasn't been sent")

        End If

        'Set the NewMail Variable to Nothing

        Set NewMail = Nothing

        End Function

        Thanks for your help

        Reply
        • Vishwamitra Mishra

          Thanks Julien,

          I will get back to you shortly 🙂

          Thanks,

          Vishwa

          Reply
          • Marty Vuttera

            Hi Vish.
            I am so sorry to bother you but I have a code problem that I have been trying to solve for 3 weeks. I am self taught in VBA but I am not good enough to figure this out. I have written CDO code for sending emails for both comcast and gmail and they work fine but when I try to apply it to yahoo it does not work. I have tried asking on mrexcel but they could not help and many other people. None could tell me what is wrong. I then created a new email at yahoo with a new password. That didn’t work. PLEASE take the time to look at this. I haven’t got anyone else to turn to.
            What I want to do is send cells A5″:W28 in an excel worksheet. I THINK if I get your code to work I MAY be able to change it enough to send the cells i need as a PDF. The people I send to do not have Excel or outlook.

            I have the latest Excel and running windows 10.

            Below is the code I copied and I replaced the places that needed it with my information
            My address and password are in it so you can try it like i do.

            That is a made up address so you I could try the code.

            My E-Mail address is D1C@comcast.net.

            Sub SendEmailUsingYahoo()

            On Error GoTo Err

            Dim NewMail As Object
            Dim mailConfig As Object
            Dim fields As Variant
            Dim msConfigURL As String

            Set NewMail = CreateObject(“CDO.Message”)
            Set mailConfig = CreateObject(“CDO.Configuration”)

            ‘ load all default configurations
            mailConfig.Load -1

            Set fields = mailConfig.fields

            ‘Set All Email Properties

            With NewMail
            .Subject = “Test Mail from LearnExcelMacro.com”
            .From = “testncaa@yahoo.com” ‘ Changed
            .To = “D1C@comcast.net” ‘Changed
            .CC = “”
            .BCC = “”
            .TextBody = “This is a test”
            End With

            msConfigURL = “http://schemas.microsoft.com/cdo/configuration”

            With fields
            ‘Enable SSL Authentication
            .Item(msConfigURL & “/smtpusessl”) = True

            ‘Make SMTP authentication Enabled=true (1)
            .Item(msConfigURL & “/smtpauthenticate”) = 1

            ‘Set the SMTP server and port Details
            ‘To get these details you can get on Settings Page of your yahoo Account
            .Item(“http://schemas.Microsoft.Com/cdo/configuration/smtpusetls”) = True
            .Item(msConfigURL & “/smtpserver”) = “smtp.mail.yahoo.com”
            .Item(msConfigURL & “/smtpserverport”) = 587
            .Item(msConfigURL & “/sendusing”) = 2

            ‘Set your credentials of your yahoo Account
            .Item(msConfigURL & “/sendusername”) = “testncaa@yahoo.com” ‘Changed
            .Item(msConfigURL & “/sendpassword”) = “martin105*” ‘Changed

            ‘Update the configuration fields
            .Update

            End With
            NewMail.Configuration = mailConfig
            NewMail.Send
            MsgBox (“Mail has been Sent”)

            Exit_Err:

            Set NewMail = Nothing
            Set mailConfig = Nothing
            End

            Err:
            Select Case Err.NUMBER

            Case -2147220973 ‘Could be because of Internet Connection
            MsgBox ” Could be no Internet Connection !! — ” & Err.Description

            Case -2147220975 ‘Incorrect credentials User ID or password
            MsgBox “Incorrect Credentials !! — ” & Err.Description

            Case Else ‘Rest other errors
            MsgBox “Error occured while sending the email !! — ” & Err.Description
            End Select

            Resume Exit_Err

            End Sub

            This is the Err I got.

            Case -2147220973 ‘Could be because of Internet Connection
            MsgBox ” Could be no Internet Connection !! — ” & Err.Description

            Thank you so much

            Marty

  4. Murali

    Hi,

    I just need to send an similar details for more the 50 members only amount will be changed…. it will take more time to send an email to each and every person.

    Can you please help me on this…… is there any ways to creat a macro for this problem…

    Thanks,

    Murali

    Reply
    • Vishwamitra Mishra

      Hi Murali,

      Yes, it's easy to do the same. You need to put the above code in a loop and keep sending the email by taking the email Address and corresponding Subject and Body of the email.

      One Sample Excel is sent to your email ID. You can customize the same for your requirement. If need any help, let me know.

      Thanks,

      Vishwa

      Reply
  5. Amanda Jeffords

    This is a great example. Just one question. I have a module in Excel that will attach a workbook and send an email through gmail. When the email arrives the file is correpted and [WARNING: A/V UNSCANNABLE] is in the message header. Any ideas?

    Reply
    • Vishwamitra Mishra

      Hi Amanda,

      Thanks for writing.

      I tried with an example and i did not face this issue. Would you please share your code, so that i can try to re-produce this issue.

      Thanks,

      Vish

      Reply
  6. daniel

    I'm trying to get this to work but am unable to. With the posted code, I'm getting an error –

    "Run-time error '-2147220973 (80040213)':

    The transport failed to connect to the server."

    I double checked my gmail credentials. I also tried smtpserverport = 465 but still get the same error. Are there other settings that I can add or change?

    Reply
  7. Sanjaya

    Hi

    I was looking mostly for this! First of all thank you very much!

    I also have a question!

    Can this be adopted to receive delivery notifications?(like return receipts)

    Any help will be appreciated!

    Reply
    • Vishwamitra Mishra

      Hi Sanjay,

      Thanks alot for the feedback.
      I am not sure about the delivery notification. I will try and if i get anything, i will let you know.
      Keep giving your valuable feedback.
      Vish

      Reply
  8. Sufiyan

    Hello there! I need some help with macro for sending e-mails.

    I use 2 email addresses at work. One is Microsoft Exchange Server and the other one is my work inbox. I need my code to take the work inbox to send e-mails, since i do not have access to send external emails using microsoft exchange server. is there a way outlook can choose the inbox from the drop down menu and send e-mails? i am using this at the moment

    Set OutApp = CreateObject("Outlook.Application")

    OutApp.Session.Logon

    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next

    With OutMail

    .To = "e-mail addresses"

    .CC = ""

    .BCC = ""

    .Subject = ""

    .Body = ""

    .Body = ""

    .Body = ""

    .Send

    End With

    On Error GoTo 0

    Set OutMail = Nothing

    Set OutApp = Nothing

    With Application

    .ScreenUpdating = True

    .EnableEvents = True

    End With

    End Sub

    Reply
    • Vishwamitra Mishra

      Hi,
      You can create two emails and while sending the email use the below code.

      Instead of writing just .Send use the below line:

      .SendUsingAccount = OutApp.Session.Accounts.Item(1)

      Where item(1), item(2), .. etc will be your accounts which are configured in your outlook.

      Try it and let me know, if it helps.

      Thanks,
      Vishwa

      Reply
  9. Andre

    Hi

    When I run the code to gmail i get the error:

    Run-time error '-2147220973(80040213)'

    The transport failed to connect to server.

    Do you know how to fix that?

    Reply
    • Vishwamitra Mishra

      Hi Andre,

      I have sent an email to your email id please check.

      Thanks,

      Vish

      Reply
  10. hoys

    Hi

    I also get this error when I run the code to gmail :

    Run-time error ‘-2147220973(80040213)’

    The transport failed to connect to server.

    Could you advise, thanks in advance! 🙂

    Reply
    • Vishwamitra Mishra

      Hi Hoys,
      This error usually comes when you are trying send email from a network where GMAIL is blocked. Please check if gmail is not blocked in your network.
      Thanks,
      Vish

      Reply
      • Charles

        Hi,

        Thank you for this code. I am getting the same error:

        Run-time error ‘-2147220973(80040213)’

        The transport failed to connect to server.

        I am working from home and gmail should not be blocked on my network. How do I check that? Any other way I could fix this.

        Thanks in advance.

        Reply
        • Charles

          Its working! Just had to use a different port. Thanks!!

          Reply
          • Vishwamitra Mishra

            Hi Charles,
            Did you use 465 or 587?

            For me its working at 25 itself.

            Thanks

          • Charles

            Port 587. Thanks Vish.

          • Vishwamitra Mishra

            Thanks Charles for this solution.
            Hi friends, if you also facing the same error, then can try using Port as 587, instead of 25.

            Thanks again Charles.

  11. Paul

    Any luck with this error? I get it when trying the gmail and yahoo code:

    Run-time error ‘-2147220973(80040213)’

    The transport failed to connect to server.

    Reply
  12. Ramir

    Hi,

    I want to create an e-mail draft in gmail by using VBA with Excel, so that I can modify the body of the e-mail before sending it.

    Is this possible? Can you help me?

    Thanks a lot!

    Reply
    • Vishwamitra Mishra

      Hi Ramir,
      For CDO.Message it is not possible to display before sending it. You can just send it. Whatever you want to modify, you can modify in the code or in excel before sending it.

      Reply
  13. djemy7

    not working in my gmail account

    Reply
    • Vishwamitra Mishra

      Try using the Port as 587, instead of 25.

      Reply
  14. Kannan

    I am trying to send mails using macro via outlook. The macro works fine in my system, but the same throws security prompt error in outlook if sent from my colleague id? why is outlook showing security prompt saying…to allow or deny…? how should this be resolved. Is this an excel macro or outlook issue?

    Reply
    • Vishwamitra Mishra

      Looks like it is your Outlook security issue.

      Reply
  15. Tancredo

    One question. I have a module in Excel that will attach a workbook and send an email through gmail. When the email arrives the file is message and excel found unreadable content in 'FILE'. want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.

    When I try to open a file appears blank.. Any ideas?

    Reply
  16. Jerry Vellutini

    This worked the first time I tried it with my g-mail account,

    I created another g-mail account and now I get this error message with both accounts:

    Run-time error '2147220975 (80040211)

    The message could not be sent to the SMPT server. The transport

    error code was 0x80040217. The server response was not available.

    Reply
  17. steve

    Is it possible to ammend the code to use Hotmail?

    Reply
  18. Gyan

    Dear Vishwamitra,

    I'm absolutely overjoyed to find this link. Quite informative I must admit.

    I was wondering if you could help me with a problem that I've been stuck with. To my knowledge CDO method for Gmail sends the emails directly without giving an opportunity to review it before clicking the send button. Is there a way I can create Gmail drafts with attachments, but preview them before sending.

    I have a VBA code but the problem is that it doesn't allow a preview, which is extremely critical. The irony is that I can't use Outlook as our company is on Google mail.

    Look forward to your suggestions / inputs.

    Happy to provide more info!

    Thanks,

    Gyan

    Reply
    • Roberto

      Hi,

      Did you ever get a response about this? I have the same issue; I need to sent emails using vba (excel) but my company is on Google e-mail.

      I tried the code with my personal e-mail acconut and it works; however when I thry using my companies e-mail, it doesn´t (myname@mycompany.com -> on google).

      thanks,

      Reply
    • Shlomi

      Hi,
      I’m dealing with same problem you wrote back on 2013.
      did you able to find a way to make preview before sanding the email by Gmail (via VBA code) ?

      In case you did, I’ll be happy to know how ?

      Thanks !

      Reply
      • Vishwamitra Mishra

        Hi,
        There is no built-in option provided by google to see a preview before sending the email by gmail, yahoo etc. If you need to have a preview of the email before sending, that can be built in excel using Excel UserForm which will look like gmail email draft screen.

        Reply
  19. Chippy

    Does this work in outlook? I t would be helpful if the same is there for outlook.

    Reply
  20. Warlito Jaque

    Hi Vishwa, I still have this error, even trying 25, 465, and 587

    Run-time error ‘-2147220973(80040213)’

    The transport failed to connect to server.

    But now i started to understand ,probably it is because our network is protected with proxy server w/c if uncheck will disconnect the network. I know of your vast knowledge on this and hope your thought for ways to address this. more power! Warlito

    Reply
  21. raj

    I run the code but getting an error : the SMTP server name is required.and was not

    found in configuration source

    Reply
  22. Jake Evans

    Is there a way to have the code prompt the Excel user for a username and password?

    Is there a way to have the user send a range of cells? Or a selection of cells?

    This is an awesome article! Thanks so much. 🙂

    Reply
  23. orion

    Hello,

    I want to attach only (worksheetsheet1),and send from gmail;

    Clearor reset (worksheetsheet1) after to be ready to send a newmail with new numbersdata

    Thanks in advance

    Reply
      • Orion

        Hi Mishra,

        thanks for the suggestion, but i wont to send Activesheet from Gmail, not from Outlook. Can you give me a solution please?

        Reply
        • Vishwamitra Mishra

          Hi Orion,

          Concept will remain same. Just use the same concept for sending it through Gmail. if you are still not clear how to use it, send me an email. I will reply you with the working code.
          Regards,
          Vishwa

          Reply
          • Francisco

            Hi,

            I will love to have that code as well.

            Thanks

            Francisco

      • Eyong

        Hi dear, I need your help. I have used the above codes to send an email through gmail. I have used the semi columns to add 5 CC and 5 BCC. The only issue is when I do not use all 5 CCs or BCCs it gives an error. The emails for CCs and BCCs are found in a range of cells. How can I have the option of 5 CCs and 5 BCCs but be able to use one, two or none of them ?
        Thanks in advance

        Reply
  24. Vagner

    ಹಲೋ, Mishra!

    I'm sending emails using a VBA macro with Excel and Outlook2010.

    But for gmails-yahoo-hotmail-etc users it is not working well. The image become an attached file and the email content is “translated” into codes (alphanumeric).

    Outlook users open it adequately, they can see the image.

    Do you know how to solve it? Where I’m doing it wrong?

    ವಂದನೆ

    =====+=====+=====+=====+=====+========+===+

    Sub enviar_email()

    'CustomerAddress = "Vagner.duarte@estacio.br"

    CustomerAddress = "vagner@vagnerduarte.com"

    For i = 2 To 50000

    If (Sheets("Dados").Range("A" & i) = "") Then

    Exit For

    Else

    nome = Sheets("Dados").Range("A" & i)

    Sheets("E-mail").Select

    ActiveSheet.Shapes.Range(Array("img_pascoa")).Select

    'Selection.Copy

    ActiveWorkbook.EnvelopeVisible = True

    With ActiveSheet.MailEnvelope

    .Item.To = Sheets("Dados").Range("A" & i)

    .Item.Subject = nome & " deseja um Feliz Páscoa!"

    .Item.send

    End With

    End If

    Sheets("Dados").Select

    Range("A1").Select

    Next

    MsgBox "E-mail(s) enviado com sucesso!"

    End Sub

    =====+=====+=====+=====+=====+========+===+

    It consists in two columns: 'DE' is the variable data that will be shown in the subjet (the name of the person). The other column is the account that the email will be sent.

    Reply
  25. Laura

    I keep getting a user defined error message and it highlights the sub name, i tried to define it for hotmail but not sure if it works

    PLEASE HELP

    Sub NewMail()
    Dim NewMail As CDO.Message

    Set NewMail = New CDO.Message

    ‘Enable SSL Authentication
    NewMail.Configuration.Fields.Item _
    (“http://login.live.com/cdo/configuration/smtpusessl”) = True

    ‘Make SMTP authentication Enabled=true (1)

    NewMail.Configuration.Fields.Item _
    (“http://login.live.com/cdo/configuration/smtpauthenticate”) = 1

    ‘Set the SMTP server and port Details
    ‘To get these details you can get on Settings Page of your Gmail Account

    NewMail.Configuration.Fields.Item _
    (“http://login.live.com/cdo/configuration/smtpserver”) = “smtp.live.com”

    NewMail.Configuration.Fields.Item _
    (“http://login.live.com/cdo/configuration/smtpserverport”) = 25

    NewMail.Configuration.Fields.Item _
    (“http://login.live.com/cdo/configuration/sendusing”) = 2

    ‘Set your credentials of your Gmail Account

    NewMail.Configuration.Fields.Item _
    (“http://login.live.com/cdo/configuration/sendusername”) = “**********@hotmail.co.za”

    NewMail.Configuration.Fields.Item _
    (“http://login.live.com/cdo/configuration/sendpassword”) = “*********”

    ‘Update the configuration fields
    NewMail.Configuration.Fields.Update

    ‘Set All Email Properties

    With NewMail
    .Subject = “Test Mail”
    .From = “***********@hotmail.co.za”
    .To = “**********@****.com”
    .CC = “”
    .BCC = “”
    .textbody = “”
    End With

    NewMail.Send
    MsgBox (“Mail has been Sent”)

    ‘Set the NewMail Variable to Nothing
    Set NewMail = Nothing
    End Sub

    Reply
  26. JASKARAN SINGH

    Sir,
    I was looking how to send birthday e-mail automatically from excel through yahoo.I had once sent by using MS outlook but now it is not functioning.I came across your website (and your name) and thankfully I believe you will help me to solve problem. I used the following macro:
    Sub SendBirthdayWishes()
    Dim OL As Object, olMail As Object, blnOpened As Boolean
    Dim ws As Worksheet, c As Range, i As Long, j As Long
    Dim k As Long, arrData(1 To 1000, 1 To 3) As Variant
    Dim sBody As String, objwShell As Object
    Application.ScreenUpdating = False
    Set ws = Sheets(“Sheet1”)
    ‘Set ws = ThisWorkbook.Worksheets(“Sheet1”)
    For Each c In ws.Range(“C2:C” & ws.Cells(ws.Rows.Count, 3).End(xlUp).Row)
    ‘If Month(c.Value) = Month(VBA.Date()) And Day(c.Value) = Day(VBA.Date()) Then
    If Month(c.Value) = Month(VBA.Date()) Then
    i = i + 1
    arrData(i, 1) = c.Offset(0, -2).Value
    arrData(i, 2) = c.Offset(0, -1).Value
    arrData(i, 3) = c.Value
    ‘ c.Offset(0, 1) = arrData(i, 3)
    End If
    Next c
    If IsEmpty(arrData(1, 1)) Then Exit Sub
    On Error Resume Next
    Set objwShell = CreateObject(“wscript.shell”)
    objwShell.Run (“””C:Program FilesExpress ClickYesClickYes.exe”” -activate”)
    Set OL = GetObject(, “Outlook.Application”)
    If OL Is Nothing Then
    Set OL = CreateObject(“Outlook.Application”)
    blnOpened = True
    End If
    On Error GoTo 0
    For j = LBound(arrData) To UBound(arrData)
    If IsEmpty(arrData(j, 1)) Then Exit For
    Set olMail = OL.CreateItem(0)
    olMail.To = arrData(j, 2)
    For k = LBound(arrData) To UBound(arrData)
    If IsEmpty(arrData(k, 1)) Then Exit For
    olMail.Cc = arrData(k, 2) & “; ”
    Next k
    olMail.Subject = “Happy Birthday!”
    sBody = “Just wanted to wish you a happy birthday when you turn ”
    sBody = sBody & Year(arrData(j, 3)) – Year(VBA.Date()) & “!”
    sBody = sBody & vbNewLine & vbNewLine
    sBody = sBody & “JASKARAN MANJIT SIMRAN HARNOOR-JMSH” ‘Signature line
    olMail.Body = sBody
    olMail.Send
    ‘c.Offset(0, 2) = sBody
    Next j
    objwShell.Run (“””C:Program FilesExpress ClickYesClickYes.exe”” -stop”)
    ThisWorkbook.Saved = True
    Application.ScreenUpdating = True
    End Sub

    Please help me to modify the above macro to send birhtday wishes through YAHOO INSTEAD OF OUTLOOK

    Reply
  27. Patrick

    I want to receive a email before a long list with date will expire with 3 day…its posibile? if yes please explain me how can I do it.

    thanks

    Reply
  28. andreschuc

    muchisimas gracias por su codigo

    se los agradezco bastante

    Reply
  29. Amit Sharma

    Hi Kakada

    My company is using google to send emails. I have my official email id as amit

    *******.com

    Can you please send me the VBA to send emails from official email id from excel.

    Regards
    Amit Sharma

    Regards
    Amit Sharma

    Reply
  30. kanchana

    hi, i dont know how to send birthday wishes through ms excel and also how to use ms outlook. kindly tell me inwhich way to use it. clearly

    Reply
  31. Rudra Banerjee

    Hi,

    I tried with all the ports i.e. 25, 465 ,587 but the error is coming.
    please help.

    Thanks

    Reply
  32. ree

    Hi,

    I tried with all the ports for gmail i.e. 25, 465 ,587 but the error is coming.
    kindly help me,its urgent.

    Thanks

    Reply
  33. Michael

    All,

    Several people were having some issue with outlook. Here is the VBA I set up for use with MS Outlook 2010

    Private Declare Function ShellExecute Lib “shell32.dll” _
    Alias “ShellExecuteA” (ByVal hwnd As Long, ByVal lpOperation As String, _
    ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
    ByVal nShowCmd As Long) As Long
    Sub SendEMail()
    Dim Email As String, Subj As String
    Dim Msg As String, URL As String
    Dim r As Integer, x As Double
    For r = 2 To 7 ‘data in rows 2-7
    ‘ Get the email address
    Email = Cells(r, 3)

    ‘ Message subject
    Subj = “”

    ‘ Compose the message

    Msg = vbNewLine & “Dear ” & Cells(r, 1) & “,” & vbNewLine & vbNewLine
    Msg = Msg & “”

    ‘ Replace spaces with %20 (hex)
    Subj = Application.WorksheetFunction.Substitute(Subj, ” “, “%20″)
    Msg = Application.WorksheetFunction.Substitute(Msg, ” “, “%20”)

    ‘ Replace carriage returns with %0D%0A (hex)
    Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, “%0D%0A”) ‘ Create the URL
    URL = “mailto:” & Email & “?subject=” & Subj & “&body=” & Msg

    ‘ Execute the URL (start the email client)
    ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus

    ‘ Wait two seconds before sending keystrokes
    Application.Wait (Now + TimeValue(“0:00:05”))
    Application.SendKeys “{Tab}{Tab}{Tab}{Tab}{Tab}^{End}{Return}{Return}^v”
    Application.SendKeys “%s”
    Next r
    End Sub

    Private Sub Workbook_Open()

    End Sub

    Reply
    • tony

      IM trying to do it from a service account and it is setting my default personal email

      Reply
    • Bren

      Hi!
      I’m also getting Run-time eror and i’ve tried all three ports. Can you please provide an answer for this? It seems like multiple people are also having a lot of trouble

      Reply
      • Vishwamitra Mishra

        Hi Ben,

        I have fixed the code a bit now. Can you copy the latest code from the post and try it? Provide your feedback if it works for you.

        Reply
  34. Adebayo Sulaimon

    Please my colleague use to send a single message to multiple recipients using vba excel, I asked but he refuse to help
    I am to send a message to multiple recipients with different attchment to different reciever. please help me out with the Macros.
    thanks

    Reply
  35. Kumar

    .From is not working. It is not taking the respective mail id mentioned

    Reply
  36. Adrian

    Hi all,
    I am trying to use excel to send out email and also having the same excel as attachment. I am using yahoo to send out the mail and the attachment code is: ‘.Attachments.Add ActiveWorkbook.FullName but it say that it is send out an active worksheet, how can i resolve this?
    Many thanks.

    Reply
    • Vishwamitra Mishra

      Hi Adrian,

      The best and safe solution is to first save the your current workbook which you want to send it in a different location (may be in temp folder), attach the file from the temp location and send it. refrain from using the same workbook as an attachment while sending out an email.
      Let me know if this helps.
      refer the below article:
      http://learnexcelmacro.com/wp/2012/01/how-to-send-activeworkbook-as-attachment-in-email/

      Reply
  37. Winston

    Hi, First of all, Thank you for sharing this code.
    I set up the code as per yours. But the email is not being received. When I run the code it states its running and then displays the “Mail has been sent” message. But when I check the “to” email – nothing has been received. I’m not sure where the issue is.
    here is my code,
    Sub send_email_via_gmail()
    Dim myMail As CDO.Message
    Set myMail = New CDO.Message
    myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpusessl”) = True
    myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpauthenticate”) = 1
    myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserver”) = “smtp.gmail.com”
    myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 25
    myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendusing”) = 2
    myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendusername”) = “xxxx@gmail.com”
    myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendpassword”) = “xxxx”
    myMail.Configuration.Fields.Update
    With myMail
    .Subject = “Test Email”
    .From = “xxxx@gmail.com”
    .To = “xxxxx@gmail.com”
    .TextBody = “G’Day”
    .AddAttachment “C:test.xlsx”
    End With
    On Error Resume Next
    myMail.Send
    MsgBox (“Mail has been sent”)
    Set myMail = Nothing

    End Sub

    Would it be possible to see why the code is not doing what it suppose to. I note I do not receive any errors when executing the code.

    Thanks,

    Winston

    Reply
  38. harinder kumar

    Dear Sir

    I HAVE DATA OF EMPLOYEES IN EXCEL SHEET WITH EMAIL ID , I JUST WANT THAT EXCEL SENT THE EMAIL WHEN I CLICK ON BUTTON TO ALL EMAIL ID OR SELECTIVE EMAIL ID WITH FETCHING RESPECTIVE EMPLOYEE DATA .

    HOPING A FAVORABLE REPLY FROM YOUR END.

    Reply
    • harinder kumar

      Dear Sir , can you provide me the code for sending email in excel thorough lotus notes having email id in a column of excel sheet after fetching data of a particular employee if I click on a particular send button in excel to a specified email id .

      waiting for a prompt and reliable reply from your end with a suitable explaining example .

      Reply
  39. ssibsm

    ok..I have a basic knowledge about excel and am trying to learn new things…need some help with this..

    Example:I have made a Time sheet in excel for John..as John is not tech with IT….in this time sheet I have added details,login,logout,formulas for calculating the total hours etc…so when John receives it he just have to put the login and logout timings and the remaining is calculated automatically…
    so here is my question,

    1)can i email this active workbook as a body of the email(Not as attachment) to John??

    2)And when John receives the email he should be able to fill the sheets with timings in the active worksheet in the email itself and forward me back the same email.(No need to download,fill,attachment and send back headache for John)

    3)which mail supports this….I use outlook 2007…

    so I was just wondering is there any thing like this and Detailed Answer is much appreciated…Thanks in advance

    Reply
  40. Manikandan

    Hi am getting the following system error
    &H80040211(-2147220975)
    pls help
    regards,
    Manikandan

    Reply
  41. Mahi

    Hi,

    I’m trying to send mail notification through Gmail however when i use one recipient it works but it gives error message when I try the same with multiple.
    Error – run-time -2147220975 (80040211)’:
    The Message cannot be sent to the SMTP server. The transport error code was 0x80040217. The Server response was not available.

    code-
    Sub email()

    Dim NewMail As CDO.Message

    Set NewMail = New CDO.Message

    NewMail.Configuration.Fields.Item _
    (“http://schemas.microsoft.com/cdo/configuration/smtpusessl”) = True

    NewMail.Configuration.Fields.Item _
    (“http://schemas.microsoft.com/cdo/configuration/smtpauthenticate”) = 1

    NewMail.Configuration.Fields.Item _
    (“http://schemas.microsoft.com/cdo/configuration/smtpserver”) = “smtp.gmail.com”

    NewMail.Configuration.Fields.Item _
    (“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 25

    NewMail.Configuration.Fields.Item _
    (“http://schemas.microsoft.com/cdo/configuration/sendusing”) = 2

    NewMail.Configuration.Fields.Item _
    (“http://schemas.microsoft.com/cdo/configuration/sendusername”) = Sheets(“Mail Format”).Range(“F14″).Value ‘”*****”

    NewMail.Configuration.Fields.Item _
    (“http://schemas.microsoft.com/cdo/configuration/sendpassword”) = Sheets(“Mail Format”).Range(“L13″).Value ‘”****”

    NewMail.Configuration.Fields.Update

    With NewMail
    .Subject = Sheets(“Mail Format”).Range(“f14″).Value ‘”Test Mail”
    .From = Sheets(“Mail Format”).Range(“F13”).Value
    .To = Sheets(“Mail Format”).Range(“F14”).Value
    ‘.CC = Sheets(“Mail Format”).Range(“F15”).Value
    ‘.BCC = “”
    .TextBody = Sheets(“Mail Format”).Range(“B16″).Value ‘”Payment Proposal is created”
    End With

    NewMail.Send
    MsgBox (“Mail has been Sent”)

    Set NewMail = Nothing

    End Sub

    Reply
    • Israel Chavez

      change the port to 465, it worked to me

      Reply
  42. Mahi

    Error – run-time -2147220975 (80040211)’:
    The Message cannot be sent to the SMTP server. The transport error code was 0x80040217. The Server response was not available.

    Reply
    • david

      hi,
      I have the same error. Were you able to solve it?

      Reply
  43. Israel Chavez

    Hi everybody:
    This worked perfecty fine to me. The number of the port was the trouble, you have to change the email address and password in this example.

    Sub SendEmailUsingGmail()

    Dim NewMail As CDO.Message

    Set NewMail = New CDO.Message

    ‘Enable SSL Authentication
    NewMail.Configuration.Fields.Item _
    (“http://schemas.microsoft.com/cdo/configuration/smtpusessl”) = True
    ‘Make SMTP authentication Enabled=true (1)

    NewMail.Configuration.Fields.Item _
    (“http://schemas.microsoft.com/cdo/configuration/smtpauthenticate”) = 1

    ‘Set the SMTP server and port Details
    ‘To get these details you can get on Settings Page of your Gmail Account

    NewMail.Configuration.Fields.Item _
    (“http://schemas.microsoft.com/cdo/configuration/smtpserver”) = “smtp.gmail.com”

    NewMail.Configuration.Fields.Item _
    (“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 465

    NewMail.Configuration.Fields.Item _
    (“http://schemas.microsoft.com/cdo/configuration/sendusing”) = 2

    ‘Set your credentials of your Gmail Account

    NewMail.Configuration.Fields.Item _
    (“http://schemas.microsoft.com/cdo/configuration/sendusername”) = “mail_from”

    NewMail.Configuration.Fields.Item _
    (“http://schemas.microsoft.com/cdo/configuration/sendpassword”) = “password”

    ‘Update the configuration fields
    NewMail.Configuration.Fields.Update

    ‘Set All Email Properties

    With NewMail
    .Subject = “Test Mail from LearnExcelMacro.com”
    .From = “mail_from”
    .To = “mail_to”
    .CC = “”
    .BCC = “”
    .TextBody = “”
    End With

    NewMail.Send
    MsgBox (“Mail has been Sent”)

    ‘Set the NewMail Variable to Nothing
    Set NewMail = Nothing

    End Sub

    Reply
  44. Himanshu

    on first run only it is showing as user-defined type not available can you pls help

    Reply
    • Himanshu

      Sub SendEmailUsingGmail()

      Dim NewMail As CDO.Message

      Set NewMail = New CDO.Message

      ‘Enable SSL Authentication
      NewMail.Configuration.Fields.Item _
      (“http://schemas.microsoft.com/cdo/configuration/smtpusessl”) = True

      ‘Make SMTP authentication Enabled=true (1)

      NewMail.Configuration.Fields.Item _
      (“http://schemas.microsoft.com/cdo/configuration/smtpauthenticate”) = 1

      ‘Set the SMTP server and port Details
      ‘To get these details you can get on Settings Page of your Gmail Account

      NewMail.Configuration.Fields.Item _
      (“http://schemas.microsoft.com/cdo/configuration/smtpserver”) = “smtp.gmail.com”

      NewMail.Configuration.Fields.Item _
      (“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 465

      NewMail.Configuration.Fields.Item _
      (“http://schemas.microsoft.com/cdo/configuration/sendusing”) = 2

      ‘Set your credentials of your Gmail Account

      NewMail.Configuration.Fields.Item _
      (“http://schemas.microsoft.com/cdo/configuration/sendusername”) = “hmishra429@gmail.com”

      NewMail.Configuration.Fields.Item _
      (“http://schemas.microsoft.com/cdo/configuration/sendpassword”) = “******”

      ‘Update the configuration fields
      NewMail.Configuration.Fields.Update

      ‘Set All Email Properties

      With NewMail
      .Subject = “Progress report”
      .From = “himanshu.mishra@gmail.com”
      .To = “xxxx@gmail.com”
      .CC = “xxxx@gmail.com”
      .BCC = “”
      .HTMLBody = “Dear Sir,Please find attached progress report.”

      ‘ For multiple Attachment you can add below lines as many times

      .AddAttachment “\10.36.120.109Production Plant-1Combined Reports–Plant 1Oct-15Combined Project status-Final-Oct-15.xls”

      End With

      NewMail.Send
      MsgBox (“Mail has been Sent”)

      ‘Set the NewMail Variable to Nothing
      Set NewMail = Nothing

      End Sub

      Reply
  45. Robert Hofer

    Thank you very much
    it works like a charm

    (sorry for sending you a testmail)

    Reply
  46. Narayan

    Hi vish,

    i tried sending email from excel using code above bet getting below error

    Run Time Error ‘-2147220975 (80040211)’: Automation Error

    Can you please help me to fix this

    Reply
    • Milind

      Hi Sir,

      I am getting below error can you please help me in this?

      Run-time error ‘-2147220975(80040211)’:

      The message could not be sent to the SMTP server. The transport
      error code was 0x80040217. The server response was not available

      Reply
  47. zahi

    hello sir,
    i try to send message my excel vba
    but error message return to my account

    Sub SendEmailUsingGmail()

    Dim NewMail As CDO.Message

    Set NewMail = New CDO.Message

    ‘Enable SSL Authentication
    NewMail.Configuration.Fields.Item _
    (“http://schemas.microsoft.com/cdo/configuration/smtpusessl”) = True

    ‘Make SMTP authentication Enabled=true (1)

    NewMail.Configuration.Fields.Item _
    (“http://schemas.microsoft.com/cdo/configuration/smtpauthenticate”) = 1

    ‘Set the SMTP server and port Details
    ‘To get these details you can get on Settings Page of your Gmail Account

    NewMail.Configuration.Fields.Item _
    (“http://schemas.microsoft.com/cdo/configuration/smtpserver”) = “smtp.gmail.com”

    NewMail.Configuration.Fields.Item _
    (“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 25

    NewMail.Configuration.Fields.Item _
    (“http://schemas.microsoft.com/cdo/configuration/sendusing”) = 2

    ‘Set your credentials of your Gmail Account

    NewMail.Configuration.Fields.Item _
    (“http://schemas.microsoft.com/cdo/configuration/sendusername”) = “alfalakandco@gmail.com”

    NewMail.Configuration.Fields.Item _
    (“http://schemas.microsoft.com/cdo/configuration/sendpassword”) = “onlyforme”

    ‘Update the configuration fields
    NewMail.Configuration.Fields.Update

    ‘Set All Email Properties

    With NewMail
    .Subject = “Test Mail from LearnExcelMacro.com”
    .From = “alfalakandco@gmail.com”
    .To = “lansuzy@gmail.com”
    .CC = “”
    .BCC = “”
    .TextBody = “hello good afternoon”
    End With

    NewMail.Send
    MsgBox (“Mail has been Sent”)

    ‘Set the NewMail Variable to Nothing
    Set NewMail = Nothing

    End Sub

    ==================================================================================

    Delivery to the following recipient failed permanently:

    lansuzy@gmail.com

    Technical details of permanent failure:
    Message rejected. See https://support.google.com/mail/answer/69585 for more information.

    —– Original message —–

    X-Received: by 10.194.24.65 with SMTP id s1mr3367292wjf.137.1460464773229;
    Tue, 12 Apr 2016 05:39:33 -0700 (PDT)
    Return-Path:
    Received: from goodPC (WimaxUser3662-147.wateen.net. [110.86.72.148])
    by smtp.gmail.com with ESMTPSA id 202sm3491285wmw.5.2016.04.12.05.39.30
    (version=TLS1 cipher=ECDHE-RSA-AES128-SHA bits=128/128);
    Tue, 12 Apr 2016 05:39:32 -0700 (PDT)
    Thread-Topic: Test Mail from LearnExcelMacro.com
    thread-index: AdGUuFuSP5UPU9YYQjmI46rFEnuUfA==
    From:
    To:
    Cc:
    Bcc:
    Subject: Test Mail from LearnExcelMacro.com
    Date: Tue, 12 Apr 2016 17:39:30 +0500
    Message-ID:
    MIME-Version: 1.0
    Content-Type: text/plain
    Content-Transfer-Encoding: 7bit
    X-Mailer: Microsoft CDO for Windows 2000
    Content-Class: urn:content-classes:message
    Importance: normal
    Priority: normal
    X-MimeOLE: Produced By Microsoft MimeOLE V6.1.7601.17609

    hello good afternoon

    Reply
  48. Vinay Mehta

    Hi. Thanks for your classy advice on CDO.
    I have very serious issue while using CDO through VBS.
    When I’m trying to send email through 172.**.***.123 where smtp access is provided, I’m able to send email.
    But when same vbs file is placed on same *.123 server, While trying to run the file from any other location by sharing the file, I’m unable to send email.
    Is there any solution for same. As in corporate, I cannot provide smtp access to every user machine who is going to use the file.

    Reply
  49. dammsugare test

    I every time spent my half an hour to read this web site’s posts everyday along with a mug of coffee.

    Reply
  50. Craig

    Great post! Here’s a similar method for automating email notifications using Python: http://bit.ly/2b90i65

    Reply
  51. Ndindash

    I tried running this but get the following error
    Run-time error’-2147024893 (80070003)’:

    Automation error
    The system cannot find the path specified

    ***Here is my code****

    Sub SendEmailUsingGmail()

    Dim NewMail As CDO.Message

    Set NewMail = New CDO.Message

    ‘Enable SSL Authentication
    NewMail.Configuration.Fields.Item _
    (“http://schemas.microsoft.com/cdo/configuration/smtpusessl”) = True

    ‘Make SMTP authentication Enabled=true (1)

    NewMail.Configuration.Fields.Item _
    (“http://schemas.microsoft.com/cdo/configuration/smtpauthenticate”) = 1

    ‘Set the SMTP server and port Details
    ‘To get these details you can get on Settings Page of your Gmail Account

    NewMail.Configuration.Fields.Item _
    (“http://schemas.microsoft.com/cdo/configuration/smtpserver”) = “smtp.gmail.com”

    NewMail.Configuration.Fields.Item _
    (“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 25

    NewMail.Configuration.Fields.Item _
    (“http://schemas.microsoft.com/cdo/configuration/sendusing”) = 2

    ‘Set your credentials of your Gmail Account

    NewMail.Configuration.Fields.Item _
    (“http://schemas.microsoft.com/cdo/configuration/sendusername”) = “investigations@psspfund.co.za”

    NewMail.Configuration.Fields.Item _
    (“http://schemas.microsoft.com/cdo/configuration/sendpassword”) = “************”

    ‘Update the configuration fields
    NewMail.Configuration.Fields.Update

    ‘Set All Email Properties

    With NewMail
    .Subject = “Test Mail from LearnExcelMacro.com”
    .From = “investigations@psspfund.co.za”
    .To = “peter@viomedic.co.za;kelezibi@viomedic.co.za”
    .CC = “tshepi@gmail.com”
    .BCC = “”
    .TextBody = “”
    HTMLBody = “Write your complete HTML Page”

    ‘ For multiple Attachment you can add below lines as many times

    .AddAttachment “C:desktopinsurance services.doc”

    End With

    NewMail.Send
    MsgBox (“Mail has been Sent”)

    ‘Set the NewMail Variable to Nothing
    Set NewMail = Nothing

    End Sub

    Reply
  52. Abhijeet Amrite

    Hi,

    When i am running the macro its asking me the VBA project password.

    Request you to kindly Help vishwa.

    Reply
    • Vishwamitra Mishra

      Hi, to send an email, you do not need VBA password. Due to some error occurred, you are trying to debug the code and hence it was asking for the VBA password.

      Reply
      • Saif

        Hi ,i already read your posting. always good. BTW, can you please guide me how a macro can work in a web mail box inside citrix. thanks

        Reply
  53. Ketan

    Thanks BRO its working Superb 🙂
    initially was getting automation error but after changing my accounts setting i was able to send messages. Cheers 🙂

    Reply
    • Zach

      What settings did you have to change?

      Reply
  54. Milind

    How to send email from gmail using Excel VBA.
    Note : Using get elements by ID.

    Reply
  55. sudhakar

    hi i have tried with your code,code is working but i am getting error (run-time error 2147220975(80040211) the message could not be sent to the smtp server. the transport error code was 0x80040217.teh server response was not available).please respond me thanks

    Reply
  56. Elmar

    Is there any way of displaying the message before sending it via Gmail ??? If done through Outlook, Excel takes you to outlook email with attachment and you check/edit and just click “send”. I need the same but for Gmail.

    I know that .Display does not work for CDO, but maybe there is a way of getting around/past that ???

    Reply
  57. Rahul Mohan M

    Hi,
    Right now I am creating a logging software using macro in WPS software. So my requirement is during logging a mail should be send to the person who takes the tool/instrument about the log. The problem with me is that I don’t have outlook app, but have an outlook mail id. So my macro should do the following – The macro should open firefox browser, then go to a outlook mail address. Since I have already opened my outlook mail, the macro should not have to sign in. The next step of macro is to create a new mail and fill all the necessary things and send the mail. Finally the newly opened tab should close after sending this mail. Is this possible using VBA. If possible can any one give me the code.

    Thanks in advance,
    Rahul Mohan M

    Reply
    • Ranga

      Dear, I have same issue. Could you send me the same code if you have fixed ?
      rangaak2000@gmail.com
      thanks

      Reply
  58. Megan

    Hi, Thank you so much for this code, it works amazing.

    Is there a way to use VBA to compose an email in gmail (exactly as above) but instead of NewMail.Send, I want to save as draft in gmail?

    Thanks,
    Megan

    Reply
  59. James

    How do i apply the gmail code to a button to send an email when clicked.

    The email would need to contain the information on the sheet.
    basically i have a form i want people to fill out and simply click the button, sending an email
    with the information they just inserted.

    Thanks in advance,

    James

    Reply
  60. mrzzrm

    great work

    Reply
  61. EAge

    Hi guys!

    I did the macro and worked everything OK. Now I need to send the e-mails through gmail to 100 different e-mails with the same attachment. One to each changing only the e-mail, the title and a name on the body. I have the names in column A and e-mails in column B. I tried to use this but is only sending to the last one with multiple of the same attachment.
    Need some help.

    Reply
  62. mm.Rajesh

    Sir,
    am having excel macro file where file are get slipt and mail will go for respective ID’s am getting this type of error please help

    ” incorrect Credentials!! –The message could not be sent to the SMTP server .The transport error code was 0x80040217.the server response not availbe”

    Reply
  63. Eugene

    I’ve been trying the code to send emails from Excel using my Gmail account, but Google blocks it and sends an alert to my reference mail. How can I avoid this to send the emails??

    Reply
  64. Dhiraj Mahajan

    Hi,
    Code sending email from Gmail is showing an error that your internet connection is not available.
    So i checked my internet connection however there is no any problem with internet, its working properly.
    Can you help me.

    Reply
  65. Sadie

    Hello,

    I have copied your coding and adjusted to include the details needed for my specific needs. (i.e. account info; to; body; cc; subject etc). but it keeps giving me a “Object Required” Error.

    Can you please help!

    here is my coding…

    Sub SendEmailUsingGmail()
    Dim NewMail As Object
    Dim mailConfig As Object
    Dim fields As Variant

    Set NewMail = CreateObject(“CDO.Message”)
    Set mailConfig = CreateObject(“CDO.Configuration”)

    ‘load all default configurations
    mailConfig.Load -1

    Set fields = mailConfig.fields

    ‘Set All Email Properties

    With NewMail
    .To = “”
    .Subject = “”
    .CC = “”
    .textbody = “”
    End With

    (and then I have all the “With fields” info)

    then…
    NewMail.Configuration = mailConfig
    NewMail.Send
    MsgBox (“Mail has been Sent”)

    End Sub

    Reply
  66. shyam

    Hi,

    Can you please help me with the list of error number and description for bounced mails/undelivered mails

    Reply
  67. Abhi

    Is there a limit to the number of emails sent using VBA?
    we are unable to send more than 20 mails

    Reply
  68. Abhinav

    I am facing a problem. My email is landing in the spam box of the recipient. PLEASE PROVIDE A SOLUTION!!

    Reply
  69. azhar shaikh

    thank you so much.. this help me a lot..

    Reply
  70. Dolan

    Thank you, works but I have one problem.

    After sending the email, my Excel becomes “slow” and unresponsive. For example, if I change a cell, it wont update until I have changed tabs or scrolled away. It seems like it is actually updating, but not showing it on the screen.

    As I don’t get any errors, I have no clue where the conflict might be. Any suggestions?

    Reply
  71. Dolan

    I deleted: MsgBox (“Mail has been Sent”)
    And know it works!

    Reply
  72. Ranga

    I don’t have outlook app but have an outlook mail ID. I need to create a macro that open the default web browser, then go to a outlook mail address in order to send multiple mails for multiple recipients. Macro should create a new mail and fill all the necessary things and send the mail.

    Reply
  73. Ras

    Sir,
    I need to add date with subject. to send daily reports. how can it possible. i just tried code
    .Subject = “Daily Sales Report”,(Date, “dd-mmm-yyyy”)
    but error delivers

    Reply
  74. ashutosh

    Dear friends,

    I have a facing a problem when it’s run the vba code ……I am not a developer just like you …. collect some codes and trying to generate a payslip through my salary sheet……so pls solve the following.. error on the line “” Set fields = mailConfig.fields..”””

    Private Sub SendEmailUsingGmail_Click()

    On Error GoTo Err

    Dim NewMail As Object
    Dim mailConfig As Object
    Dim fields As Variant
    Dim msConfigURL As String

    Do While a <= 1

    EmpID = Sheet1.Range("B14").Offset(a, 0).Value

    Sheet2.Range("A8").Value = EmpID

    Filename = Sheet2.Range("B9").Value & "-" & Range("B10").Value & ".pdf"
    'Sheet2.PrintOut from:=1, To:=1

    Sheet2.ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "\" & Filename

    Set NewMail = CreateObject("CDO.Message")
    Set mailConfig = CreateObject("CDO.Configuration")

    ' load all default configurations
    mailConfig.Load -1

    Set fields = mailConfig.fields

    'Set All Email Properties

    With NewMail
    .Subject = Sheet2.Range("A7").Value
    .From = "zoom.compliance01@gmail.com"
    .To = Sheet2.Range("B57").Value
    .CC = ""
    .BCC = ""
    .TextBody = "Please find attachment"
    .Attachments.Add (ThisWorkbook.Path & "\" & Filename)
    End With

    msConfigURL = "http://schemas.microsoft.com/cdo/configuration&quot;

    With fields
    'Enable SSL Authentication
    .Item(msConfigURL & "/smtpusessl") = True

    'Make SMTP authentication Enabled=true (1)
    .Item(msConfigURL & "/smtpauthenticate") = 1

    'Set the SMTP server and port Details
    'To get these details you can get on Settings Page of your Gmail Account
    .Item(msConfigURL & "/smtpserver") = "smtp.gmail.com"
    .Item(msConfigURL & "/smtpserverport") = 465
    .Item(msConfigURL & "/sendusing") = 2

    'Set your credentials of your Gmail Account
    .Item(msConfigURL & "/sendusername") = "zoom.compliance01@gmail.com"
    .Item(msConfigURL & "/sendpassword") = "************"

    'Update the configuration fields
    .Update

    End With
    NewMail.Configuration = mailConfig
    NewMail.Send
    MsgBox ("Mail has been Sent")

    Exit_Err:

    Set NewMail = Nothing
    Set mailConfig = Nothing
    End

    Err:
    Select Case Err.Number

    Case -2147220973 'Could be because of Internet Connection
    MsgBox " Could be no Internet Connection !! — " & Err.Description

    Case -2147220975 'Incorrect credentials User ID or password
    MsgBox "Incorrect Credentials !! — " & Err.Description

    Case Else 'Rest other errors
    MsgBox "Error occured while sending the email !! — " & Err.Description
    End Select

    Resume Exit_Err

    Loop

    Set NewMail = Nothing
    Set mailConfig = Nothing

    End Sub

    Reply
  75. Srikanth k

    When i tried running Excel vb script to send an email to outlook using SMTP, but I’m getting the Error, “Run -time error ‘-2147220973(80040213)’: The transport failed to connect to the server.As you have mentioned i have checked my port number and it seems correct, actually i need to send an email from my work account. The below are the details i’m using to send an email.
    .Item(msConfigURL & “/smtpserver”) = “smtp.bcbsnc.com”
    .Item(msConfigURL & “/smtpserverport”) = 25

    I have aslo cross checked the details using command prompt and using the command (telnet smtp.bcbsnc.com 25) and was able to send a test message, but using excel VBA i could not send. Could you please check and provide your inputs/suggestions to solve this issue. I guess, in outlook macros were disabled and due to this i’m facing error or not. Can you check on this also.

    Reply
  76. Jacob

    Copy and pasted code, worked first time! Awesome, thank you very much!

    Reply
  77. Aqeel

    I used this code to send email through GMAIL ..it worked perfectly but Arabic texts don’t show in emails on the other side…it only shows symbols not Arabic letters…it seems its an encoding problem, i guess…Can you help me with that…

    Reply
  78. NAGARAJ PRAKASH

    Hi

    I am trying to use the send email from Gmail VBA code and I am getting error “incorrect credentials” in-spite of entering correct email id and password. The transport error code was 0x80040217. Will you please help me to correct the same.

    Reply
  79. Shivanand

    i WOULD LIKE TO SEND EXCEL DATA BY GMAIL. PLEASE HELP ME ON THIS WHERE DO I HAVE TO CHANGE

    Sub Macro1()

    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets(“Sheet1”)

    ‘create a CDO object
    Dim NewMail As Object
    Dim mailConfig As Object
    Dim OA As Object
    Dim msg As Object

    ‘late binding
    Set NewMail = CreateObject(“CDO.Message”)
    Set mailConfig = CreateObject(“CDO.Configuration”)

    Set OA = CreateObject(“gmail.Application”)
    Dim i As Integer
    Dim j As Integer
    Dim last_row As Integer

    last_row = Application.WorksheetFunction.CountA(sh.Range(“M:M”))

    For i = 2 To last_row
    Set msg = OA.createitem(0)
    msg.To = sh.Range(“N” & i).Value
    msg.CC = sh.Range(“O” & i).Value & ” ; ” & sh.Range(“P” & i).Value & ” ; ” & sh.Range(“Q” & i).Value
    msg.Subject = “Order Dispatch Details”
    msg.htmlbody = “Dear Concern, Please find below order dispatch statustable, th, td { border: 1px solid black; border-collapse: collapse;}” & _
    ” & sh.Range(“A1”).Value & “” & sh.Range(“B1”).Value & “” & sh.Range(“C1”).Value & “” & sh.Range(“D1”).Value & “” & sh.Range(“E1”).Value & “” & sh.Range(“F1”).Value & “” & sh.Range(“G1”).Value & “” & sh.Range(“H1”).Value & “” & sh.Range(“I1”).Value & “” & sh.Range(“J1”).Value & “” & sh.Range(“K1”).Value & “” & sh.Range(“L1”).Value & “” & sh.Range(“M1”).Value & _
    “” & sh.Range(“A” & i).Value & “” & sh.Range(“B” & i).Value & “” & sh.Range(“C” & i).Value & “” & sh.Range(“D” & i).Value & “” & sh.Range(“E” & i).Value & “” & sh.Range(“F” & i).Value & “” & sh.Range(“G” & i).Value & “” & sh.Range(“H” & i).Value & “” & sh.Range(“I” & i).Value & “” & sh.Range(“J” & i).Value & “” & sh.Range(“K” & i).Value & “” & sh.Range(“L” & i).Value & “” & sh.Range(“M” & i).Value & “”
    For j = (i + 1) To last_row
    If sh.Range(“N” & i).Value = sh.Range(“N” & j).Value Then

    msg.htmlbody = msg.htmlbody & “” & sh.Range(“A” & j).Value & “” & sh.Range(“B” & j).Value & “” & sh.Range(“C” & j).Value & “” & sh.Range(“D” & j).Value & “” & sh.Range(“E” & j).Value & “” & sh.Range(“F” & j).Value & “” & sh.Range(“G” & j).Value & “” & sh.Range(“H” & j).Value & “” & sh.Range(“I” & j).Value & “” & sh.Range(“J” & j).Value & “” & sh.Range(“K” & j).Value & “” & sh.Range(“L” & j).Value & “” & sh.Range(“M” & j).Value & “”
    i = j
    End If
    Next j
    msConfigURL = “http://schemas.microsoft.com/cdo/configuration”

    msg.htmlbody = msg.htmlbody & “Regards,Shilpa Nigam, Mobile no: +91-7290019439”

    msg.Send

    Next i

    MsgBox “Sent”

    End Sub

    Reply
  80. Shiva

    I made the changes in the coding but unable to send mail through same

    Sub send_email_via_Gmail()

    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets(“Sheet1”)

    Dim myMail As CDO.Message
    Set myMail = New CDO.Message

    Dim i As Integer
    Dim j As Integer
    Dim last_row As Integer

    myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpusessl”) = True
    myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smptauthenticate”) = 1

    myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserver”) = “smtp.gmail.com”

    myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 25

    myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendusing”) = 2

    myMail.Configuration.Fields.Update

    last_row = Application.WorksheetFunction.CountA(sh.Range(“X:X”))

    For i = 2 To last_row
    Set myMail = OA.createitem(0)
    myMail.To = sh.Range(“Y” & i).Value
    myMail.CC = sh.Range(“Z” & i).Value & ” ; ” & sh.Range(“AA” & i).Value & ” ; ” & sh.Range(“AB” & i).Value
    myMail.Subject = “Order Dispatch Details”
    myMail.htmlbody = “Dear Concern, Please find below dispatch statustable, th, td { border: 1px solid black; border-collapse: collapse;}” & _
    ” & sh.Range(“A1”).Value & “” & sh.Range(“B1”).Value & “” & sh.Range(“C1”).Value & “” & sh.Range(“D1”).Value & “” & sh.Range(“E1”).Value & “” & sh.Range(“F1”).Value & “” & sh.Range(“G1”).Value & “” & sh.Range(“H1”).Value & “” & sh.Range(“I1”).Value & “” & sh.Range(“J1”).Value & “” & sh.Range(“K1”).Value & “” & sh.Range(“L1”).Value & “” & sh.Range(“M1”).Value & “” & sh.Range(“N1”).Value & “” & sh.Range(“O1”).Value & “” & sh.Range(“P1”).Value & “” & sh.Range(“Q1”).Value & “” & sh.Range(“R1”).Value & “” & sh.Range(“S1”).Value & “” & sh.Range(“T1”).Value & “” & sh.Range(“U1”).Value & “” & sh.Range(“V1”).Value & _
    ” & sh.Range(“W1”).Value & “” & sh.Range(“X1”).Value & _
    “” & sh.Range(“A” & i).Value & “” & sh.Range(“B” & i).Value & “” & sh.Range(“C” & i).Value & “” & sh.Range(“D” & i).Value & “” & sh.Range(“E” & i).Value & “” & sh.Range(“F” & i).Value & “” & sh.Range(“G” & i).Value & “” & sh.Range(“H” & i).Value & “” & sh.Range(“I” & i).Value & “” & sh.Range(“J” & i).Value & “” & sh.Range(“K” & i).Value & “” & sh.Range(“L” & i).Value & “” & sh.Range(“M” & i).Value & “” & sh.Range(“N” & i).Value & “” & sh.Range(“O” & i).Value & “” & sh.Range(“P” & i).Value & “” & sh.Range(“Q” & i).Value & “” & sh.Range(“R” & i).Value & “” & sh.Range(“S” & i).Value & “” & sh.Range(“T” & i).Value & “” & sh.Range(“U” & i).Value & “” & sh.Range(“V” & i).Value & “” & sh.Range(“W” & i).Value & “” & sh.Range(“X” & i).Value & “”
    For j = (i + 1) To last_row
    If sh.Range(“Y” & i).Value = sh.Range(“Y” & j).Value Then

    myMail.htmlbody = myMail.htmlbody & “” & sh.Range(“A” & j).Value & “” & sh.Range(“B” & j).Value & “” & sh.Range(“C” & j).Value & “” & sh.Range(“D” & j).Value & “” & sh.Range(“E” & j).Value & “” & sh.Range(“F” & j).Value & “” & sh.Range(“G” & j).Value & “” & sh.Range(“H” & j).Value & “” & sh.Range(“I” & j).Value & “” & sh.Range(“J” & j).Value & “” & sh.Range(“K” & j).Value & “” & sh.Range(“L” & j).Value & “” & sh.Range(“M” & j).Value & “” & sh.Range(“N” & j).Value & “” & sh.Range(“O” & j).Value & “” & sh.Range(“P” & j).Value & “” & sh.Range(“Q” & j).Value & “” & sh.Range(“R” & j).Value & “” & sh.Range(“S” & j).Value & “” & sh.Range(“T” & j).Value & “” & sh.Range(“U” & j).Value & “” & sh.Range(“V” & j).Value & “” & sh.Range(“W” & j).Value & “” & sh.Range(“X” & j).Value & “”
    i = j
    End If
    Next j

    myMail.htmlbody = myMail.htmlbody & “Regards,Vikas Karn, Mobile no: +91-83 7795 2790”

    myMail.Send

    Next i

    MsgBox “Sent”

    End Sub

    Reply
  81. naim said

    How to send Email from Gmail in Excel Macro with multiple attachment from liste

    Reply
    • Vishwamitra Mishra

      Hi You can simply add a for loop to add as many attachments as you want from a list

      
      With NewMail
        .Subject = "Test Mail from LearnExcelMacro.com"
        .From = "vishwamitra01@yahoo.com"
        .To = "vishwamitra02@gmail.com;info@learnexcelmacro.com"
        .CC = "vishwamitra01@gmail.com"
        .BCC = ""
        .HTMLBody = "Write your complete HTML Page"
      
        Dim attachments as Variant
        attachments = ("C:\Macro-help-1.xls", "C:\Macro-help-2.xls", "C:\Macro-help-3.xls")
        for each attachment in attachments
          .AddAttachment attachment
        next
      End With
      
      Reply

Trackbacks/Pingbacks

  1. Learn Excel Macro Download : Excel Macro Application to Send Multiple Emails - [...] How to send Email from Excel ? How to send Email from Gmail in Excel Macro ? How to…
  2. sending mail using CDO object in ACCESS - Technology - […] http://learnexcelmacro.com/wp/2011/12/how-to-send-an-email-using-excel-macro-from-gmail-or-yahoo… […]
  3. Welcome to LearnExcelMacro.com Download : Excel Macro Application to Send Multiple Emails - […] by changing the port number to 587 in the code for Gmail. That worked for him. Thanks Charles. See…

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest