Excel VBA integration with mailchimp

[evalina]

Hi all,

I need some help in integrating vba with mailchimp.

I need to be able to subscribe and if already subscribed, update a mailchimp record from a given excel range.

I was able to subscribe a new member to mailchimp using the following code:

Function listSubscribe2(ByVal strEmailAddress, ByVal strlistid As String, Optional ByVal strFname As String, Optional ByVal strLname As String, Optional ByVal strAcct As String, Optional ByVal strFnam_T1 As String, Optional ByVal strLnam_T1 As String, Optional ByVal strFnam_T2 As String, Optional ByVal strLnam_T2 As String, Optional ByVal strrem As String, Optional ByVal strpoi_T1 As String, Optional ByVal strpor_T1 As String, Optional ByVal strpoi_T2 As String, Optional ByVal strpor_T2 As String, Optional ByVal strprof_T1 As String, Optional ByVal strprof_T2 As String, Optional ByVal strpor_C As String, Optional ByVal strTA As String, Optional ByVal strOth As String)

On Error GoTo errorHandler

Dim objXMLHTTP As Object
Dim strResponseText As String
Dim strError As String
Dim strURL As String

Set objXMLHTTP = CreateObject("MSXML2.ServerXMLHTTP")

strURL = strMailChimpURL & "?method=listSubscribe&output=xml&apikey=" & apikey & "&id=" & strlistid & _
"&email_address=" & strEmailAddress & _
"&merge_vars=" & _
"&merge_vars[MMERGE1]=" & strFname & _
"&merge_vars[MMERGE2]=" & strLname & _
"&merge_vars[MMERGE3]=" & strAcct & _
"&merge_vars[MMERGE4]=" & strFnam_T1 & _
"&merge_vars[MMERGE5]=" & strLnam_T1 & _
"&merge_vars[MMERGE6]=" & strFnam_T2 & _
"&merge_vars[MMERGE7]=" & strLnam_T2 & _
"&merge_vars[MMERGE8]=" & strrem & _
"&merge_vars[MMERGE9]=" & strpoi_T1 & _
"&merge_vars[MMERGE10]=" & strpor_T1 & _
"&merge_vars[MMERGE11]=" & strpoi_T2 & _
"&merge_vars[MMERGE12]=" & strpor_T2 & _
"&merge_vars[MMERGE13]=" & strprof_T1 & _
"&merge_vars[MMERGE14]=" & strprof_T2 & _
"&merge_vars[MMERGE15]=" & strpor_C & _
"&merge_vars[MMERGE16]=" & strTA & _
"&merge_vars[MMERGE17]=" & strOth & _
"&send_welcome=false" & _
"&double_optin=false"

objXMLHTTP.Open "GET", strURL, False
objXMLHTTP.Send

strResponseText = objXMLHTTP.responsetext

If InStr(1, strResponseText, "error") > 0 Then
listSubscribe2 = "Not possible to add" & strEmailAddress & "syntax error"
Else
listSubscribe2 = "Contact succesfully added to the list"
End If

Set objXMLHTTP = Nothing
Exit Function

errorHandler:
listSubscribe2 = "ERR : " & Err.Description
End Function

However the update is not working for me. i used the same code as above, only I changed the method to:

strURL = strMailChimpURL & "?method=update-member&output=xml&apikey=" & apikey & "&id=" & strlistid & _
"&email_address=" & strEmailAddress & _
"&merge_vars=" & _
"&merge_vars[MMERGE1]=" & strFname & _
"&merge_vars[MMERGE2]=" & strLname & _
"&merge_vars[MMERGE3]=" & strAcct & _
"&merge_vars[MMERGE4]=" & strFnam_T1 & _
"&merge_vars[MMERGE5]=" & strLnam_T1 & _
"&merge_vars[MMERGE6]=" & strFnam_T2 & _
"&merge_vars[MMERGE7]=" & strLnam_T2 & _
"&merge_vars[MMERGE8]=" & strrem & _
"&merge_vars[MMERGE9]=" & strpoi_T1 & _
"&merge_vars[MMERGE10]=" & strpor_T1 & _
"&merge_vars[MMERGE11]=" & strpoi_T2 & _
"&merge_vars[MMERGE12]=" & strpor_T2 & _
"&merge_vars[MMERGE13]=" & strprof_T1 & _
"&merge_vars[MMERGE14]=" & strprof_T2 & _
"&merge_vars[MMERGE15]=" & strpor_C & _
"&merge_vars[MMERGE16]=" & strTA & _
"&merge_vars[MMERGE17]=" & strOth & _
"&send_welcome=false" & _
"&double_optin=false"

Can you see where the problem is?

Thank you for your help!