none
help to send multiple emails using VBA in excel. RRS feed

  • Question

  • Hello, I am using the following script in VBA macro to send multiple email using my gmail. However the macro is sending email to only 1st recipient. Can anyone please suggest a work around of this?

    Function fnSendEmail()
     
    'Create an object of CDO type
    Set myMail = CreateObject("CDO.Message")
    'Enable SSL Authentication
    myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
    'Enable basic smtp authentication
    myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
    'Specify SMTP server and port
    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
    'Specify user id and password
    myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "futurextech2016@gmail.com"
    myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "********"
    'Update the configuration fields
    myMail.Configuration.Fields.Update
     
    'Specify email properties
            myMail.Subject = "Sending Email from QTP"
            myMail.From = "futurextech2016@gmail.com"
            myMail.To = Sheets("Sheet1").Range("A1:A10").Value
            myMail.CC = ""
            myMail.BCC = ""
            myMail.TextBody = "This is the Text Body"
    'Send mail
    myMail.Send
    Set myMail = Nothing
     
    End Function

    Friday, August 26, 2016 11:38 AM

Answers

  • myMail.To = Sheets("Sheet1").Range("A1:A10").Value

    To

    myMail.To = join(Application.transpose(Sheets("Sheet1").range("A1:A4").Value),",")

    May be it will solve. I guess the "To" takes a string as argument. But Range.Value will give a two dimensional array if more than one cell used. The above only converts two dimensional array to a string.

    But frankly I have no idea on CDO.


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Saturday, August 27, 2016 6:52 PM
    Answerer

All replies

  • myMail.To = Sheets("Sheet1").Range("A1:A10").Value

    To

    myMail.To = join(Application.transpose(Sheets("Sheet1").range("A1:A4").Value),",")

    May be it will solve. I guess the "To" takes a string as argument. But Range.Value will give a two dimensional array if more than one cell used. The above only converts two dimensional array to a string.

    But frankly I have no idea on CDO.


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Saturday, August 27, 2016 6:52 PM
    Answerer
  • Set a breakpoint at myMail.CC = "" (F9). Run the code till it stops on the breakpoint. In the Immediate Window (Ctrl-G), type

    ? myMalil.To

    (? = Print/display)

    Is it multiple emails separated by comma or semicolon?


    Best regards, George



    Sunday, August 28, 2016 1:16 PM