How to Push Excel Contacts to Brevo with a VBA Macro (and the Office Scripts Alternative)

You’ve got contacts in Excel and you want them in Brevo. The fast-and-dirty answer is to save the file as .csv and import it, which is fine once. For anything you’re doing repeatedly — weekly sales handoff, a workbook your team updates daily, a partner list that gets refreshed — you want a button right inside Excel that does the sync.

This guide covers the two paths that actually make sense for that:

1. A VBA macro embedded in the workbook — no licensing, no cloud, works offline, runs the moment a user clicks a button. The right answer for ~80% of “Excel-to-Brevo” cases.
2. Office Scripts + Power Automate — TypeScript instead of VBA, runs in the cloud, supports scheduled triggers. The right answer if the workbook lives in OneDrive/SharePoint and you want unattended sync — but be aware of Power Automate licensing.

If you’re looking for the Google Sheets equivalent, see the companion article on Apps Script. And if you just want a one-shot CSV import from a script on your laptop, the CSV import guide has Python, Node.js, and cURL versions.

What the macro does

When the user clicks a “Sync to Brevo” button on the sheet:

1. Read every row from the active worksheet (header row first, one contact per row).
2. Build a JSON array shaped for Brevo’s jsonBody parameter.
3. POST it to https://api.brevo.com/v3/contacts/import with the workbook’s stored API key.
4. Show a message box with the result.

That’s it. ~120 lines of VBA. Below is the full, working module.

Sheet layout the macro expects

email is mandatory. Every other column becomes a Brevo contact attribute, mapped by the column header (uppercased) to the attribute name. So firstName → FIRSTNAME, company → COMPANY. Custom attributes (anything beyond the standard set) need to exist in your Brevo account first — define them under Contacts → Settings → Contact attributes.

Step 1: Open the VBA editor

In Excel: press Alt + F11. The VBA editor opens. In the Project pane on the left, right-click your workbook and choose Insert → Module. A blank Module1 appears.

Step 2: Paste the full macro

Replace Module1’s contents with this:

' ===========================================================================
' 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

Step 3: Save the workbook as .xlsm

VBA macros only persist in macro-enabled workbooks. Save As → choose Excel Macro-Enabled Workbook (.xlsm). The plain .xlsx format strips macros silently — many people lose code this way the first time.

Step 4: Configure your API key

Run ConfigureApiKey once. Either:

• In the VBA editor, click anywhere inside the ConfigureApiKey sub and press F5, or
• In Excel, Developer → Macros, pick ConfigureApiKey, Run.

Paste your xkeysib-... key. The macro stores it as a custom document property inside the workbook itself — it’s not in source code, not in the registry, and travels with the file (so be aware: if you email the .xlsm to someone, the API key goes with it).

If you’d rather put the key somewhere outside the workbook, swap the storage to the Windows registry:

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

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

SaveSetting/GetSetting writes under HKCU\Software\VB and VBA Program Settings\Brevo\Sync — per-user, not per-workbook. Use this if multiple workbooks should share one key, or if you don’t want the key in the file.

Step 5: Add a button on the sheet

This is what turns it into a one-click experience for non-technical users.

Insert → Shapes → Rectangle, drop one onto the sheet, label it “Sync to Brevo.” Right-click the shape → Assign Macro → pick SyncSheetToBrevo. Done.

Or, for a more polished UI, add a custom ribbon tab via the Office Custom UI Editor — but for most internal tools, the shape-as-button is plenty.

Step 6: Run it

Click the button. The macro reads the rows, batches them, posts each batch to Brevo, and shows a summary message box. Brevo’s import is asynchronous, so the success message means “Brevo accepted the batch” — the actual contact creation happens server-side in the next few seconds. You’ll get an email summary from Brevo when it finishes (unless you set disableNotification: true).

Common pitfalls

The button does nothing and there’s no error. Macros are disabled. Look at the yellow security bar at the top of the sheet, click Enable Content. If your org blocks macros, see the trust-center / code-signing path below.

Compile error: User-defined type not defined. You’re on Mac Excel, which doesn’t have MSXML2.XMLHTTP. Mac VBA can’t make HTTPS requests directly; use the Office Scripts path below instead.

400 Bad Request from Brevo with no obvious cause. Almost always one of: (a) a custom attribute in your sheet doesn’t exist in Brevo yet — create it first; (b) JSON escaping bug — quotes or backslashes in cell values that didn’t get escaped. The EscapeJson function in the code handles the standard cases; if your data has weird characters, log payload to the Immediate window (Debug.Print payload) and inspect.

401 Unauthorized. Wrong header. It’s api-key (lowercase, hyphen), not Authorization. The macro uses the right one — but if you copied a snippet from elsewhere, double-check.

Excel freezes on big imports. The macro runs synchronously on the UI thread. For 50,000+ rows, you’ll watch Excel hang for 10–30 seconds while it builds the JSON and waits on Brevo. Either accept it, or switch the MSXML2.XMLHTTP to its async variant — but at that scale you’re better off in Power Automate (next section).

When VBA isn’t enough: Office Scripts + Power Automate

VBA can’t do scheduled cloud sync. If you need:

• The workbook syncing to Brevo every hour without anyone opening it
• The workbook in OneDrive/SharePoint, edited from the web
• An IT department that bans desktop macros

…then you want Office Scripts (Microsoft’s cloud equivalent of Apps Script) plus Power Automate (their scheduling and HTTP layer).

The split: Office Scripts reads the sheet and returns the contact data. Power Automate takes that data and POSTs it to Brevo on a trigger.

The 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;
}

The Power Automate flow:

1. Trigger: Recurrence (every 1 hour) — or manual button, or “When a row is modified” if you want change-driven sync.
2. Action: Excel Online → Run script — point it at your workbook and the script above. Save its return value as contacts.
3. Action: HTTP (this is the Premium connector — see licensing note below).
   • 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 → if status code ≠ 202, send a Teams/email alert.

Licensing reality check: the HTTP action is a Power Automate Premium connector. On Microsoft 365 Business Basic/Standard plans you get the standard connectors but not Premium. The cheapest workaround is the Power Automate Premium add-on (~$15/user/month at time of writing), or move HTTP to a small Azure Function that the standard flow can call. If you’re already on E3/E5 with Premium included, you’re set.

This is the main reason the Apps Script story is cleaner: Apps Script’s UrlFetchApp is free and unrestricted, while the Microsoft equivalent puts the network call behind a paid connector tier.

VBA vs Office Scripts vs Apps Script — when to pick what

Why this beats Zapier / no-code platforms

For a recurring Excel-to-Brevo job, third-party automation tools (Zapier, Make, n8n) charge per-task and put a third party between your data and Brevo. The VBA approach has zero ongoing cost, no third-party data flow, and lives inside the file — when the workbook moves, the integration moves with it. Office Scripts + Power Automate is similar but with Microsoft as the third party (already in your stack if you’re on M365).

The whole point of the Brevo POST /v3/contacts/import endpoint is that you don’t need a glue platform — your tools already know how to make HTTP requests.

Further reading

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