Create Letters.doc in word with bookmarks in excel

rockdrigotoca

New Member
Joined
Aug 24, 2010
Messages
23
Hi everyone!!

I'm trying to create Word documents from Excel fields.

I already created bookmarks in Word and have the same Titles in A Row in Excel 2003 and I have tried the following threads:

http://www.mrexcel.com/forum/showthread.php?t=451429&highlight=excel+code+word+bookmark
http://www.mrexcel.com/forum/showthread.php?t=428862&highlight=excel+code+word+bookmark

But I cannot manage it properly. I know that I need the code that goes on the lines of Set WdApp = CreateObject("Word.Application")
rather than Set WdApp = New.Object
I cannot get Word to open a document! =(

It's basic letter creation, First_Name, 2_Name, Address_1, Address_2...
These have been bookmarked in Word and have exact same Titles in Columns A-D

Could anyone please help me???
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
You will find an example of how to do this if you look at my contribution to the following post:
http://www.mrexcel.com/forum/showthread.php?t=478182

Note that #4 describes the creation of an example Word document and Excel workbook and #5 contains the VBA that does the work.

My code will save and close the Word document - if you want to leave it open you will need to change those lines and add something like "oDoc.Visible = True" (but I have not tested that).
 
Upvote 0
Hi Derek!

First, thanks for your response!

Second, I actually saw your thread before posting mine, though I cannot quite get it to work... (maybe because I have Excel 2003??)
From your code, there are a few tweaks I need to change
e.g.
Your Dim oApp As Word.Application has to change to:
Dim WdApp as Workbook
then
Set WdApp = GetObject(, "Word.Application").

Anyhow, I tried to do something around it with the help from your thread and others but I cannot manage to extend it to all the data set I have... it will only produce the first letter in Word but not the others... could you please see if you can help me?

The thread I posted that into is:

http://www.mrexcel.com/forum/showthread.php?t=491852


Kind regards,
 
Upvote 0
Not sure why you need "Dim WdApp as Workbook" - that does not make sense unless you have not set a 'Reference' (Tool menu) to the Microsoft Word Object nn.n Library.
I will need to look back through my old code listings to find the other answer for you - I have the code somewhere to use a 'template' to create a Word document containing multiple letters.
 
Upvote 0
Here is the code that may provide the solution for you.
In this example, worksheet "Merge Data" contains the headings that match the bookmarks and the data to be merged; worksheet "Control Sheet" contains the path of the Word document in B1 and the document name in B2. A1 and A2 would just have headings that indicate what is in B1 and B2.
Code:
Option Explicit
Sub MailMerge()
'Word application objects declaration
' Important:
' Set Reference (Tools menu) to Microsoft Word nn.n Object Library
Dim oApp      As Word.Application
Dim oDoc      As Word.Document
Dim oTemplate As Word.Document
Dim oBookMark As Word.Bookmark
 On Error GoTo HandleError
' Excel application objects declaration
Dim wb As Workbook
Dim ws As Worksheet
Dim wsControl As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim objX As Object
Dim strDocName As String
Dim strPathName As String
Dim lngKount As Long
Dim lngRecordKount As Long
Dim strFileName As String
'
     Application.ScreenUpdating = False
     Set wb = ThisWorkbook
     Set ws = wb.Worksheets("Merge Data")
     Set wsControl = wb.Worksheets("Control Sheet")
'Set data range
    ' Records in Column A, excluding heading
     Set rng = ws.Range(ws.Cells(2, 1), ws.Cells(65536, 1).End(xlUp))
     lngRecordKount = rng.Rows.Count
' Get location of WORD document:
     strPathName = wsControl.Range("B1").Value
     strDocName = wsControl.Range("B2").Value
     If ((strDocName = "") Or (strDocName = " ")) Then
        ' use document with same name as this workbook:
         strDocName = Left(wb.Name, Len(wb.Name) - 4) & ".doc"
     End If
     If ((strPathName = "") Or (strPathName = " ")) Then
         ' use same path as this workbook:
         strPathName = wb.Path
     End If
     strFileName = strPathName & "\" & strDocName
' Check that file exists:
     If Dir(strFileName) = "" Then
         MsgBox strFileName & vbCrLf & "cannot be found", vbOKOnly + vbCritical, "Error"
                GoTo HandleExit
    End If
'Create new word application
     Application.StatusBar = "Starting Microsoft Word"
     Set oApp = CreateObject("Word.Application")
'Create new document
     Application.StatusBar = "Creating new Word Document"
     Set oDoc = oApp.Documents.Add
'Loop in data range
     lngKount = 1
     For Each rng2 In rng.Cells
         Application.StatusBar = "Creating document " & lngKount & " of " & lngRecordKount
        'Insert requested file/template into the main document
         oApp.Selection.InsertFile strFileName
        'Find all bookmarks and replace with data:
         For Each oBookMark In oDoc.Bookmarks
             'Corresponding header found in first row ?
             Set objX = ws.Rows(1).Find(oBookMark.Name, LookIn:=xlValues, LookAt:=xlWhole)
             If Not objX Is Nothing Then
                ' heading found for bookmark
                 oBookMark.Range.Text = rng2.Offset(, objX.Column - 1).Value
            Else
                 MsgBox "Error - Bookmark '" & oBookMark.Name & "' not found in " & vbCrLf & _
                    "[" & wb.Name & "]!" & wsControl.Name & vbCrLf & vbCrLf & _
                    "Please check that all bookmarks exist as headings", vbOKOnly, "Bookmark Error"
                 oApp.Quit
                 GoTo HandleExit
            End If
        Next oBookMark
        'Goto next page
        'wdPageBreak = 7
         oApp.Selection.InsertBreak 7
         lngKount = lngKount + 1
    Next rng2
'
    MsgBox "Process complete - please check result", vbOKOnly + vbInformation, "Merge Complete"
    oApp.Visible = True
HandleExit:
     On Error Resume Next
     Application.StatusBar = ""
     Application.ScreenUpdating = True
     Set oDoc = Nothing
     Set oTemplate = Nothing
     Set oBookMark = Nothing
     Set wb = Nothing
     Set ws = Nothing
     Set wsControl = Nothing
     Set rng = Nothing
     Set rng2 = Nothing
     Set objX = Nothing
'
    Set oApp = Nothing
'
    Exit Sub
'
HandleError:
    ' Do something here?
    Resume HandleExit
End Sub
You will need to add some error-trapping etc.
This will work for Excel 2003, 2007 and 2010.
The Word document (template) is 'inserted' into a new document for each record for which the letter is required.
The Word document remains visible but unsaved at the end of processing but you can change that of course.
 
Upvote 0
Hi Derek!!

Wicked!!! It works and it send the info to Word!!! Although I think i'm doing something wrong or Word is because if I have 3 sets of data in Excel, it just adds it up to the "bookmarks" in page 1 of Word but it does create 3 pages...

I'm only using names... so I have Rodrigo, Loretta, Adriana
In Word, I have "Your name is: []" (the [] is the bookmark)

So I get 3 pages, the first says "Your name is: RodrigoLorettaAdriana"
And the remaining 2 just say "Your name is:"
I'm sure I must be doing something wrong or Word is... jeje

I'll try to fix it but I really appreciate your help!!! It has been actually very helpful and now I understand more things about VBA!

Cheers!
:LOL:
 
Upvote 0
What you should have in your Excel workbook is, for example:
A1: NameColumn
A2: Rodrigo
A3: Loretta
A4: Adriana
In the Word document I would put:
Your name is: NameColumn
and then highlight "NameColumn" and add the bookmark as "NameColumn".
This should then work. The names need to be in columns and you would then use columns B, C, D etc. for other data for each letter.
You may have problems when you come to use dates, currency etc. and I overcome this by using a 'suffix' to the column header and bookmarks - for example "FromDate", PaymentAmount" and then I would include the following code:
Code:
If Right(oBookMark.Name, 4) = "Date" Then
	oBookMark.Range.Text = Format(cll.Offset(, fnd.Column - 1).Value, "dd mmmm yyyy")
ElseIf Right(oBookMark.Name, 4) = "Rate" Then
	oBookMark.Range.Text = Format(cll.Offset(, fnd.Column - 1).Value, "0.00%")
ElseIf Right(oBookMark.Name, 7) = "Payment" Then
	oBookMark.Range.Text = Format(cll.Offset(, fnd.Column - 1).Value, "£#,##0.00")
ElseIf Right(oBookMark.Name, 6) = "Amount" Then
	oBookMark.Range.Text = Format(cll.Offset(, fnd.Column - 1).Value, "£#,##0.00")
Else
	oBookMark.Range.Text = cll.Offset(, fnd.Column - 1).Value
End If
 
Upvote 0
Excellent Derek!!

This is just perfect!!!

Thank you very very much!!!

I also added at the end:

PHP:
oApp.Application.PrintOut Copies:=1
            .Close savechanges:=False

So I can print without saving each document and then it it goes into the loop!
:)

Cheers!!

Rod
 
Upvote 0
This is some pretty heavy duty stuff for a beginner like me: is there a more "general" resource for this macro?

More specifically, I'd like to customize this for my own project, is there any way to demarcate the individual elements in that code?

Hope you can help!
 
Upvote 0

Forum statistics

Threads
1,215,055
Messages
6,122,902
Members
449,097
Latest member
dbomb1414

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
Back
Top