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.

ADVERTISEMENTS

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.

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.

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.
  4. 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.
  5. Add a Table with Title to the InfoPath form.
  6. 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.
  7. Add a Text Box control to the table and name it emailAddresses.
  8. Modify the table and controls to resemble the following figure:

    Figure 2. The InfoPath form in design mode.
  9. Go to Tools > Options..., click on the Design tab, and make sure that the Default programming language is set to VBScript.
  10. 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.
  11. The form should resemble the following figure when it is opened:

    Figure 3. The resulting InfoPath form.
 
 Subscribe for updates via RSS or Email

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.

InfoPath 2013 Cookbook: 121 Codeless Recipes for Beginners
InfoPath 2013 Cookbook 2: 121 Codeless Recipes for SharePoint 2013
InfoPath 2010 Cookbook: 101 Codeless Recipes for Beginners
InfoPath 2010 Cookbook 2: 101 Codeless Recipes for SharePoint 2010
InfoPath 2010 Cookbook 3: 101 Code Recipes for C# Developers
InfoPath 2010 Cookbook 5: Integrating InfoPath with Excel and Excel Services


Related InfoPath Articles:

ADVERTISEMENTS

InfoPath 2013 Cookbook: 121 Codeless Recipes for Beginners

InfoPath 2013 Cookbook 2: 121 Codeless Recipes for SharePoint 2013

InfoPath 2010 Cookbook: 101 Codeless Recipes for Beginners

InfoPath 2010 Cookbook 2: 101 Codeless Recipes for SharePoint 2010

InfoPath 2010 Cookbook 3: 101 Code Recipes for C# Developers

InfoPath 2010 Cookbook 4: 101 Code Recipes for VB Developers

InfoPath 2010 Cookbook 5: Integrating InfoPath with Excel and Excel Services