Sub addItemtoList()

    Dim LISTNAME As String
    Dim SharepointUrl As String
    Dim ValueVar As String
    Dim FieldNameVar As String
    Dim CurUserName As String, CurPassword As String
    CurUserName = "user.name"
    CurPassword = "password123"

    LISTNAME = "SharepointlistName"      'Or Guild

    SharepointUrl = "https://xxxxxxxx.sharepoint.com/sites/au/finance/xxxxxx/"
    ValueVar = "Testing Value Add"
    FieldNameVar = "'Title'"

    Dim objXMLHTTP As MSXML2.XMLHTTP

    Dim strListNameOrGuid As String
    Dim strBatchXml As String
    Dim strSoapBody As String

    Set objXMLHTTP = New MSXML2.XMLHTTP

    strListNameOrGuid = LISTNAME


    'Add New Item'
    strBatchXml = "<Batch OnError='Continue'><Method ID='1' Cmd='New'><Field Name='ID'>New</Field><Field Name=" + FieldNameVar + ">" + ValueVar + "</Field></Method></Batch>"

    objXMLHTTP.Open "POST", SharepointUrl + "_vti_bin/Lists.asmx", False, CurUserName, CurPassword
    objXMLHTTP.setRequestHeader "Content-Type", "text/xml; charset=""UTF-8"""
    objXMLHTTP.setRequestHeader "SOAPAction", "http://schemas.microsoft.com/sharepoint/soap/UpdateListItems"

    strSoapBody = "<soap:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' " _
     & "xmlns:xsd='http://www.w3.org/2001/XMLSchema' " _
     & "xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'><soap:Body><UpdateListItems " _
     & "xmlns='http://schemas.microsoft.com/sharepoint/soap/'><listName>" & strListNameOrGuid _
     & "</listName><updates>" & strBatchXml & "</updates></UpdateListItems></soap:Body></soap:Envelope>"

    objXMLHTTP.send strSoapBody

    If objXMLHTTP.Status = 200 Then
    ' Do something with response
    End If

    Set objXMLHTTP = Nothing

    End Sub