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.