Excel-contacten naar Brevo pushen met een VBA-macro (en het Office Scripts-alternatief)

Je hebt contacten in Excel en je wilt ze in Brevo. Het snelle-en-vuile antwoord is het bestand opslaan als .csv en importeren, wat één keer prima is. Voor alles wat je herhaaldelijk doet (wekelijkse sales-overdracht, een werkmap die je team dagelijks bijwerkt, een partnerlijst die wordt ververst) wil je een knop direct in Excel die de sync uitvoert.

Deze gids behandelt de twee paden die hier daadwerkelijk zinvol zijn:

1. Een VBA-macro ingebed in de werkmap. Geen licenties, geen cloud, werkt offline, draait zodra een gebruiker op een knop klikt. Het juiste antwoord voor ongeveer 80% van de “Excel-naar-Brevo”-gevallen.
2. Office Scripts + Power Automate. TypeScript in plaats van VBA, draait in de cloud, ondersteunt geplande triggers. Het juiste antwoord als de werkmap in OneDrive/SharePoint staat en je onbeheerde sync wilt, maar wees je bewust van Power Automate-licenties.

Als je het Google Sheets-equivalent zoekt, zie het bijbehorende artikel over Apps Script. En als je gewoon een eenmalige CSV-import wilt vanuit een script op je laptop, heeft de CSV-importgids versies in Python, Node.js en cURL.

Wat de macro doet

Wanneer de gebruiker op een “Sync to Brevo”-knop op het blad klikt:

1. Lees elke rij van het actieve werkblad (eerst de headerrij, één contact per rij).
2. Bouw een JSON-array gevormd voor Brevo’s jsonBody-parameter.
3. POST het naar https://api.brevo.com/v3/contacts/import met de in de werkmap opgeslagen API-sleutel.
4. Toon een berichtvenster met het resultaat.

Dat is het. Ongeveer 120 regels VBA. Hieronder de volledige, werkende module.

Bladindeling die de macro verwacht

email is verplicht. Elke andere kolom wordt een Brevo-contactattribuut, gemapt door de kolomheader (in hoofdletters) naar de attribuutnaam. Dus firstName → FIRSTNAME, company → COMPANY. Aangepaste attributen (alles buiten de standaardset) moeten eerst bestaan in je Brevo-account. Definieer ze onder Contacts → Settings → Contact attributes.

Stap 1: Open de VBA-editor

In Excel: druk op Alt + F11. De VBA-editor opent. In het Project-paneel aan de linkerkant, klik met de rechtermuisknop op je werkmap en kies Insert → Module. Een leeg Module1 verschijnt.

Stap 2: Plak de volledige macro

Vervang de inhoud van Module1 met dit:

' ===========================================================================
' Brevo contact sync for Excel
' Reads the active sheet's rows and POSTs them to Brevo's import API.
' ===========================================================================
Option Explicit

Private Const BREVO_API_BASE As String = "https://api.brevo.com/v3"
Private Const BREVO_LIST_ID As Long = 42       ' <- your Brevo list ID
Private Const BATCH_SIZE As Long = 1000

' --- Public entry points (the ones you assign to ribbon buttons) -----------

