Generate a concatenated string of email addresses from a list using VBScript
Use VBScript to extract email addresses from a SharePoint list of contacts and generate a concatenated string of email addresses separated by a semi-colon.
You have a SharePoint list that stores the first name, last name, and email address of contacts. You would like to use the email addresses to generate a concatenated string of email addresses separated by a semi-colon like in firstname.lastname@example.org; email@example.com; firstname.lastname@example.org.
Retrieve the data connection to the SharePoint list, extract all the contacts, and use their email addresses to generate a concatenated string of email addresses.
If you are using a repeating table or a people picker in a repeating table, and do not want to write code, you can also use the eval() and concat() functions to concatenate data from a repeating table.
You can accomplish this functionality as follows:
- You have a SharePoint Contacts list with the columns LastName, FirstName,
and E-mail Address.
Figure 1. SharePoint Contacts list.
- Create a New Blank Form in InfoPath.
- Create a Data Connection to the SharePoint list, select the LastName, FirstName, and Email-Address to be included in the data connection, and call the data connection Contacts. The XML data for the SharePoint list used in this solution resembles the following:
- Add a Table with Title to the InfoPath form.
- Add a List Box control to the table and bind it to the Data Connection for the SharePoint list. Use the LastName field as the Value as well as the Display name.
- Add a Text Box control to the table and name it emailAddresses.
- Modify the table and controls to resemble the following figure:
Figure 2. The InfoPath form in design mode.
- Go to Tools > Options..., click on the Design tab, and make sure that the Default programming language is set to VBScript.
- Go to Tools > Programming > On Load Event... to add an OnLoad
event handler to the form. Add the following code to the OnLoad event handler:
' Retrieve the DOM for the secondary data source connected
' to the SharePoint Contacts list
Set objContactsDOM = XDocument.GetDOM("Contacts")
' Set the namespaces to be used when retrieving XML nodes
objContactsDOM.setProperty "SelectionNamespaces", _
"xmlns:dfs=""http://schemas.microsoft.com/office/infopath/2003/dataFormSolution"" " & _
' Retrieve all the Contacts nodes
Set objContacts = objContactsDOM.selectNodes("/dfs:myFields/dfs:dataFields/dsf:Contacts")
' Loop through all the contacts
For i = 0 To objContacts.length - 1
' Retrieve email address for contact
emailAddress = objContacts(i).selectSingleNode("@E-mail_Address").text
' Generate concatenated string of email addresses
If (emailAddresses = "") Then
emailAddresses = emailAddress
emailAddresses = emailAddresses & ";" & emailAddress
' Set the value of the emailAddresses field to the concatenated string
' of email addresses
XDocument.DOM.selectSingleNode("//my:emailAddresses").text = emailAddresses
- The form should resemble the following figure when it is opened:
Figure 3. The resulting InfoPath form.
<dsf:Contacts Last_Name="Jones" First_Name="Andruw"
<dsf:Contacts Last_Name="Jones" First_Name="Chipper"
<dsf:Contacts Last_Name="Renteria" First_Name="Edgar"
<dsf:Contacts Last_Name="Smoltz" First_Name="John"
Copyright: This article may not be used on web sites (whether personal or otherwise), copied, disseminated, altered, printed, published, broadcasted, or reproduced in any way without an expressed written consent of S.Y.M. Wong-A-Ton. The techniques demonstrated in this article may be used within any Microsoft InfoPath project. This article is provided without any warranties. Copyright for this article is non-transferrable and remains with the author, S.Y.M. Wong-A-Ton.
Related InfoPath Articles:
- How to add a data connection to a SharePoint list or library in InfoPath
- How to loop through items in a repeating table in InfoPath 2007
- People picker data merging in InfoPath 2013
- Extract emails from people picker in InfoPath 2010
- Submit InfoPath form and send SharePoint email with link