Mailmerge current record and save as new document

[CrisMonty]

Hi all,

I'm looking to add a button to my Customers form which will mailmerge the current record to a Word template and then save the Word doc as a new file (Ideally the customer's name).

I've looked at the Super Easy Mailmerge but I can't work out how to implement it without all of the variables (selecting documents etc.).

The files will all be saved to one location (C:\Customers\Exports\) and this won't change.
This is also the location of the mailmerge template (C:\Customers\Exports\Template.docx)

If possible, can anybody provide some code that will do this for me? I'll be happy to provide any extra info that you might need. I'm a complete beginner with VBA so i'd really appreciate any help.

 

[Cronk]

If you were to do a Google search on, say,
vba ms access bookmarks in word

you would be offered a lot of good suggestions including
http://www.access-programmers.co.uk/forums/showthread.php?t=236469

Do a bit of reading and come back with any specific question you might have.

 

[CrisMonty]

Bookmarks is it? Drats, I was hoping it would be more like docmd.mailmerge. Wishful thinking.

Thank you for the link, I'll take a look.

 

[Reenen]

But why don't you just write a report, and export the resulting report to word?

 

[CrisMonty]

Can you do that and maintain all of the formatting? That does sound much easier

edit: I can only work out how to export it as Rich Text and it loses all of the formatting this way.

 

[Cronk]

I didn't say you had to use bookmarks. Just as you didn't say you did not want to use them.

Try a google search on vba ms access mailmerge in word

I've used both methods depending on requirements.

 

[mh123]

The easiest way I found to set up mailmerges is to set up the word document that links to the database then in VBA just set that to open the word document (making sure all alerts are on) then you could saveas the file where it needs to be with VBA

HTH if you need more input or some help on what code to put in let me know but do try get there on your own first!

 

[CrisMonty]

I've been trying it with bookmarks with some degree of success. Still getting the hang of them though.

I found a piece of code on vba express (all credit goes to the author as I barely know what is going on in it):

Sub ExportQueryToWord()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim doc As Word.Document
Dim MyWord As Word.Application
Dim FirstName As String
Dim LastName As String
Dim fso As Object
Dim strFolder As String
'Check to see if folder exists
strFolder = '<<<Place the filepath to the folder that contains your word doc
Set fso = CreateObject("Scripting.FileSystemObject")
.create the folder if it doesn't exist
If Not fso.FolderExists(strFolder) Then
fso.CreateFolder (strFolder) '<<<Create folder if not exist
End If
Set db = CurrentDb '<<<Opens connection to current database
'create a connection to the query that shows the record you want to work with
Set rst = db.OpenRecordset("QueryName")
Set MyWord = New Word.Application
rst.MoveFirst '<<<Implicity move to first record in query
Do Until rst.EOF
'document that you want to push records to that contains bookmarks
Set doc = MyWord.Documents.Open("C:\TestDoc.doc")
MyWord.Visible = False '<<<works without showing the document
FirstName = rst!First_Name '<<<Grab first name from recordset as variable value
LastName = rst!Last_Name '<<<Same for last name
'paste variables into word document
doc.Bookmarks("FirstName").Range.Text = FirstName
doc.Bookmarks("LastName").Range.Text = LastName
'save and close document
doc.SaveAs (strFolder & "Name You Want Document To Have.doc")
doc.Close wdDoNotSaveChanges
rst.MoveNext
Loop
MsgBox "All letters have been created."
'quit word and release connections
MyWord.Quit
Set MyWord = Nothing
rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
End Sub

I've been trying to adapt it so that it just uses the current record on a form and in general get it to work for me. I'm trying to make the Query it's based on show only the current record in the form by making the criteria of the ID = forms!Customers!ID.

The Query works when the form is open but at the moment the code just crashes. I'll keep trying.

@mh123: I'm trying to make it all as fool-proof as possible so ideally, 1 click and it's done is my aim.

 

[Cronk]

Depending on the requirements, I output using Access reports, Word mailmerge (for complex formatted docs) or using bookmarks where I need to control the output depending on the data.

For example, setting up for group headers in an Access report is straight forward but a Word Mailmerge would not enable that. However, VBA coding in Access can output such to a Word document.

I've used bookmarks to generate a single document containing letters to multiple recipients, all printed portrait but some with landscape attachments.

Does the code you provided compile? If it's crashing during run time, what line is causing the problem?

Incidentally, if you are outputting from a button on your form, you don't need to open a recordset. Just insert the values of the controls.
eg

doc.Bookmarks("FirstName").Range.Text= me.txtFirstName

(Incidentally in case you didn't know, I selected to line of code and clicked on the # button at the tip of the message box.)

 

[CrisMonty]

I finally got it to work with a mix of the code above and

doc.Bookmarks("FirstName").Range.Text= me.txtFirstName

I think I even understand what the code does now. Thank you all for your help.