Public Sub SyncSheetToBrevo()
    Dim apiKey As String
    apiKey = GetApiKey()
    If apiKey = "" Then
        MsgBox "No API key configured. Run ConfigureApiKey first.", _
               vbExclamation, "Brevo Sync"
        Exit Sub
    End If

    Dim ws As Worksheet
    Set ws = ActiveSheet

    Dim emailCol As Long
    emailCol = FindEmailColumn(ws)
    If emailCol = 0 Then
        MsgBox "Sheet must have an 'email' column in row 1.", _
               vbExclamation, "Brevo Sync"
        Exit Sub
    End If

    Dim lastRow As Long, lastCol As Long
    lastRow = ws.Cells(ws.Rows.Count, emailCol).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    If lastRow < 2 Then
        MsgBox "No contact rows found.", vbInformation, "Brevo Sync"
        Exit Sub
    End If

    Dim contacts As Collection
    Set contacts = New Collection

    Dim r As Long, c As Long
    For r = 2 To lastRow
        Dim email As String
        email = LCase(Trim(CStr(ws.Cells(r, emailCol).Value)))
        If email <> "" And InStr(email, "@") > 0 Then
            Dim json As String
            json = "{""email"":""" & EscapeJson(email) & """,""attributes"":{"

            Dim attrFirst As Boolean
            attrFirst = True
            For c = 1 To lastCol
                If c <> emailCol Then
                    Dim val As String
                    val = CStr(ws.Cells(r, c).Value)
                    If val <> "" Then
                        If Not attrFirst Then json = json & ","
                        Dim attrName As String
                        attrName = UCase(Trim(CStr(ws.Cells(1, c).Value)))
                        json = json & """" & attrName & """:""" & EscapeJson(val) & """"
                        attrFirst = False
                    End If
                End If
            Next c

            json = json & "}}"
            contacts.Add json
        End If
    Next r

    If contacts.Count = 0 Then
        MsgBox "No valid contact rows found.", vbInformation, "Brevo Sync"
        Exit Sub
    End If

    Dim totalSent As Long
    Dim batchNum As Long
    Dim okCount As Long, failCount As Long
    Dim batchStart As Long

    For batchStart = 1 To contacts.Count Step BATCH_SIZE
        batchNum = batchNum + 1
        Dim batchEnd As Long
        batchEnd = batchStart + BATCH_SIZE - 1
        If batchEnd > contacts.Count Then batchEnd = contacts.Count

        Dim payload As String
        payload = "{""jsonBody"":["
        Dim i As Long
        For i = batchStart To batchEnd
            If i > batchStart Then payload = payload & ","
            payload = payload & contacts(i)
        Next i
        payload = payload & "],""listIds"":[" & BREVO_LIST_ID & _
                  "],""updateExistingContacts"":true,""emptyContactsAttributes"":false}"

        Dim ok As Boolean
        ok = PostToBrevo(apiKey, payload)
        If ok Then
            okCount = okCount + 1
            totalSent = totalSent + (batchEnd - batchStart + 1)
        Else
            failCount = failCount + 1
        End If
    Next batchStart

    MsgBox "Sent " & totalSent & " contact(s) in " & batchNum & " batch(es)." _
           & vbCrLf & "Successful batches: " & okCount _
           & vbCrLf & "Failed batches: " & failCount, _
           vbInformation, "Brevo Sync"
End Sub

Public Sub ConfigureApiKey()
    Dim key As String
    key = InputBox("Paste your Brevo API key (xkeysib-...):", "Brevo API Key")
    If key = "" Then Exit Sub
    key = Trim(key)
    If Left(key, 8) <> "xkeysib-" Then
        MsgBox "That doesn't look like a Brevo API key (should start with xkeysib-).", _
               vbExclamation, "Brevo API Key"
        Exit Sub
    End If

    On Error Resume Next
    ThisWorkbook.CustomDocumentProperties("BrevoApiKey").Delete
    On Error GoTo 0

    ThisWorkbook.CustomDocumentProperties.Add _
        Name:="BrevoApiKey", _
        LinkToContent:=False, _
        Type:=msoPropertyTypeString, _
        Value:=key

    ThisWorkbook.Save
    MsgBox "API key saved inside the workbook.", vbInformation, "Brevo API Key"
End Sub

' --- Private helpers --------------------------------------------------------

Private Function GetApiKey() As String
    On Error Resume Next
    GetApiKey = ThisWorkbook.CustomDocumentProperties("BrevoApiKey").Value
    On Error GoTo 0
End Function

Private Function FindEmailColumn(ws As Worksheet) As Long
    Dim lastCol As Long, c As Long
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    For c = 1 To lastCol
        If LCase(Trim(CStr(ws.Cells(1, c).Value))) = "email" Then
            FindEmailColumn = c
            Exit Function
        End If
    Next c
    FindEmailColumn = 0
End Function

Private Function PostToBrevo(apiKey As String, payload As String) As Boolean
    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "POST", BREVO_API_BASE & "/contacts/import", False
    http.SetRequestHeader "api-key", apiKey
    http.SetRequestHeader "Content-Type", "application/json"
    http.SetRequestHeader "Accept", "application/json"
    http.Send payload
    PostToBrevo = (http.Status = 202)
    If Not PostToBrevo Then
        Debug.Print "Brevo error " & http.Status & ": " & http.responseText
    End If
End Function

