ZUGFeRD and XRechnung from Excel and Access: E-Invoicing with VBA and One API Call

This guide adds ZUGFeRD and XRechnung to an Excel workbook or an Access database that creates invoices with VBA. You need one VBA module and one REST API. The code uses MSXML2.ServerXMLHTTP for the HTTP calls and ADODB.Stream for the files. Both are part of Windows. No add-in, no new software, no change to your invoice sheet. The email section shows how the API sends the invoice to the customer. The import section shows a button that checks a supplier invoice and reads its data. If your invoices live in Google Sheets instead, read the Google Sheets guide. It shows the same integration in Apps Script.

Why Excel and Access need ZUGFeRD now

German companies that invoice from Excel or Access must send and receive structured e-invoices. Since January 2025, every German business must accept e-invoices. From January 2027, businesses above EUR 800,000 turnover must send them. From 2028, all businesses must. The PDF from File, Save As or from ExportAsFixedFormat does not count. It is a picture of the invoice with no data inside. The law wants a hybrid file: a PDF the customer can open, with XML inside that their software can read. This file is ZUGFeRD. The Germany mandate page lists the dates and formats.

The workbook or database can stay. The invoice data is already there: number, date, customer, lines, VAT rates. The macro that prints the PDF today can post the same data to an API and save the ZUGFeRD PDF instead. The people who use the sheet see one new button.

The integration does not change when the formats change. ZUGFeRD, XRechnung and the EN 16931 rule sets get new versions. The API applies them on their effective dates. Your macro sends the same JSON before and after. Nothing to update in the workbook.

What you need

• Excel 2010 or later, or Access 2010 or later, on Windows. 32-bit and 64-bit Office both work. The workbook is saved as .xlsm, the database as .accdb.
• MSXML 6 and ADO. Both ship with Windows. The code creates them with CreateObject, so no reference needs to be added in the VBA editor.
• HTTPS to api.invoicexml.com. Windows 10 and 11 support TLS 1.2 out of the box. If your company uses a proxy, see the Office section.
• Outlook, only if you want to send from the macro instead of from the API.
• An InvoiceXML account. Create one. You get an API key and 100 free credits. No credit card is needed.

How the integration works

The user fills in the invoice sheet or the invoice form and clicks a button. The macro reads the cells or the record, builds a JSON body as a string and posts it to one endpoint. The API calculates the totals and the VAT, creates the XML, embeds it in a PDF/A-3 file, validates it and returns the PDF. The macro saves the PDF in a folder or in the database, or lets the API email it to the customer. One call, one validated file.

All endpoints take the same JSON body. You create these objects from the listings in this guide:

• Module modInvoiceXml: the settings reader, the HTTP helper, the JSON helpers and the file helpers. Identical in Excel and Access.
• Module modInvoiceExcel or modInvoiceAccess: reads the invoice and builds the JSON, creates the file, sends it by email.
• Module modInvoiceImport: the file dialog, the validation, the report mail and the small JSON reader.
• In Excel: a sheet Settings (very hidden), a sheet Rechnung with named cells and the table tblLines, a sheet Eingang for incoming invoices, and two buttons.
• In Access: the tables tblSettings and tblEingangsrechnungen, a column LeitwegID on tblCustomers, an attachment field PdfFile and a date field EInvoiceCreated on tblInvoices, and two buttons on frmInvoice.

Everything else is standard Office: Range, ListObject, Names, DAO.Recordset, Application.FileDialog and Outlook.Application.

Step 1: Store the API key and write the HTTP helper

Excel. Add a sheet Settings. Put the API key in a cell and name it ApiKey (Formulas, Define Name). Put your company data in named cells next to it: SellerName, SellerVatId, SellerStreet, SellerCity, SellerPostCode, SellerCountry, SellerIban, SellerEmail. In the VBA editor, set the sheet's Visible property to xlSheetVeryHidden. The sheet then does not appear in the Unhide list. Protect the VBA project with a password (Tools, VBAProject Properties, Protection).

Access. Create a table tblSettings with the columns SettingName and SettingValue and one row per setting. Hide the table (right click, Hide in this Group) and give users an .accde file. In both cases: share the file only with the people who may send invoices. Anyone who can open it can run the macro.

The HTTP helper and the JSON helpers are the same in Excel and Access. VBA has no JSON library, so the body is built by hand. Two small functions make that safe: JsonEscape for strings and JsonNumber for numbers.

' modInvoiceXml: settings, HTTP helper, JSON helpers
Option Explicit

Private Const BASE_URL As String = "https://api.invoicexml.com"

Public Type ApiResult
    Status As Long      ' HTTP status code
    Valid As Boolean    ' X-Invoice-Valid header of the report endpoints
    Body() As Byte      ' the returned file
    Text As String      ' the returned JSON, or the error JSON on 400
End Type

' Excel: the named cell on the very hidden sheet Settings
Public Function Setting(settingName As String) As String
    Setting = CStr(ThisWorkbook.Names(settingName).RefersToRange.Value)
End Function

