Creating Distribution List with VBA Excel

In the last post in Excel Category I talked about text functions and I posted a situation to create a distribution list from list of email ids in a column. There is no formula in excel which you can use to create a distribution list. But good news is that you can create one using VBA. Below is the code which would give a formula in excel to use for distribution list.

There are many ways to get this function written but below is one of the easiest way to understand the code. Paste the below code in any vba module and use it as a normal formula in excel.


Function listcat(src_range As Range, diff As String) As String
‘src_range is range variable to allow range selection and diff is variable to store ‘differentiators
Dim final As String
‘variable called final to store the distribution list
Dim c As Range
For Each c In src_range
final = final & diff & c.Value
Next c
‘finally taking the clean distribution list to listcat
listcat = Right(final, Len(final) – 1)
End Function


That is it go back to excel and use this like a formula in excel, for ex: =listcat(B2:B9,”;”) where B2:B9 is the list of email ids which needs to be added and “;” is the separator.

Attached is the file for your reference with the code and formula hope you will find it useful.

Note: These days you can directly copy the entire values in the column and paste in outlook/other mail clients which will automatically create a list. But if in situations it doesn’t this is the best way.