VBA - exporting serial letters to single documents

Oct 14, 2021
Reaction score

I´ve been working on a method to create serial letters with data from an Excel Sheet.
I want a new folder to be created when exporting the serial letters into single documents.
The creation of the folder works pretty good, the folder is being created as and where I want it to be.
What doesn´t really work is saving the documents.
I only want them to be saved in the new folder if the datafields first name ("Vorname") and surname ("Nachname") are filled.
At the moment, Word shows an error message and creates a new .doc, which is not being saved in the folder, having no name.

Can someone tell me what´s the error?

Thank you so much! Very best wishes,



Here I can show you what I´ve written so far:

Sub briefe_erstellen()
'create_letters ()

'define string for folderpath
Dim strFolderPath As String
Dim Datum As String
strDatum = Format(Date, "JJJJ-MM-TT")
strFolderPath = "C:\Users\equi\Documents\Divers\XX\bill_backup_" & Format(Date, "YYYY-MM-DD")
' Check if folder already exists
If Dir(strFolderPath, vbDirectory) = "" Then
' Create folder
MkDir (strFolderPath)
MsgBox "Folder has been created!"
MsgBox"Folder is available"
End If

' "Nachname is surname"
' "Vorname is first name"

Dim nNachname As String
Dim nVorname As String

Dim Pfad As String

nNachname = "Nachname" 'Is a series field for me and then gives the file names

nVorname = "Vorname" 'Is a series field for me and then gives the file names

'Pfad = Path

Pfad = "C:\Users\equi\Documents\Divers\XX\bill_backup_" & Format(Date, "YYYY-MM-DD")

'Hide application for better performance
MsgBox "Mail merge letters are exported. This process may take a few minutes - Microsoft Word is hidden during this time", vbOKOnly + vbInformation
Application.Visible = False

With ActiveDocument.MailMerge
.DataSource.ActiveRecord = wdLastRecord
anzahl = .DataSource.ActiveRecord
flag = False
For Each x In .DataSource.DataFields
If x.Name = nEmail Then
flag = True
Exit For
End If
.Destination = wdSendToNewDocument
For i = 1 To anzahl
.DataSource.ActiveRecord = i
dsname = Pfad & "\" & _
.DataSource.DataFields(nVorname).Value & "_" & .DataSource.DataFields(nNachname).Value & "_" & Format(Date, "YYYY-MM-DD") & "_Rechnung.docx"
.DataSource.FirstRecord = i
.DataSource.LastRecord = i
ActiveDocument.Range.Find.Execute findtext:="^b", replacewith:=""
Set fs = CreateObject("Scripting.FileSystemObject")

ActiveDocument.SaveAs FileName:=dsname, AddToRecentFiles:=False

Next i

End With
End Sub

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question