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.
Distribution List - Create Email List using ExcelVisual Basic1234567891011121314151617181920212223242526272829303132333435Option ExplicitPublic Sub Add_Semicolon_To_Email_Addresses_List()Dim iRow As Double, iCol As Double, Ws As WorksheetDim Email_List As String, eFile_Path As String, eFile'Initialize variablesiRow = 2iCol = 1Email_List = ""eFile = FreeFileSet Ws = ThisWorkbook.Sheets("Sheet1")'Get Each Email Address and Add a SemicolonWhile Ws.Cells(iRow, iCol) <> ""Email_List = Email_List & Ws.Cells(iRow, iCol) & ";"iRow = iRow + 1WendIf Email_List <> "" Then'Trim the Last SemicolonEmail_List = VBA.Mid(Email_List, 1, VBA.Len(Email_List) - 1)'Write Semicolon separated Email Address List to Output File'Create Email List from ExceleFile_Path = Ws.Cells(1, 3)If eFile_Path = "" Then eFile_Path = ThisWorkbook.Path & "\Email_Distribution_List.txt"Open eFile_Path For Output As eFilePrint #eFile, Email_ListClose #eFileMsgBox "Email List Added with Semicolon in this path " & eFile_PathExit SubEnd IfMsgBox "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.
Previous Post: Function in VBA to Check if string contains only Letters
Next Post: How To Open VCF File in Notepad?
VBA to Create Email List using Excel – Distribution List