Private Function EscapeJson(s As String) As String
    Dim r As String
    r = Replace(s, "\", "\\")
    r = Replace(r, """", "\""")
    r = Replace(r, vbCrLf, "\n")
    r = Replace(r, vbLf, "\n")
    r = Replace(r, vbCr, "\n")
    r = Replace(r, vbTab, "\t")
    EscapeJson = r
End Function

Stap 3: Sla de werkmap op als .xlsm

VBA-macro’s blijven alleen behouden in macro-enabled werkmappen. Save As, kies Excel Macro-Enabled Workbook (.xlsm). Het gewone .xlsx-formaat strijkt macro’s stilzwijgend weg. Veel mensen verliezen op deze manier de eerste keer hun code.

Stap 4: Configureer je API-sleutel

Draai ConfigureApiKey één keer. Ofwel:

• Klik in de VBA-editor ergens binnen de ConfigureApiKey-sub en druk op F5, of
• In Excel, Developer → Macros, kies ConfigureApiKey, Run.

Plak je xkeysib-...-sleutel. De macro slaat hem op als een aangepaste documenteigenschap binnen de werkmap zelf. Het staat niet in broncode, niet in het register, en reist mee met het bestand (let op: als je de .xlsm naar iemand mailt, gaat de API-sleutel mee).

Als je de sleutel liever buiten de werkmap zet, vervang je de opslag door het Windows-register:

' Replace the body of ConfigureApiKey with:
SaveSetting "Brevo", "Sync", "ApiKey", key

' And GetApiKey with:
GetApiKey = GetSetting("Brevo", "Sync", "ApiKey", "")

SaveSetting/GetSetting schrijft onder HKCU\Software\VB and VBA Program Settings\Brevo\Sync. Per gebruiker, niet per werkmap. Gebruik dit als meerdere werkmappen één sleutel moeten delen, of als je de sleutel niet in het bestand wilt.

Stap 5: Voeg een knop toe aan het blad

Dit is wat het verandert in een een-klik-ervaring voor niet-technische gebruikers.

Insert → Shapes → Rectangle, plaats er een op het blad, label hem “Sync to Brevo.” Klik met de rechtermuisknop op de vorm, Assign Macro, kies SyncSheetToBrevo. Klaar.

Of, voor een gepolijstere UI, voeg een aangepast lint-tabblad toe via de Office Custom UI Editor. Maar voor de meeste interne tools is de vorm-als-knop ruim voldoende.

Stap 6: Draai het

Klik op de knop. De macro leest de rijen, batcht ze, post elke batch naar Brevo en toont een samenvattings-berichtvenster. Brevo’s import is asynchroon, dus het succesbericht betekent “Brevo heeft de batch geaccepteerd”. De daadwerkelijke contactaanmaak gebeurt server-side in de volgende paar seconden. Je krijgt een e-mail-samenvatting van Brevo wanneer het klaar is (tenzij je disableNotification: true instelt).

Veelvoorkomende valkuilen

De knop doet niets en er is geen foutmelding. Macro’s zijn uitgeschakeld. Kijk naar de gele beveiligingsbalk bovenaan het blad, klik op Enable Content. Als je organisatie macro’s blokkeert, zie het trust-center / code-signing-pad hieronder.

Compile error: User-defined type not defined. Je gebruikt Mac Excel, dat geen MSXML2.XMLHTTP heeft. Mac VBA kan geen HTTPS-verzoeken direct doen. Gebruik in plaats daarvan het Office Scripts-pad hieronder.

400 Bad Request van Brevo zonder duidelijke oorzaak. Bijna altijd een van: (a) een aangepast attribuut in je blad bestaat nog niet in Brevo, maak het eerst aan; (b) JSON-escaping-bug, quotes of backslashes in celwaarden die niet zijn ge-escaped. De EscapeJson-functie in de code handelt de standaardgevallen af; als je data rare tekens heeft, log payload naar het Immediate window (Debug.Print payload) en inspecteer.

401 Unauthorized. Verkeerde header. Het is api-key (kleine letters, koppelteken), niet Authorization. De macro gebruikt de juiste, maar als je een snippet van elders hebt gekopieerd, dubbelcheck.

Excel bevriest bij grote imports. De macro draait synchroon op de UI-thread. Voor 50.000+ rijen kijk je toe hoe Excel 10 tot 30 seconden hangt terwijl het de JSON bouwt en wacht op Brevo. Accepteer het, of schakel de MSXML2.XMLHTTP over naar de async-variant. Maar op die schaal zit je beter in Power Automate (volgende sectie).

Wanneer VBA niet genoeg is: Office Scripts + Power Automate

VBA kan geen geplande cloud-sync. Als je nodig hebt:

• De werkmap die elk uur naar Brevo synchroniseert zonder dat iemand hem opent
• De werkmap in OneDrive/SharePoint, bewerkt vanuit het web
• Een IT-afdeling die desktopmacro’s verbiedt

…dan wil je Office Scripts (Microsofts cloud-equivalent van Apps Script) plus Power Automate (hun planning- en HTTP-laag).

De splitsing: Office Scripts leest het blad en geeft de contactdata terug. Power Automate neemt die data en POST’t deze naar Brevo bij een trigger.

Het Office Script (Excel for the web, Automate → New Script):

function main(workbook: ExcelScript.Workbook): {email: string, attributes: Record<string, string>}[] {
  const sheet = workbook.getActiveWorksheet();
  const range = sheet.getUsedRange();
  if (!range) return [];

  const values = range.getValues() as string[][];
  if (values.length < 2) return [];

  const headers = values[0].map(h => String(h).trim());
  const emailIdx = headers.findIndex(h => h.toLowerCase() === "email");
  if (emailIdx === -1) throw new Error("Sheet must have an 'email' column");

  const contacts: {email: string, attributes: Record<string, string>}[] = [];
  for (let r = 1; r < values.length; r++) {
    const row = values[r];
    const email = String(row[emailIdx] ?? "").trim().toLowerCase();
    if (!email || !email.includes("@")) continue;

    const attributes: Record<string, string> = {};
    for (let c = 0; c < headers.length; c++) {
      if (c === emailIdx) continue;
      const v = row[c];
      if (v === null || v === "") continue;
      attributes[headers[c].toUpperCase()] = String(v);
    }
    contacts.push({ email, attributes });
  }
  return contacts;
}

De Power Automate-flow:

1. Trigger: Recurrence (elk 1 uur), of handmatige knop, of “When a row is modified” als je veranderings-gedreven sync wilt.
2. Action: Excel Online → Run script, wijs het naar je werkmap en het bovenstaande script. Sla de retourwaarde op als contacts.
3. Action: HTTP (dit is de Premium-connector, zie licentienotitie hieronder).
   • Method: POST
   • URI: https://api.brevo.com/v3/contacts/import
   • Headers: api-key: xkeysib-..., Content-Type: application/json
   • Body:

     {
       "jsonBody": @{outputs('Run_script')?['body/result']},
       "listIds": [42],
       "updateExistingContacts": true
     }

4. Action: Condition → als statuscode ≠ 202, stuur een Teams/email-alert.

Realiteitscheck licenties: de HTTP-actie is een Power Automate Premium-connector. Op Microsoft 365 Business Basic/Standard-plannen krijg je de standaardconnectors maar geen Premium. De goedkoopste workaround is de Power Automate Premium-add-on (rond de $15/gebruiker/maand op het moment van schrijven), of verplaats HTTP naar een kleine Azure Function die het standaardflow kan aanroepen. Als je al op E3/E5 met Premium inbegrepen zit, ben je klaar.

Dit is de belangrijkste reden dat het Apps Script-verhaal schoner is: Apps Script’s UrlFetchApp is gratis en onbeperkt, terwijl het Microsoft-equivalent de netwerkoproep achter een betaalde connector-tier zet.

VBA versus Office Scripts versus Apps Script: wanneer kies je wat

Waarom dit beter is dan Zapier / no-code-platforms

Voor een terugkerende Excel-naar-Brevo-job rekenen externe automatiseringstools (Zapier, Make, n8n) per taak en plaatsen ze een derde partij tussen jouw data en Brevo. De VBA-aanpak heeft nul doorlopende kosten, geen data-stroom via derden, en leeft binnen het bestand. Wanneer de werkmap verplaatst wordt, verplaatst de integratie mee. Office Scripts + Power Automate is vergelijkbaar maar met Microsoft als de derde partij (al in je stack als je op M365 zit).

Het hele punt van Brevo’s POST /v3/contacts/import-endpoint is dat je geen lijmplatform nodig hebt. Je tools weten al hoe ze HTTP-verzoeken moeten doen.

Verder lezen

• Brevo API: Import contacts reference
• Microsoft Office Scripts overview
• Power Automate HTTP-connector (Premium)
• Het Apps Script-equivalent voor Google Sheets
• De script-gebaseerde CSV/Excel-import (Python, Node.js, cURL)