none
excel and E-mail RRS feed

  • Question

  • i have an excel sheet containing about 100000 e-mail addresses at the same rows a1 a2 a3 a4 a5 ...etc

    how to send same message to all addresses from one mail account by one button click

    my excel file is .xlsx file

    any ideas



    Friday, March 2, 2018 11:28 PM

Answers

  • SMTP

    Two routines here, one is for retrieving all of the e-mail addresses from the Excel Workbook and the other for sending the e-mail. You will need to find out what your SMTP server requirements are. I used GMAIL as an example and had to turned off a security setting which blocks sending e-mail from an unknown app.

    You should be able to combine the two routines to send one or more e-mails (but if you are spamming it might not work):

        Sub ReadCol1FromExcelWorksheet()
    
            Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                             "Data Source=C:\Users\...\Documents\Database\Excel\EmailAddress.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=No"""
    
            Dim excelConnection As New System.Data.OleDb.OleDbConnection(connectionString)
            excelConnection.Open()
    
            Dim excelCommand As System.Data.OleDb.OleDbCommand = excelConnection.CreateCommand()
    
            excelCommand.CommandText = "SELECT * FROM [Address$]"
    
            Dim excelReader As OleDbDataReader
            excelReader = excelCommand.ExecuteReader()
    
            While (excelReader.Read())
                'If no header (see connection string) F1 is the first column, F2 is the second, etc.
                Console.WriteLine(excelReader.Item("F1").ToString)
            End While
    
            excelReader.Close()
            excelConnection.Close()
    
        End Sub
    
        Public Sub SendTheMail()
    
            Dim smtpServer As New Net.Mail.SmtpClient()
            smtpServer.UseDefaultCredentials = True
            smtpServer.Port = 587
            smtpServer.Host = "smtp.gmail.com"
            smtpServer.EnableSsl = True
            smtpServer.DeliveryMethod = SmtpDeliveryMethod.Network
            smtpServer.Credentials = New System.Net.NetworkCredential("email@gmail.com", "password")
            smtpServer.Timeout = 20000
            Dim mail As New System.Net.Mail.MailMessage()
            mail.From = New MailAddress("email@gmail.com")
            mail.To.Add("recipient@name.com")
            mail.Subject = "Test Mail"
            mail.Body = "This is for testing SMTP mail from GMAIL"
            smtpServer.Send(mail)
            MsgBox("mail sent")
    
        End Sub


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Saturday, March 3, 2018 3:38 PM

All replies

  • First issue is will your email provider allow this as most will not as it appears to be a spam generator. You can use SpreadSheetLight (as I introduced you too already), Excel automation or OleDb. You would be better off using the text file the email addresses came from as this can be done with IO.File.ReadAllLines then iterate the lines in the file.

    Sending email sample operations can be found here.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, March 3, 2018 12:03 AM
    Moderator
  • Which e-mail client (Outlook?) are you using, or are you using SMTP?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Saturday, March 3, 2018 6:48 AM
  • SMTP
    Saturday, March 3, 2018 1:41 PM
  • SMTP

    Two routines here, one is for retrieving all of the e-mail addresses from the Excel Workbook and the other for sending the e-mail. You will need to find out what your SMTP server requirements are. I used GMAIL as an example and had to turned off a security setting which blocks sending e-mail from an unknown app.

    You should be able to combine the two routines to send one or more e-mails (but if you are spamming it might not work):

        Sub ReadCol1FromExcelWorksheet()
    
            Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                             "Data Source=C:\Users\...\Documents\Database\Excel\EmailAddress.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=No"""
    
            Dim excelConnection As New System.Data.OleDb.OleDbConnection(connectionString)
            excelConnection.Open()
    
            Dim excelCommand As System.Data.OleDb.OleDbCommand = excelConnection.CreateCommand()
    
            excelCommand.CommandText = "SELECT * FROM [Address$]"
    
            Dim excelReader As OleDbDataReader
            excelReader = excelCommand.ExecuteReader()
    
            While (excelReader.Read())
                'If no header (see connection string) F1 is the first column, F2 is the second, etc.
                Console.WriteLine(excelReader.Item("F1").ToString)
            End While
    
            excelReader.Close()
            excelConnection.Close()
    
        End Sub
    
        Public Sub SendTheMail()
    
            Dim smtpServer As New Net.Mail.SmtpClient()
            smtpServer.UseDefaultCredentials = True
            smtpServer.Port = 587
            smtpServer.Host = "smtp.gmail.com"
            smtpServer.EnableSsl = True
            smtpServer.DeliveryMethod = SmtpDeliveryMethod.Network
            smtpServer.Credentials = New System.Net.NetworkCredential("email@gmail.com", "password")
            smtpServer.Timeout = 20000
            Dim mail As New System.Net.Mail.MailMessage()
            mail.From = New MailAddress("email@gmail.com")
            mail.To.Add("recipient@name.com")
            mail.Subject = "Test Mail"
            mail.Body = "This is for testing SMTP mail from GMAIL"
            smtpServer.Send(mail)
            MsgBox("mail sent")
    
        End Sub


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Saturday, March 3, 2018 3:38 PM