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.

Hello. magnificent job. I did not expect this. This is a excellent story. Thanks!

Hey! This is kind of off topic but I need some help from an established blog. Is it very difficult to set up your own blog? I’m not very techincal but I can figure things out pretty fast. I’m thinking about making my own but I’m not sure where to start. Do you have any tips or suggestions? Thank you

I just want to mention I’m very new to weblog and seriously savored this website. Most likely I’m going to bookmark your site . You really come with impressive article content. Thanks a bunch for revealing your blog.

Yes you can do it easily and effectively with our excel to outlook converter which easily create Outlook distribution list from VBA excel files.

Very excellent info can be found on weblog .