Excel VBA integration with mailchimp

evalina

New Member
Joined
May 17, 2017
Messages
1
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!
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Watch MrExcel Video

Forum statistics

Threads
1,126,989
Messages
5,622,021
Members
415,874
Latest member
JockPC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top