Contacts by ListIds is returning results too many results.

SOLVED
Go to solution
Member

Contacts by ListIds is returning results too many results.

Using VBA to run loop and get XML files of contacts within specific contact lists following this procedure:

 

  1. Call https://api.constantcontact.com/ws/customers/NCJTCemail/contacts?listid={listid}&api_key={api key}
  2. Create XML file with results of this get
  3. If a next URL exists, then loop and repeat until there is no next URL

The list I am pulling has only 3 contacts (verified when running this in the Test API functionality, JSON file has only 3 entries). Therefore, this should run once, and quit. However, the results file has more than 3 contacts, and none of them are actually on the list. The process runs, and it does not quit.

 

I've attempted this with other lists as well, similar results (ex. list with 500 contacts should produce 10 files of 50 contacts, instead results in 100's of files).

 

What is going on?

  

Sanitized Code below:

strPostURL = "https://api.constantcontact.com/ws/customers/NCJTCemail/contacts?listid=[list id]&api_key=[api key]"

 

Dim objrequest As New MSXML2.XMLHTTP60

 

With objrequest
.Open "Get", strPostURL, False
.setRequestHeader "Authorization", "Bearer " & {access token}
.send
End With

strpostresponse = objrequest.responseText

Dim lngFreeFile As Long
lngFreeFile = FreeFile

Open "C:\EmailAnalytics\Lists\" & ListName & "_" & intFileCount & ".xml" For Output As #lngFreeFile
Print #lngFreeFile, strpostresponse
Close #lngFreeFile

Dim splitArray
splitArray = Split(strpostresponse, """")

Dim lineNum As Integer
lineNum = 11
Debug.Print splitArray(lineNum)
Debug.Print splitArray(lineNum + 2)

If splitArray(lineNum + 2) = "next" Then
strNext = splitArray(lineNum)
Else
strNext = ""
End If

 

 

1 ACCEPTED SOLUTION

Ultimately Jimmy recommended I switch to v2 of the API. This produced the file in JSON, and I was able to pull the information I needed using the code below.

 

This code is dependent on VBA-JSON (https://github.com/VBA-tools/VBA-JSON) and the reference Microsoft XML, v6.0

 

 

Sub GetSelfIdentified()

Dim strNext, strCode, strList, strListName As String
Dim LR As Long

Range("A2").Select

ActiveWorkbook.Sheets("ContactLists").Activate

Dim intPageCount As Long

For rr = 3 To 16  'rows where the list IDs are located
    strNext = "start"
    intPageCount = 1
    Do While strNext <> ""
        If intPageCount = 1 Then
            Cells(rr, 1).Activate
            strListName = ActiveCell.Value
            Cells(rr, 2).Activate
            strList = "/v2/lists/" & ActiveCell.Value & "/contacts?limit=500&api_key={API KEY}"
        Else
            strList = strNext & "&api_key={API KEY}"
        End If
        
        
        LR = Range("H65000").End(xlUp).Row  'find the bottom row of the column where the email addresses will go
' this could be skipped if the addresses get put into a text file
        
        
    strPostURL = "https://api.constantcontact.com" & strList

    Dim objrequest As New MSXML2.XMLHTTP60
    
        With objrequest
            .Open "Get", strPostURL, False
            .setRequestHeader "Authorization", "Bearer " & "{token}"
            .send
        End With
    
    strPostResponse = objrequest.responseText
    
    Dim Parsed As Dictionary
    Set Parsed = JsonConverter.ParseJson(strPostResponse)
    Debug.Print JsonConverter.ConvertToJson(Parsed, Whitespace:=2)
    
For a = 1 To Parsed("results").Count 'loops through all of the results
    For b = 1 To Parsed("results")(a)("email_addresses").Count  'loops through all of the email_addresses in each result
        Cells(LR + a, 8).Value = Parsed("results")(a)("email_addresses")(b)("email_address")
    Next
Next
            
strNext = Parsed("meta")("pagination")("next_link") 'this gets the URL code for the next page
Set objrequest = Nothing intPageCount = intPageCount + 1 Loop 'Now loop back through and put strNext in the URL Next 'Keep doing this until strNext <> "next" 'Remove those email addresses from "Not Identified" list 'Be a hero 'Get the girl 'Win the day End Sub

 

View solution in original post

5 REPLIES 5
Moderator

Hello @NCJTC,

 

Let me work on this and see what I can come up with.


Regards,
Jimmy D.
Tier II API Support Engineer

Thanks in advance!

Hi @NCJTC,

 

Due to the nature of the results and other information that we will need to discuss can you please email us at webservices@constantcontact.com. I've come up with some results, but for privacy reasons I do not want to put that information here.


Regards,
Jimmy D.
Tier II API Support Engineer

Ultimately Jimmy recommended I switch to v2 of the API. This produced the file in JSON, and I was able to pull the information I needed using the code below.

 

This code is dependent on VBA-JSON (https://github.com/VBA-tools/VBA-JSON) and the reference Microsoft XML, v6.0

 

 

Sub GetSelfIdentified()

Dim strNext, strCode, strList, strListName As String
Dim LR As Long

Range("A2").Select

ActiveWorkbook.Sheets("ContactLists").Activate

Dim intPageCount As Long

For rr = 3 To 16  'rows where the list IDs are located
    strNext = "start"
    intPageCount = 1
    Do While strNext <> ""
        If intPageCount = 1 Then
            Cells(rr, 1).Activate
            strListName = ActiveCell.Value
            Cells(rr, 2).Activate
            strList = "/v2/lists/" & ActiveCell.Value & "/contacts?limit=500&api_key={API KEY}"
        Else
            strList = strNext & "&api_key={API KEY}"
        End If
        
        
        LR = Range("H65000").End(xlUp).Row  'find the bottom row of the column where the email addresses will go
' this could be skipped if the addresses get put into a text file
        
        
    strPostURL = "https://api.constantcontact.com" & strList

    Dim objrequest As New MSXML2.XMLHTTP60
    
        With objrequest
            .Open "Get", strPostURL, False
            .setRequestHeader "Authorization", "Bearer " & "{token}"
            .send
        End With
    
    strPostResponse = objrequest.responseText
    
    Dim Parsed As Dictionary
    Set Parsed = JsonConverter.ParseJson(strPostResponse)
    Debug.Print JsonConverter.ConvertToJson(Parsed, Whitespace:=2)
    
For a = 1 To Parsed("results").Count 'loops through all of the results
    For b = 1 To Parsed("results")(a)("email_addresses").Count  'loops through all of the email_addresses in each result
        Cells(LR + a, 8).Value = Parsed("results")(a)("email_addresses")(b)("email_address")
    Next
Next
            
strNext = Parsed("meta")("pagination")("next_link") 'this gets the URL code for the next page
Set objrequest = Nothing intPageCount = intPageCount + 1 Loop 'Now loop back through and put strNext in the URL Next 'Keep doing this until strNext <> "next" 'Remove those email addresses from "Not Identified" list 'Be a hero 'Get the girl 'Win the day End Sub

 

View solution in original post

Hi @NCJTC,

 

Thanks for posting your code to share with others.


Regards,
Jimmy D.
Tier II API Support Engineer
Developer Portal

View API documentation, code samples, get your API key.

Visit Page