VBA to Create Email List using Excel – Distribution List

VBA to Add Semicolon to Email Addresses in Excel

If you have Email Ids in Excel, then create distribution list using this app or VBA code.

We come across situations when we have to send any email to a list of people. Usually these mails are about promotional offers or news letter to a large audience. In such cases, we will first collect the email addresses of each person in Excel spreadsheet. It is always easy to manage the data in Excel than any other app.

Once we are done with collecting Email Ids, then we can create email list & send email to this huge list. We will either need to use the app in this page which can send personalized email to each Email id in Worksheet or Use code in this page to convert the Emails Ids to distribution list.

VBA to Create Distribution List from Excel

Once the distribution list is created, then you can copy the output and just paste it in To, Cc or Bcc field. Then send email to everyone in the list. Use the below code to do such conversion.

Option Explicit
Public Sub Add_Semicolon_To_Email_Addresses_List()
    Dim iRow As Double, iCol As Double, Ws As Worksheet
    Dim Email_List As String, eFile_Path As String, eFile
    
    'Initialize variables
    iRow = 2
    iCol = 1
    Email_List = ""
    eFile = FreeFile
    Set Ws = ThisWorkbook.Sheets("Sheet1")
    
    'Get Each Email Address and Add a Semicolon
    While Ws.Cells(iRow, iCol) <> ""
        Email_List = Email_List & Ws.Cells(iRow, iCol) & ";"
        iRow = iRow + 1
    Wend
    
    If Email_List <> "" Then
        'Trim the Last Semicolon
        Email_List = VBA.Mid(Email_List, 1, VBA.Len(Email_List) - 1)
        
        'Write Semicolon separated Email Address List to Output File
        'Create Email List from Excel
        eFile_Path = Ws.Cells(1, 3)
        If eFile_Path = "" Then eFile_Path = ThisWorkbook.Path & "\Email_Distribution_List.txt"
        Open eFile_Path For Output As eFile
        Print #eFile, Email_List
        Close #eFile
        MsgBox "Email List Added with Semicolon in this path " & eFile_Path
        Exit Sub
    End If
    MsgBox "No Email Ids to Covert to Distribution List"
    
End Sub

To execute this code, open the spread sheet having the Email Addresses list. Press Alt + F11 to view VB Editor. Copy the above code & paste it in here. Then Press F5 to execute this code.

Once conversion is successful, the code will give a message along with the path for the output file.

Convert Excel Email Ids to Distribution List

Open the output file that has all the email addresses delimited with a semicolon. This can be used as such to create email. Just copy the whole content of this file, paste it in the To, Cc or Bcc field of your email.

Now, you are ready to compose a email blast or a newsletter email that can be send to the whole group of people from your Excel email distribution list. You don’t have to copy each email id from Excel. The process is now automated using our simple macro.