--- title: "How to Push Excel Contacts to Brevo with a VBA Macro (and the Office Scripts Alternative)" description: "A working VBA macro that posts contacts from an Excel sheet to Brevo's API in one click - plus when to use Office Scripts + Power Automate instead, and the trade-offs vs a Google Apps Script setup." url: https://tajo.io/blog/import-excel-contacts-to-brevo-with-vba-macro/ markdown: https://tajo.io/blog/import-excel-contacts-to-brevo-with-vba-macro.md lang: en-US author: "Set Noa" author_url: https://tajo.io/set-noa/ published: 2026-04-30 updated: 2026-05-11 alternates: {bg-BG: /bg/blog/import-excel-contacts-to-brevo-with-vba-macro.md, cs-CZ: /cz/blog/import-excel-contacts-to-brevo-with-vba-macro.md, de-DE: /de/blog/import-excel-contacts-to-brevo-with-vba-macro.md, da-DK: /dk/blog/import-excel-contacts-to-brevo-with-vba-macro.md, es-ES: /es/blog/import-excel-contacts-to-brevo-with-vba-macro.md, fr-FR: /fr/blog/import-excel-contacts-to-brevo-with-vba-macro.md, el-GR: /gr/blog/import-excel-contacts-to-brevo-with-vba-macro.md, hi-IN: /hi/blog/import-excel-contacts-to-brevo-with-vba-macro.md, id-ID: /in/blog/import-excel-contacts-to-brevo-with-vba-macro.md, hr-HR: /hr/blog/import-excel-contacts-to-brevo-with-vba-macro.md, hu-HU: /hu/blog/import-excel-contacts-to-brevo-with-vba-macro.md, it-IT: /it/blog/import-excel-contacts-to-brevo-with-vba-macro.md, ja-JP: /jp/blog/import-excel-contacts-to-brevo-with-vba-macro.md, ko-KR: /kr/blog/import-excel-contacts-to-brevo-with-vba-macro.md, ar-SA: /ar/blog/import-excel-contacts-to-brevo-with-vba-macro.md, nl-NL: /nl/blog/import-excel-contacts-to-brevo-with-vba-macro.md, no-NO: /no/blog/import-excel-contacts-to-brevo-with-vba-macro.md, pl-PL: /pl/blog/import-excel-contacts-to-brevo-with-vba-macro.md, pt-BR: /pt/blog/import-excel-contacts-to-brevo-with-vba-macro.md, ro-RO: /ro/blog/import-excel-contacts-to-brevo-with-vba-macro.md, ru-RU: /ru/blog/import-excel-contacts-to-brevo-with-vba-macro.md, sv-SE: /se/blog/import-excel-contacts-to-brevo-with-vba-macro.md, zh-CN: /zh-cn/blog/import-excel-contacts-to-brevo-with-vba-macro.md, sl-SI: /sl/blog/import-excel-contacts-to-brevo-with-vba-macro.md, sr-Latn-RS: /sr/blog/import-excel-contacts-to-brevo-with-vba-macro.md, th-TH: /th/blog/import-excel-contacts-to-brevo-with-vba-macro.md, tr-TR: /tr/blog/import-excel-contacts-to-brevo-with-vba-macro.md, ur-PK: /ur/blog/import-excel-contacts-to-brevo-with-vba-macro.md, vi-VN: /vi/blog/import-excel-contacts-to-brevo-with-vba-macro.md, sk-SK: /sk/blog/import-excel-contacts-to-brevo-with-vba-macro.md} --- [Data Sync](https://tajo.io/blog/?category=data-sync) # How to Push Excel Contacts to Brevo with a VBA Macro (and the Office Scripts Alternative) A working VBA macro that posts contacts from an Excel sheet to Brevo's API in one click - plus when to use Office Scripts + Power Automate instead, and the trade-offs vs a Google Apps Script setup. excel to brevo How do you import Excel contacts into Brevo? Two viable paths: a VBA macro inside the .xlsm (best for one-click manual sync from desktop Excel, no licensing) or Office Scripts + Power Automate (best for scheduled cloud sync, but needs M365 + Power Automate Premium for HTTP). Both hit Brevo's POST /v3/contacts/import. Full code below for both. [Learn more](https://tajo.io/blog/import-excel-contacts-to-brevo-with-vba-macro/#what-the-macro-does) | Source field | Destination field | Sample value | | --- | --- | --- | | `email` | `EMAIL` | `alex@example.com` | | `firstName` | `FIRSTNAME` | `Alex` | | `company` | `COMPANY` | `Example Co` | 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](https://tajo.io/blog/import-google-sheet-contacts-to-brevo-apps-script). And if you just want a one-shot CSV import from a script on your laptop, the [CSV import guide](https://tajo.io/blog/import-csv-contacts-to-brevo-with-a-script) 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. *What one click on Sync to Brevo sends where. The macro reads worksheet rows, builds JSON batches for Brevo's jsonBody parameter, and posts them to the contacts import endpoint; Brevo's status comes back as a message box.* ## Sheet layout the macro expects | email | firstName | lastName | company | city | | --- | --- | --- | --- | --- | | [jane@example.com](mailto:jane@example.com) | Jane | Doe | Acme | Berlin | | [john@example.com](mailto:john@example.com) | John | Smith | Globex | Paris | `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: ```vba ' =========================================================================== ' 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: ```vba ' 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**): ```typescript function main(workbook: ExcelScript.Workbook): {email: string, attributes: Record}[] { 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}[] = []; 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 = {}; 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: ```json { "jsonBody": @{outputs('Run_script')?['body/result']}, "listIds": [42], "updateExistingContacts": true } ``` 4. **Action**: *Condition* → if status code ≠ 202, send a Teams/email alert. *The Power Automate flow, step by step* **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 | Need | Best option | | --- | --- | | One-click button in a workbook your team already opens daily | **VBA macro** (this guide, top half) | | Workbook in OneDrive/SharePoint, hourly auto-sync | **Office Scripts + Power Automate** (need Premium for HTTP) | | Mac Excel only, can’t use VBA | **Office Scripts + Power Automate** | | The data lives in Google Sheets, not Excel | **[Apps Script](https://tajo.io/blog/import-google-sheet-contacts-to-brevo-apps-script)** (free, scheduled triggers built in) | | One-off import, will never need it again | **Save As → CSV** and use the [CSV import script](https://tajo.io/blog/import-csv-contacts-to-brevo-with-a-script) | | Bulk import from a file >10 MB | **CSV with `fileUrl`** - see the [CSV guide](https://tajo.io/blog/import-csv-contacts-to-brevo-with-a-script#files-larger-than-10-mb-use-fileurl) | ## 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](https://developers.brevo.com/reference/import-contacts) - [Microsoft Office Scripts overview](https://learn.microsoft.com/en-us/office/dev/scripts/overview/excel) - [Power Automate HTTP connector](https://learn.microsoft.com/en-us/connectors/webcontents/) (Premium) - [The Apps Script equivalent for Google Sheets](https://tajo.io/blog/import-google-sheet-contacts-to-brevo-apps-script) - [The script-based CSV/Excel import (Python, Node.js, cURL)](https://tajo.io/blog/import-csv-contacts-to-brevo-with-a-script) ## Frequently Asked Questions **Can a VBA macro really call Brevo's API from inside Excel?** Yes. VBA can make HTTP requests through MSXML2.XMLHTTP, which ships with every modern Windows install. The macro POSTs JSON to api.brevo.com/v3/contacts/import, the same endpoint a Python or Node script would hit. Mac Excel can do it too via Office Scripts + Power Automate, but desktop Mac VBA can't (no MSXML). **Can I run this on a schedule like Apps Script time triggers?** Not from VBA alone - Excel must be open for Application.OnTime to fire. For unattended scheduled sync you have two options: (1) Windows Task Scheduler that opens the workbook and runs the macro, or (2) Office Scripts triggered by a Power Automate scheduled flow (covered later in this guide). **Is VBA secure? My company blocks macros.** VBA macros run with file-system and registry access, so blocking them by default is a sensible policy. Two paths around it: (1) sign the macro with a code-signing cert and put the workbook in a Trusted Location, or (2) skip VBA entirely and use Office Scripts (TypeScript, sandboxed, no file-system access). Office Scripts is the modern Microsoft-blessed path. **How does this compare to the Google Apps Script approach?** Apps Script is closer to set-and-forget - runs in Google's cloud, scheduled triggers built in, no Excel/Power Automate license. VBA wins for offline work and one-click manual sync from a workbook your team already has open. Office Scripts + Power Automate is the cloud/scheduled equivalent on the Microsoft side, but Power Automate often needs a paid premium connector for outbound HTTP. ## Related Articles ### [How to Import CSV or Excel Contacts to Brevo with a Script (Python, Node.js, cURL)](https://tajo.io/blog/import-csv-contacts-to-brevo-with-a-script/) Apr 30, 2026 · 5 min read CSV → Brevo ### [Import Google Sheets Contacts to Brevo With Apps Script](https://tajo.io/blog/import-google-sheet-contacts-to-brevo-apps-script/) Apr 30, 2026 · 3 min read Google Sheets → Brevo ### [Brevo Data Export and Migration: How to Move Your Data In or Out](https://tajo.io/blog/brevo-data-export-migration/) Aug 19, 2026 · 10 min read 90 days max range, events endpoint