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.

Problem

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 someone1@somewhere.com;someone2@somewhere.com;someone3@somewhere.com.

Solution

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.

Discussion

You can accomplish this functionality as follows:

1. You have a SharePoint Contacts list with the columns LastName, FirstName, and E-mail Address.
   
   Figure 1. SharePoint Contacts list.
   
2. Create a New Blank Form in InfoPath.
3. 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:
<dfs:myFields>
<dfs:dataFields>
<dsf:Contacts Last_Name="Jones" First_Name="Andruw"
E-mail_Address="adruw.jones@atlantabraves.com"/>
<dsf:Contacts Last_Name="Jones" First_Name="Chipper"
E-mail_Address="chipper.jones@atlantabraves.com"/>
<dsf:Contacts Last_Name="Renteria" First_Name="Edgar"
E-mail_Address="edgar.renteria@atlantabraves.com"/>
<dsf:Contacts Last_Name="Smoltz" First_Name="John"
E-mail_Address="john.smoltz@atlantabraves.com"/>
</dfs:dataFields>
</dfs:myFields>
Note: Namespace declarations have been left out to increase clarity.
4. Add a Table with Title to the InfoPath form.
5. 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.
6. Add a Text Box control to the table and name it emailAddresses.
7. Modify the table and controls to resemble the following figure:
   
   Figure 2. The InfoPath form in design mode.
8. Go to Tools > Options..., click on the Design tab, and make sure that the Default programming language is set to VBScript.
9. 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:
   Dim objContactsDOM
Dim objContacts
Dim emailAddress
Dim emailAddresses

' 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"" " & _
"xmlns:dsf=""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
Else
emailAddresses = emailAddresses & ";" & emailAddress
End If

Next

' Set the value of the emailAddresses field to the concatenated string
' of email addresses
XDocument.DOM.selectSingleNode("//my:emailAddresses").text = emailAddresses
   This code ensures that whenever the form is loaded, the data in the secondary data source will be used to generate a concatenated string of email addresses from the SharePoint Contacts list.
10. The form should resemble the following figure when it is opened:
    
    Figure 3. The resulting InfoPath form.

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