Mail Merge To All Customers Using VBA Code

[marky_dp]

Hi all,

As the title suggestes I would like to write some code, that on a click of a button on a form, a mail merge letter is created in Word that includes all of the customers currently held in the db.

I've searched the forum already but haven't really found anything that appears straightforward.

Regards and thanks in advance

 

[KeithG]

You will have to first create the template document for the mail merge. Then if your buttons click event write the code to open up the document connect to the data source and print/email your document. Something like this

dim msWrd as New Word.Application
dim msDoc as Word.Document
dim msMailMerge as Word.MailMerge

set msDoc=msWrd.open(PathOfFile)

set msMailMerge=msDoc.mailmerge

msMailMerge.datasource=Connection to Database

.......


The code in your button should look similar the the above code.

 

[marky_dp]

Heres some of the code I have been using, that creates a single letter, based on a pre-made template.

Private Sub MergeButton_Click()

'Variables for holding strings (text)
Dim AddressLine, Dear As String

'Start building address line by dealing with blank last name and company fields
If IsNull([CustomerLastName]) Then

AddressLine = vbCrLf + [CustomerCompanyName]
'Set Last Name to dear sir/madam
Dear = "Sir/Madam"

Else

AddressLine = vbCrLf + [CustomerFirstName] + " " + [CustomerLastName]

'If company name exsits, add that on after customers name
If Not IsNull([CustomerCompanyName]) Then

AddressLine = AddressLine + vbCrLf + [CustomerCompanyName]

End If

'Make Dear = Customer first name instead
Dear = "Dear " + [CustomerFirstName] + "," + vbCrLf

End If

'Add a line break and then address line 1
AddressLine = AddressLine + vbCrLf + [CustomerAddressLine1]

'If address line 2 isnt null, add line break and line 2
If Not IsNull([CustomerAddressLine2]) Then

AddressLine = AddressLine + vbCrLf + [CustomerAddressLine2]

End If

'If address line 3 isnt null, add line break and line 3
If Not IsNull([CustomerAddressLine3]) Then

AddressLine = AddressLine + vbCrLf + [CustomerAddressLine3]

End If

'If address line 4 isnt null, add line break and line 4
If Not IsNull([CustomerAddressLine4]) Then

AddressLine = AddressLine + vbCrLf + [CustomerAddressLine4]

End If

'If address postcode isnt null, add line break and postcode
If Not IsNull([CustomerAddressPostCode]) Then

AddressLine = AddressLine + vbCrLf + [CustomerAddressPostCode] + vbCrLf

End If

'Declare a new instance of microsoft word
Dim word As New word.Application
Set word = CreateObject("Word.Application")

'Specify path of template letter
Dim MergeDoc As String
MergeDoc = Application.CurrentProject.Path
MergeDoc = MergeDoc + "\AshbyEmporiumIndividualCustomerTemplate.dot"

'Open the document template and make it visible on screen
word.Documents.Add MergeDoc
word.Visible = True

'Replace Each Book Mark in word document with current data
With word.ActiveDocument.Bookmarks

.Item("Date").Range.Text = Date
.Item("AddressLines").Range.Text = AddressLine
.Item("Recipient").Range.Text = Dear

End With

'Print the now merged letter
word.ActiveDocument.PrintOut

'All finished, just close everything
'Word.ActiveDocument.Close wdDoNotSaveChanges
'Word.Quit

End Sub


This code adds only a single customer's details to the letter template. The customer that it adds to the letter template, is the customer that is displayed on the form when the 'Merge' button is pressed.

This is working fine and doesn't produce any dialogue boxes when Word is opened. All Good!

Would it be possible to adapt this code in some way so that it emails all of the customers in the database, rather than just the one onscreen when the merge button is clicked?

Regards and thanks again?