Issue with sending current record information from Access to Word (mailmerge) using VBA

adibakale

Board Regular
Joined
Apr 10, 2015
Messages
52
I am creating an Access database for the department I work in. When reviewing orders on a form, there are certain instances were we need to mail a letter out to the customer. I have a command button that opens up a word form and inserts address info from the customer table in access to the word document in this format:

Name
Address
City, State, Zip.

I am coming across an issue were only the City, State, Zip remain in the word document after the VBA is finished. I can see it while its processing and it does insert the Name and Address, but for some reason it deletes the Name and Address leaving only the City, State, Zip. I am having difficulty figuring out why this is happening and how to prevent it. I tried using this code to fix the issue, but the code seems to stop after it reaches this:

.ActiveDocument.Bookmarks("Name").Select
.Selection.Text = (CStr(Forms!frm_Main!CHName))

'Add this line to reapply the bookmark name to the selection.
.ActiveDocument.Bookmarks.Add Name:="Last",Range:=Selection.Range


Here is my VBA Code, any help with this would be greatly appreciated.


Private Sub cmdElan_Click()
On Error GoTo MergeButton_Err
Dim objWord As Word.Application
'Copy the Photo control on the frm_Main form.
'DoCmd.GoToControl "Photo"
'DoCmd.RunCommand acCmdCopy
'Start Microsoft Word 97.
Set objWord = CreateObject("Word.Application")
With objWord
'Make the application visible.
.Visible = True
'Open the document.
.Documents.Open ("C:\Users\c015892\Desktop\Elan.doc")
'Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("Name").Select
.Selection.Text = (CStr(Forms!frm_Main!CHName))
.ActiveDocument.Bookmarks("Address").Select
.Selection.Text = (CStr(Forms!frm_Main!CHStreet))
.ActiveDocument.Bookmarks("City").Select
.Selection.Text = (CStr(Forms!frm_Main!CHCity))
.ActiveDocument.Bookmarks("State").Select
.Selection.Text = (CStr(Forms!frm_Main!CHState))
.ActiveDocument.Bookmarks("Zipcode").Select
.Selection.Text = (CStr(Forms!frm_Main!CHZip))
'.ActiveDocument.Bookmarks("Greeting").Select
'.Selection.Text = (CStr(Forms!frm_Main!FirstName))
'Paste the photo.
'.ActiveDocument.Bookmarks("Photo").Select
'.Selection.Paste
End With
'Print the document in the foreground so Microsoft Word will not close
'until the document finishes printing.
'objWord.ActiveDocument.PrintOut Background:=False
'Close the document without saving changes.
'objWord.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
'Quit Microsoft Word and release the object variable.
'objWord.Quit
'Set objWord = Nothing
'Exit Sub
MergeButton_Err:
'If a field on the form is empty, remove the bookmark text, and
'continue.
If Err.Number = 94 Then
objWord.Selection.Text = ""
Resume Next
'If the Photo field is empty.
'ElseIf Err.Number = 2046 Then
'MsgBox "Please add a photo to this record and try again."
'Else
'MsgBox Err.Number & vbCr & Err.Description
End If
Exit Sub
End Sub
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,460
Members
414,069
Latest member
StudExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top