' Access: the same function reads tblSettings instead
' Public Function Setting(settingName As String) As String
'     Setting = Nz(DLookup("SettingValue", "tblSettings", "SettingName = '" & settingName & "'"), "")
' End Function

Public Function PostJson(path As String, json As String) As ApiResult
    Dim http As Object
    Set http = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    http.Open "POST", BASE_URL & path, False
    http.setRequestHeader "Authorization", "Bearer " & Setting("ApiKey")
    http.setRequestHeader "Content-Type", "application/json; charset=utf-8"
    http.send json                       ' a VBA string is sent as UTF-8
    PostJson = ReadResult(http)
End Function

Private Function ReadResult(http As Object) As ApiResult
    Dim r As ApiResult
    r.Status = http.Status
    r.Valid = (http.getResponseHeader("X-Invoice-Valid") = "true")
    r.Body = http.responseBody           ' bytes: PDF, XML or JSON
    If InStr(http.getResponseHeader("Content-Type"), "json") > 0 Then
        r.Text = Utf8ToText(r.Body)
    End If
    ReadResult = r
End Function

' Strings: double quotes, backslashes, line breaks and tabs are escaped
Public Function JsonEscape(value As Variant) As String
    Dim s As String
    s = CStr(value)
    s = Replace(s, "\", "\\")
    s = Replace(s, """", "\""")
    s = Replace(s, vbCrLf, "\n")
    s = Replace(s, vbCr, "\n")
    s = Replace(s, vbLf, "\n")
    s = Replace(s, vbTab, "\t")
    JsonEscape = """" & s & """"
End Function

' Numbers: Str always writes a period. CStr writes the Windows decimal
' separator, which is a comma on a German PC and breaks the JSON.
Public Function JsonNumber(value As Variant) As String
    Dim s As String
    s = Trim$(Str$(CDbl(value)))
    If Left$(s, 1) = "." Then s = "0" & s
    If Left$(s, 2) = "-." Then s = "-0" & Mid$(s, 2)
    JsonNumber = s
End Function

' Bytes to text, for the JSON answers
Public Function Utf8ToText(data() As Byte) As String
    Dim stm As Object
    Set stm = CreateObject("ADODB.Stream")
    stm.Type = 1                         ' adTypeBinary
    stm.Open
    stm.Write data
    stm.Position = 0
    stm.Type = 2                         ' adTypeText
    stm.Charset = "utf-8"
    Utf8ToText = stm.ReadText
    stm.Close
End Function

ServerXMLHTTP sends a VBA string as UTF-8, so umlauts in names and addresses arrive correctly. The report endpoints put the verdict in the X-Invoice-Valid header, so ReadResult stores it in Valid. If you prefer WinHttp.WinHttpRequest.5.1, change the CreateObject line. The methods have the same names.

Step 2: Map cells and tables to JSON

The API takes an EN 16931 invoice as JSON. In Excel, the header comes from named cells on the sheet Rechnung and the lines from the table tblLines. In Access, the header comes from tblInvoices joined with tblCustomers and the lines from tblInvoiceLines. The seller data comes from the settings. This is the mapping:

You do not send totals, VAT amounts or the gross sum. The API calculates them from the lines. If the numbers do not add up, the API returns an error instead of a file. This catches rounding and discount mistakes before the customer sees them. Your sheet can keep its own total formulas for the printed layout.

' modInvoiceExcel: the sheet Rechnung to JSON
Option Explicit

Public Function BuildInvoiceJson() As String
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Rechnung")

    ' Lines from the table tblLines: Description, Quantity, Unit, NetPrice, VatRate
    Dim lo As ListObject, r As ListRow, lines As String
    Set lo = ws.ListObjects("tblLines")
    For Each r In lo.ListRows
        If Len(Col(lo, r, "Description")) > 0 Then
            If Len(lines) > 0 Then lines = lines & ","
            lines = lines & "{""quantity"":" & JsonNumber(Col(lo, r, "Quantity")) & _
                ",""unitCode"":" & JsonEscape(UnitCode(Col(lo, r, "Unit"))) & _
                ",""item"":{""name"":" & JsonEscape(Col(lo, r, "Description")) & "}" & _
                ",""priceDetails"":{""netPrice"":" & JsonNumber(Col(lo, r, "NetPrice")) & "}" & _
                ",""vatInformation"":{""rate"":" & JsonNumber(VatPercent(Col(lo, r, "VatRate"))) & "}}"
        End If
    Next r

    BuildInvoiceJson = "{" & _
        """invoiceNumber"":" & JsonEscape(ws.Range("InvoiceNumber").Value) & _
        ",""issueDate"":" & JsonEscape(Format(ws.Range("InvoiceDate").Value, "yyyy-mm-dd")) & _
        ",""dueDate"":" & JsonEscape(Format(ws.Range("DueDate").Value, "yyyy-mm-dd")) & _
        ",""currency"":""EUR""" & _
        OptionalField("purchaseOrderReference", ws.Range("OrderReference").Value) & _
        OptionalField("buyerReference", ws.Range("LeitwegID").Value) & _
        ",""seller"":" & Party(Setting("SellerName"), Setting("SellerVatId"), Setting("SellerStreet"), _
                               Setting("SellerCity"), Setting("SellerPostCode"), Setting("SellerCountry")) & _
        ",""buyer"":" & Party(ws.Range("CustomerName").Value, ws.Range("CustomerVatId").Value, _
                              ws.Range("CustomerStreet").Value, ws.Range("CustomerCity").Value, _
                              ws.Range("CustomerPostCode").Value, ws.Range("CustomerCountry").Value) & _
        ",""paymentDetails"":{""paymentAccountIdentifier"":" & JsonEscape(Setting("SellerIban")) & "}" & _
        ",""lines"":[" & lines & "]}"
End Function

Private Function Col(lo As ListObject, r As ListRow, columnName As String) As Variant
    Col = r.Range.Cells(1, lo.ListColumns(columnName).Index).Value
End Function

' A cell formatted as 19 % holds 0.19. The API wants 19.
Private Function VatPercent(value As Variant) As Double
    If value < 1 Then VatPercent = Round(value * 100, 2) Else VatPercent = value
End Function

' The next three functions go into modInvoiceXml. Access uses them too.
Public Function Party(partyName As Variant, vatId As Variant, street As Variant, city As Variant, _
                      postCode As Variant, country As Variant) As String
    Party = "{""name"":" & JsonEscape(partyName) & _
        OptionalField("vatIdentifier", vatId) & _
        ",""postalAddress"":{""line1"":" & JsonEscape(street) & ",""city"":" & JsonEscape(city) & _
        ",""postCode"":" & JsonEscape(postCode) & ",""country"":" & JsonEscape(country) & "}}"
End Function

' Leaves the field out when the cell is empty, so B2B invoices carry no buyerReference
Public Function OptionalField(key As String, value As Variant) As String
    If Len(Trim$(CStr(value))) > 0 Then OptionalField = ",""" & key & """:" & JsonEscape(value)
End Function

' UN/ECE Recommendation 20 codes for the units your lines use
Public Function UnitCode(unit As Variant) As String
    Select Case LCase$(CStr(unit))
        Case "stk", "stück", "pcs": UnitCode = "H87"
        Case "std", "h": UnitCode = "HUR"
        Case "kg": UnitCode = "KGM"
        Case "m": UnitCode = "MTR"
        Case Else: UnitCode = "C62"
    End Select
End Function

Two Excel traps. A post code with a leading zero must be a text cell, or Excel turns 01067 into 1067. And an empty line in tblLines is skipped by the Description check, so the table can have spare rows.

In Access, the same function takes the invoice id and reads two recordsets. The helpers Party, OptionalField and UnitCode are shared. Nz turns Null fields into empty strings, which OptionalField then leaves out.

' modInvoiceAccess: tblInvoices + tblCustomers + tblInvoiceLines to JSON
Option Explicit

Public Function BuildInvoiceJson(invoiceId As Long) As String
    Dim db As DAO.Database, rs As DAO.Recordset, lines As String
    Set db = CurrentDb

    Set rs = db.OpenRecordset("SELECT Description, Quantity, Unit, NetPrice, VatRate " & _
        "FROM tblInvoiceLines WHERE InvoiceID = " & invoiceId & " ORDER BY LineID", dbOpenSnapshot)
    Do Until rs.EOF
        If Len(lines) > 0 Then lines = lines & ","
        lines = lines & "{""quantity"":" & JsonNumber(rs!Quantity) & _
            ",""unitCode"":" & JsonEscape(UnitCode(Nz(rs!Unit, ""))) & _
            ",""item"":{""name"":" & JsonEscape(Nz(rs!Description, "")) & "}" & _
            ",""priceDetails"":{""netPrice"":" & JsonNumber(rs!NetPrice) & "}" & _
            ",""vatInformation"":{""rate"":" & JsonNumber(rs!VatRate) & "}}"
        rs.MoveNext
    Loop
    rs.Close

    Set rs = db.OpenRecordset("SELECT i.InvoiceNumber, i.InvoiceDate, i.DueDate, i.OrderReference, " & _
        "c.CustomerName, c.VatId, c.Street, c.City, c.PostCode, c.Country, c.LeitwegID " & _
        "FROM tblInvoices AS i INNER JOIN tblCustomers AS c ON i.CustomerID = c.CustomerID " & _
        "WHERE i.InvoiceID = " & invoiceId, dbOpenSnapshot)

    BuildInvoiceJson = "{" & _
        """invoiceNumber"":" & JsonEscape(rs!InvoiceNumber) & _
        ",""issueDate"":" & JsonEscape(Format(rs!InvoiceDate, "yyyy-mm-dd")) & _
        ",""dueDate"":" & JsonEscape(Format(rs!DueDate, "yyyy-mm-dd")) & _
        ",""currency"":""EUR""" & _
        OptionalField("purchaseOrderReference", Nz(rs!OrderReference, "")) & _
        OptionalField("buyerReference", Nz(rs!LeitwegID, "")) & _
        ",""seller"":" & Party(Setting("SellerName"), Setting("SellerVatId"), Setting("SellerStreet"), _
                               Setting("SellerCity"), Setting("SellerPostCode"), Setting("SellerCountry")) & _
        ",""buyer"":" & Party(rs!CustomerName, Nz(rs!VatId, ""), rs!Street, rs!City, rs!PostCode, rs!Country) & _
        ",""paymentDetails"":{""paymentAccountIdentifier"":" & JsonEscape(Setting("SellerIban")) & "}" & _
        ",""lines"":[" & lines & "]}"
    rs.Close
End Function

Step 3: Create the ZUGFeRD invoice

One macro builds the body, posts it and saves the PDF. Assign it to a button on the sheet (Developer, Insert, Button) or to the button cmdZugferd on the Access form.

' modInvoiceExcel: the button "ZUGFeRD erstellen" on the sheet Rechnung
Public Sub CreateZugferd()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Rechnung")

    Dim res As ApiResult
    res = PostJson("/v1/create/zugferd", "{""invoice"":" & BuildInvoiceJson() & "}")
    If res.Status <> 200 Then
        ' 400 returns JSON with an errors array: one entry per violated rule,
        ' with the rule id and the JSON path of the field
        MsgBox "Die Rechnung wurde nicht erstellt (HTTP " & res.Status & ")." & _
               vbCrLf & vbCrLf & res.Text, vbExclamation
        Exit Sub
    End If

    Dim path As String
    path = InvoiceFolder() & ws.Range("InvoiceNumber").Value & ".pdf"
    SaveBytes res.Body, path
    ws.Range("Status").Value = "ZUGFeRD erstellt " & Format(Now, "dd.mm.yyyy hh:nn")
    ws.Hyperlinks.Add ws.Range("Status"), path
End Sub

' The folder Rechnungen next to the workbook, created on first use.
' In Access, use CurrentProject.Path instead of ThisWorkbook.Path.
Public Function InvoiceFolder() As String
    InvoiceFolder = ThisWorkbook.Path & "\Rechnungen\"
    If Dir(InvoiceFolder, vbDirectory) = "" Then MkDir InvoiceFolder
End Function

' modInvoiceXml: write the returned bytes to disk
Public Sub SaveBytes(data() As Byte, path As String)
    Dim stm As Object
    Set stm = CreateObject("ADODB.Stream")
    stm.Type = 1                         ' adTypeBinary
    stm.Open
    stm.Write data
    stm.SaveToFile path, 2               ' adSaveCreateOverWrite
    stm.Close
End Sub

For French customers, post the same body to /v1/create/facturx. ZUGFeRD and Factur-X are the same standard under two names. Pick the endpoint by the country cell. The Factur-X API toolkit covers the French side.

Step 4: Save the PDF next to the workbook or in the database

Excel. The macro above already saves the PDF in the folder Rechnungen next to the workbook and writes a link into the Status cell. If you keep an invoice list on another sheet, write the file path into the row of that invoice. The folder is the archive: PDF/A-3 is an archive format, and the XML travels inside the file.

Access. The button on frmInvoice saves the record first, creates the PDF and stores it in the attachment field PdfFile of tblInvoices. The date field EInvoiceCreated marks the record as done. Users open the PDF from the paperclip on the form.

' frmInvoice: the button cmdZugferd
Private Sub cmdZugferd_Click()
    If Me.Dirty Then Me.Dirty = False    ' save the record first

    Dim res As ApiResult
    res = PostJson("/v1/create/zugferd", "{""invoice"":" & BuildInvoiceJson(CLng(Me!InvoiceID)) & "}")
    If res.Status <> 200 Then
        MsgBox "Die Rechnung wurde nicht erstellt (HTTP " & res.Status & ")." & _
               vbCrLf & vbCrLf & res.Text, vbExclamation
        Exit Sub
    End If

    Dim path As String
    path = InvoiceFolder() & Me!InvoiceNumber & ".pdf"
    SaveBytes res.Body, path
    AttachPdf CLng(Me!InvoiceID), path
    Me.Refresh
End Sub

' modInvoiceAccess: store the PDF in the attachment field and mark the invoice as done
Public Sub AttachPdf(invoiceId As Long, path As String)
    Dim rs As DAO.Recordset2, att As DAO.Recordset2
    Set rs = CurrentDb.OpenRecordset("SELECT PdfFile, EInvoiceCreated FROM tblInvoices " & _
                                     "WHERE InvoiceID = " & invoiceId, dbOpenDynaset)
    rs.Edit
    Set att = rs!PdfFile.Value           ' the attachment field is a recordset of its own
    att.AddNew
    att!FileData.LoadFromFile path
    att.Update
    rs!EInvoiceCreated = Now
    rs.Update
    rs.Close
End Sub

When to run it. A button per invoice is the normal case for a small company. For a batch, loop over SELECT InvoiceID FROM tblInvoices WHERE EInvoiceCreated IS NULL and call the same two steps per record. In Excel, a macro can loop over an invoice list sheet the same way and fill the Rechnung sheet per row before each call.

Sending from Office. If you send invoices from Outlook today, attach the ZUGFeRD PDF from the Rechnungen folder instead of the printed PDF. The hybrid PDF already contains the readable invoice. The customer gets one PDF that opens normally and that their software can also read.

Send the invoice by email from the API

You can skip Outlook. The API creates the invoice, validates it and emails it to the customer in one call. The macro never handles the file. This is enough for Germany: there is no central platform for B2B e-invoices, and an email with a ZUGFeRD PDF is a complete delivery.

Set up your sending domain once in the dashboard: add the domain and publish the three CNAME records it shows. From then on, every address on that domain can send. The customer sees your address, and replies reach you. The request is the same JSON body plus an email object. The recipient comes from the cell CustomerEmail (or tblCustomers.Email in Access):

' modInvoiceExcel: the API creates, validates and sends the invoice
Public Sub EmailZugferd()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Rechnung")
    Dim invoiceNo As String
    invoiceNo = ws.Range("InvoiceNumber").Value

    Dim body As String
    body = "{""invoice"":" & BuildInvoiceJson() & _
        ",""email"":{""from"":" & JsonEscape(Setting("SellerEmail")) & _
        ",""to"":" & JsonEscape(ws.Range("CustomerEmail").Value) & _
        ",""subject"":" & JsonEscape("Rechnung " & invoiceNo) & _
        ",""message"":" & JsonEscape("Guten Tag," & vbCrLf & vbCrLf & _
            "anbei erhalten Sie unsere Rechnung " & invoiceNo & " als ZUGFeRD-PDF." & _
            vbCrLf & vbCrLf & "Mit freundlichen Grüßen") & "}}"

    Dim res As ApiResult
    res = PostJson("/v1/create/zugferd/email", body)
    If res.Status = 200 Then
        ' 200 means: created, validated and handed to delivery
        ws.Range("Status").Value = "Per E-Mail gesendet " & Format(Now, "dd.mm.yyyy hh:nn")
    Else
        MsgBox res.Text, vbExclamation
    End If
End Sub

' Or send the saved PDF with Outlook. The mail opens, the user checks it and clicks Send.
Public Sub SendWithOutlook(path As String, toAddress As String, invoiceNo As String)
    Dim ol As Object, mail As Object
    Set ol = CreateObject("Outlook.Application")
    Set mail = ol.CreateItem(0)          ' olMailItem
    mail.To = toAddress
    mail.Subject = "Rechnung " & invoiceNo
    mail.Body = "Guten Tag," & vbCrLf & vbCrLf & "anbei erhalten Sie unsere Rechnung " & invoiceNo & "."
    mail.Attachments.Add path
    mail.Display                         ' .Send sends without showing the window
End Sub

Use this route when the API should send. Use the plain endpoint from step 3 when Excel or Access should store the file and send it itself. The SellerEmail setting must be an address on your verified domain. The email endpoint also exists for XRechnung (/v1/create/xrechnung/email) and Factur-X.

Import incoming supplier invoices

Since January 2025, German companies must accept e-invoices. Supplier invoices arrive as ZUGFeRD PDFs and XRechnung XML files in the mailbox. A folder watcher is too much for a VBA macro. A button is enough: "Eingangsrechnung prüfen" opens a file dialog, and the macro does the rest. The pipeline has three steps: validate, send the report back if the invoice is invalid, extract the data if it is valid.

1. Validate. Post the file to POST /v1/validate/zugferd/report or /v1/validate/xrechnung/report. The API checks the file against EN 16931 and the German rules. The response is a PDF validation report, and the header X-Invoice-Valid says if the invoice passed. The form field validationLanguage sets the language of the report, here German. If you only want the findings as JSON, use /v1/validate/zugferd without /report.
2. Reply with the report. If the invoice is invalid, email the PDF report to the supplier and stop. The report lists every violated rule with the field and the message. The supplier can fix the invoice without a phone call.
3. Extract. If the invoice is valid, post the file to POST /v1/extract/json. The API reads the embedded XML, or the XRechnung XML, and returns it as JSON in the same shape as the create request.

The upload endpoints take multipart/form-data with one file field named file. VBA has no helper for that, so the macro builds the body itself. The body is three parts in one binary stream: the text header of the form fields, the bytes of the file, and the closing boundary. ADODB.Stream does the work. One stream in binary mode collects the parts. A second stream in text mode turns the text parts into UTF-8 bytes.

' modInvoiceXml: file upload as multipart/form-data with one field named "file"
Public Function PostFile(path As String, filePath As String, Optional language As String = "") As ApiResult
    Const BOUNDARY As String = "----InvoiceXmlVba8f3a1c9d"
    Dim fileName As String
    fileName = Mid$(filePath, InStrRev(filePath, "\") + 1)
    Dim contentType As String
    contentType = IIf(LCase$(Right$(fileName, 4)) = ".xml", "application/xml", "application/pdf")

    ' The text before the file: the optional validationLanguage field, then the file part header
    Dim head As String
    If Len(language) > 0 Then
        head = "--" & BOUNDARY & vbCrLf & _
               "Content-Disposition: form-data; name=""validationLanguage""" & vbCrLf & vbCrLf & _
               language & vbCrLf
    End If
    head = head & "--" & BOUNDARY & vbCrLf & _
           "Content-Disposition: form-data; name=""file""; filename=""" & fileName & """" & vbCrLf & _
           "Content-Type: " & contentType & vbCrLf & vbCrLf
    ' The text after the file: the closing boundary
    Dim tail As String
    tail = vbCrLf & "--" & BOUNDARY & "--" & vbCrLf

    ' One binary stream: text bytes, file bytes, text bytes
    Dim body As Object
    Set body = CreateObject("ADODB.Stream")
    body.Type = 1                        ' adTypeBinary
    body.Open
    body.Write Utf8Bytes(head)
    body.Write ReadBytes(filePath)
    body.Write Utf8Bytes(tail)
    body.Position = 0

    Dim http As Object
    Set http = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    http.Open "POST", BASE_URL & path, False
    http.setRequestHeader "Authorization", "Bearer " & Setting("ApiKey")
    http.setRequestHeader "Content-Type", "multipart/form-data; boundary=" & BOUNDARY
    http.send body.Read                  ' the whole body as a byte array
    body.Close
    PostFile = ReadResult(http)
End Function

Private Function ReadBytes(filePath As String) As Byte()
    Dim stm As Object
    Set stm = CreateObject("ADODB.Stream")
    stm.Type = 1
    stm.Open
    stm.LoadFromFile filePath
    ReadBytes = stm.Read
    stm.Close
End Function

' Text to UTF-8 bytes. The stream puts a 3-byte order mark in front. Skip it.
Private Function Utf8Bytes(text As String) As Byte()
    Dim stm As Object
    Set stm = CreateObject("ADODB.Stream")
    stm.Type = 2                         ' adTypeText
    stm.Charset = "utf-8"
    stm.Open
    stm.WriteText text
    stm.Position = 0
    stm.Type = 1
    stm.Position = 3
    Utf8Bytes = stm.Read
    stm.Close
End Function

The button macro ties it together. It validates, saves the report, and either opens the report mail or reads the data:

' modInvoiceImport: the button "Eingangsrechnung prüfen"
Option Explicit

Public Sub CheckIncomingInvoice()
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)   ' 3
    fd.Title = "Eingangsrechnung wählen"
    fd.Filters.Clear
    fd.Filters.Add "E-Rechnung (ZUGFeRD, XRechnung)", "*.pdf; *.xml"
    fd.AllowMultiSelect = False
    If fd.Show <> -1 Then Exit Sub
    Dim filePath As String, fileName As String
    filePath = fd.SelectedItems(1)
    fileName = Mid$(filePath, InStrRev(filePath, "\") + 1)

    ' 1. Validate. PDF files are ZUGFeRD, XML files are XRechnung.
    Dim endpoint As String
    endpoint = IIf(LCase$(Right$(fileName, 4)) = ".xml", _
                   "/v1/validate/xrechnung/report", "/v1/validate/zugferd/report")
    Dim res As ApiResult
    res = PostFile(endpoint, filePath, "de")
    If res.Status <> 200 Then
        MsgBox "Prüfung fehlgeschlagen (HTTP " & res.Status & ")." & vbCrLf & res.Text, vbExclamation
        Exit Sub
    End If
    Dim reportPath As String
    reportPath = InvoiceFolder() & "Pruefbericht-" & fileName & ".pdf"
    SaveBytes res.Body, reportPath

    ' 2. Invalid: offer to email the PDF report to the supplier, then stop
    If Not res.Valid Then
        If MsgBox("Die Rechnung " & fileName & " ist ungültig. Prüfbericht an den Lieferanten senden?", _
                  vbYesNo + vbExclamation) = vbYes Then
            SendReport reportPath, fileName
        End If
        Exit Sub
    End If

    ' 3. Valid: read the data and write the row
    res = PostFile("/v1/extract/json", filePath)
    If res.Status <> 200 Then
        MsgBox res.Text, vbExclamation
        Exit Sub
    End If
    WriteIncomingRow res.Text, filePath
    MsgBox "Rechnung " & JsonValue(res.Text, "invoiceNumber") & " wurde übernommen.", vbInformation
End Sub

' The macro does not know the supplier's address. The mail opens, the user adds it and clicks Send.
Private Sub SendReport(reportPath As String, fileName As String)
    Dim ol As Object, mail As Object
    Set ol = CreateObject("Outlook.Application")
    Set mail = ol.CreateItem(0)
    mail.Subject = "Ihre Rechnung " & fileName & " konnte nicht verarbeitet werden"
    mail.Body = "Guten Tag," & vbCrLf & vbCrLf & _
        "die angehängte Prüfung zeigt die Fehler in Ihrer Rechnung. " & _
        "Bitte senden Sie uns eine korrigierte Rechnung." & vbCrLf & vbCrLf & "Mit freundlichen Grüßen"
    mail.Attachments.Add reportPath
    mail.Display
End Sub

Plain PDFs: some suppliers still send PDFs without XML. Post those to POST /v1/parse/json with the same PostFile function. The API reads typed, scanned and photographed invoices with AI and returns the same JSON plus a confidence object with scores between 0.0 and 1.0. The macro can write high scores directly and mark the rest for a check. Files that are not invoices are rejected with error code 4008.

Write the incoming invoice from the JSON

The JSON from both endpoints has the same shape. The macro needs a handful of fields from it: the invoice number, the issue date, the supplier's name and VAT id, and the totals. VBA cannot parse JSON on its own. ScriptControl can, but it exists only in 32-bit Office, so the macro does not use it. The function below is about 30 lines. It finds a key in the text and reads the string or number behind it. The optional afterKey argument starts the search after another key, so JsonValue(json, "vatIdentifier", "seller") reads the seller's VAT id and not the buyer's. This is a small helper for a known JSON shape, not a JSON parser. It is enough for the fields in this guide.

' modInvoiceImport: reads one value from the JSON the API returns
Public Function JsonValue(json As String, key As String, Optional afterKey As String = "") As String
    Dim p As Long, q As Long
    p = 1
    If Len(afterKey) > 0 Then p = InStr(json, """" & afterKey & """:")
    If p = 0 Then Exit Function
    p = InStr(p, json, """" & key & """:")
    If p = 0 Then Exit Function
    p = p + Len(key) + 3
    Do While Mid$(json, p, 1) = " ": p = p + 1: Loop

    If Mid$(json, p, 1) = """" Then
        ' A string: runs to the next quote that is not escaped
        q = p + 1
        Do While q <= Len(json) And (Mid$(json, q, 1) <> """" Or Mid$(json, q - 1, 1) = "\")
            q = q + 1
        Loop
        JsonValue = JsonUnescape(Mid$(json, p + 1, q - p - 1))
    Else
        ' A number: runs to the first character that is not part of a number. null gives ""
        q = p
        Do While q <= Len(json) And InStr("0123456789.-", Mid$(json, q, 1)) > 0: q = q + 1: Loop
        JsonValue = Mid$(json, p, q - p)
    End If
End Function

' Undoes the escapes the API may use: \uXXXX for umlauts, \" \n \/ and \\
Private Function JsonUnescape(s As String) As String
    Dim p As Long
    p = InStr(s, "\u")
    Do While p > 0
        s = Left$(s, p - 1) & ChrW$(CLng("&H" & Mid$(s, p + 2, 4) & "&")) & Mid$(s, p + 6)
        p = InStr(p + 1, s, "\u")
    Loop
    s = Replace(s, "\""", """")
    s = Replace(s, "\n", vbLf)
    s = Replace(s, "\/", "/")
    JsonUnescape = Replace(s, "\\", "\")
End Function

' Excel: one row per incoming invoice on the sheet Eingang
Private Sub WriteIncomingRow(json As String, filePath As String)
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Eingang")
    Dim r As Long
    r = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
    ws.Cells(r, 1).Value = JsonValue(json, "invoiceNumber")
    ws.Cells(r, 2).Value = CDate(JsonValue(json, "issueDate"))
    ws.Cells(r, 3).Value = JsonValue(json, "name", "seller")
    ws.Cells(r, 4).Value = JsonValue(json, "vatIdentifier", "seller")
    ws.Cells(r, 5).Value = Val(JsonValue(json, "grandTotalAmount", "totals"))
    ws.Cells(r, 6).Value = Val(JsonValue(json, "duePayableAmount", "totals"))
    ws.Cells(r, 7).Value = filePath
End Sub

' Access: one record in tblEingangsrechnungen, with the supplier found by VAT id
Private Sub WriteIncomingRow(json As String, filePath As String)
    Dim vatId As String
    vatId = JsonValue(json, "vatIdentifier", "seller")
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("tblEingangsrechnungen", dbOpenDynaset)
    rs.AddNew
    rs!SupplierID = DLookup("SupplierID", "tblSuppliers", "VatId = '" & vatId & "'")   ' Null if unknown
    rs!InvoiceNumber = JsonValue(json, "invoiceNumber")
    rs!InvoiceDate = CDate(JsonValue(json, "issueDate"))
    rs!SupplierName = JsonValue(json, "name", "seller")
    rs!SupplierVatId = vatId
    rs!GrandTotal = Val(JsonValue(json, "grandTotalAmount", "totals"))
    rs!DuePayable = Val(JsonValue(json, "duePayableAmount", "totals"))
    rs!SourceFile = filePath
    rs!NeedsReview = IsNull(rs!SupplierID)
    rs.Update
    rs.Close
End Sub

Two limits of the helper, stated plainly. If the seller block has no VAT id, JsonValue(json, "vatIdentifier", "seller") finds the buyer's, so compare the value with your supplier list before you trust it. And a string that contains an escaped backslash right before its closing quote is read one character too far. Supplier names and invoice numbers do not contain that. Val reads the amounts with a period, so the German decimal separator does not get in the way. The row in tblEingangsrechnungen is the draft: accounts payable looks at it, assigns the account and books it.

XRechnung for German public buyers

Invoices to German federal, state and municipal buyers must be XRechnung, not ZUGFeRD. XRechnung is pure XML with no PDF. Portals such as ZRE and OZG-RE validate it against the KoSIT rules. The same macro creates it. You need a third endpoint and a few more fields. Add a cell LeitwegID on the sheet Rechnung (or fill it from the customer sheet with a lookup), or a column LeitwegID on tblCustomers. Keep it a text cell: Leitweg-IDs have leading zeros and dashes. Add the settings SellerContact and SellerPhone.

' In BuildInvoiceJson, when LeitwegID is filled. buyerReference is already written by OptionalField.
' The seller gets a contact and an electronic address:
",""seller"":{""name"":" & JsonEscape(Setting("SellerName")) & _
    ",""vatIdentifier"":" & JsonEscape(Setting("SellerVatId")) & _
    ",""postalAddress"":{""line1"":" & JsonEscape(Setting("SellerStreet")) & _
        ",""city"":" & JsonEscape(Setting("SellerCity")) & _
        ",""postCode"":" & JsonEscape(Setting("SellerPostCode")) & _
        ",""country"":" & JsonEscape(Setting("SellerCountry")) & "}" & _
    ",""contact"":{""name"":" & JsonEscape(Setting("SellerContact")) & _
        ",""phone"":" & JsonEscape(Setting("SellerPhone")) & _
        ",""email"":" & JsonEscape(Setting("SellerEmail")) & "}" & _
    ",""electronicAddress"":{""identifier"":" & JsonEscape(Setting("SellerVatId")) & _
        ",""schemeId"":""9930""}}" & _

' The buyer gets an electronic address too, after its postalAddress. 9930 = German VAT id.
    ",""electronicAddress"":{""identifier"":" & JsonEscape(ws.Range("CustomerVatId").Value) & _
        ",""schemeId"":""9930""}}" & _

' Then post to /v1/create/xrechnung. The response is XML, not a PDF.
res = PostJson("/v1/create/xrechnung", "{""invoice"":" & BuildInvoiceJson() & "}")
SaveBytes res.Body, InvoiceFolder() & ws.Range("InvoiceNumber").Value & ".xml"

The Leitweg-ID is the routing id the public buyer gives you when you register as a supplier. Rule BR-DE-15 rejects invoices without it. The seller contact (name, phone, email) and the electronic addresses are required by the German rules. Scheme 9930 means a German VAT id. For Peppol delivery, use the Peppol participant id and its scheme instead.

The API returns the XML, validated against the KoSIT rules. Deliver it the way the buyer requires: upload to ZRE or OZG-RE, Peppol, or email where the buyer accepts it. /v1/render/xrechnung/to/pdf turns the XML into a readable PDF for your own records. Post the XML file with PostFile and save the result with SaveBytes. XRechnung is also valid for B2B invoices, so the /email route works for it too. The XRechnung API toolkit covers the format in detail.

64-bit Office, macro security and the Trust Center

64-bit Office. The code runs on 32-bit and 64-bit Office without changes. It creates MSXML2.ServerXMLHTTP.6.0, ADODB.Stream and Outlook.Application with CreateObject, and these objects exist in both bitnesses. There is no Declare statement for a Windows API function, so no PtrSafe keyword and no LongPtr type is needed. ScriptControl is the one object that exists only in 32-bit Office, and this guide does not use it.

Macro security. A workbook from a mail or a download carries the Mark of the Web, and Office blocks its macros. Keep the workbook or database in a trusted location (File, Options, Trust Center, Trust Center Settings, Trusted Locations), or unblock it once in the file properties. For a file that several people use, sign the VBA project: Tools, Digital Signature in the VBA editor. SelfCert from the Office folder creates a certificate for use inside one company. Users then trust the publisher once, and the macros run without a warning.

Proxy. ServerXMLHTTP uses the WinHTTP proxy settings, not the proxy of the browser. If your company has a proxy, set it once on the PC with netsh winhttp set proxy proxy.firma.local:8080, or call http.setProxy 2, "proxy.firma.local:8080" right after Open.

Access without Excel objects. Application.FileDialog and CreateObject("Outlook.Application") work in Access the same way. The only Excel-specific pieces are the named cells and the ListObject, and the Access module replaces them with recordsets. The shared module modInvoiceXml can be exported from the workbook and imported into the database as it is.

Endpoint reference

Get started

The whole integration is three modules and two buttons. Store the key, map the cells or the tables, post the JSON, save or email the file, and check what comes in. No format logic in the workbook. Nothing to update when the formats change. This guide is part of the integrations hub, which also covers Google Sheets, Business Central, Dynamics NAV, SAP and other platforms.

Related guides:

• E-invoicing from Google Sheets: the same integration in Apps Script, with Drive and Gmail.
• PDF to ZUGFeRD conversion: when the PDF already exists and only the XML is missing.
• ZUGFeRD API toolkit: the format, the profiles and the endpoints in detail.
• XRechnung API toolkit: Leitweg-ID, KoSIT validation, PDF preview.