Solved

# VBA - Mail merge from Excel into a word letter,

Posted on 2014-11-18
Medium Priority
5,467 Views
Hi Guys,

I'm novice guys with a very little vba knowlegde.

I need some guidance in how and what the best possibilities are, and how it will be done best.
I have a workbook where i need each sheet to mail merge into a letter with a button from Excel, is this possible?
Would it be possible to embed the word letter file into the excel file or would it be needed to save the word letter file locally or at a network location?

The letters would need to be merge out from a few chosen columns, which might variate from sheet to sheet, and each sheet has its own letter format and doc file.

What is possible and could any1 kindly help me make this code?

How i see it would be like:

You open the excel workbook, go to each sheet to create its specific letter, click on a button called [Create Letter], the letters will get saved locally in c:/temp folder in its respectable folder named after what sheet it is.

here is the workbook i got help with earlier from Gowflow

Sample-file-19.11.2014-V06.xlsm
0
Question by:Hakum
• 9
• 7

LVL 77

Expert Comment

ID: 40452072
As it stands, your question is more like a project than a single question, so I will just advise you on the choice of approaches to use.

When Mail Merge runs it creates an output that uses the Main form like a template. It the output is to a document, that document no longer has the MailMerge fields, so cannot be used as an input to another Mail Merge. Mail Merge needs a single table such as a worksheet for the input data.

If your data is spread across various worksheets, you will need to get them into a singe source. You can either copy all the relevant data into a single worksheet or you can use SQL to create a query that will present the data in a table-like manner. For a novice, copying the relevant data into a single worksheet would be easier.

If you need it to be multi-stage as you suggest, then you would have to emulate the mail merge with a non-mail merge template, marking the target positions with Bookmarks or Content Controls and do the whole thing in code.

Don't embed the document into the workbook. it is a further complication.
0

LVL 1

Author Comment

ID: 40452094
Hi Graham,

Hope you are well!

I completely agree and understand you points! that is exactly why i have i got them into once source, i assume you haven't seen the workbook that i attached, there is already a code that splits the data into its respectable sheet, so this is not the case, each sheet is ready to be merged. as it is, it was more a nice to have feature if it was possible to choose which col's that will be merged, if not then this doesn't matter.

So what i'm looking for is pretty much a code that will merge in the col's in the sheets from fx. the sheet "Letter 1" into the word document called "Letter 1" which would be in a specific location, locally or on network location.

Is there any "easy" way of doing this? would it be maybe be easier to write the code in the Word Letter and telling it to look for the excel file?
0

LVL 1

Author Comment

ID: 40452100
would it be easier to merge with fieldcodes or bookmarks?
0

LVL 1

Author Comment

ID: 40452271
I tried to do some coding and i think i got pretty far or near what i was thinking but i need some help with the code...

As for now i got this far, but for some reason the cell data gets pasted as a table why so? and i cant really figure out how that each row will be created as its own letter and how to map each cell to its bookmarks.... kindly help.. i'm a total novice :S

Option Explicit

Sub CreateWordDoc1()

Dim wdapp As Object
Dim SaveName As String
Dim FileExt As String

Set wdapp = CreateObject("Word.Application")

With wdapp
'.Visible = True
'.Activate

Range("G2").Copy

.Selection.GoTo what:=-1, Name:="Regnr"
.Selection.Paste

FileExt = ".docx"

SaveName = "C:\Temp\Document\doc\Letter 1 " & _
Format(Now, "dd-mm-yyyy hh-mm-ss") & FileExt

.ActiveDocument.SaveAs SaveName

.ActiveDocument.Close
.Quit

End With

End Sub

Sample-file-19.11.2014-V06.xlsm
letter-1.dotx
0

LVL 77

Expert Comment

ID: 40453608
Sorry, I have been fighting a virus/adware attack.

I did look at your workbook, but I didn't get much from it. What is 'fx'?

In your next comment, it's not clear what you mean by field codes. Standard Mail Merge uses Mergefields (and others). If you employ other mechanisms, you could use bookmarks to mark the target positions. You could use (legacy) Form Fields. Another way would be to use Content Controls.

The MailMerge  'Letters' layout produces one letter per record (row). Your code tries to copy a whole column into the letter.

As I understand it,  you need to produce a letter for each row, but a with a different lyoutfor each of several sheets. For this, the MailMerge mechanism should work. You will need to produce one Main document for each relevant sheet.

Start with one, similar to your sample document and in Word, find Start Mail Merge on the Mailings tab. Choose 'Step by Step MailMerge Wizard...'. This opens a task pane on the right.

Step1
Select 'Letters'
then  'Next:  Starting document' (at the bottom of the pane)

Step2
Select 'Use the current document'
then  'Next:  Select recipients'

Step3
Select 'Use an existing list'
then 'Browse...'
Navigate to and open your workboo
Choose the correct sheet in the Select Table dialogue.
You will get the chance to avoid certain rows, in the Mail Merge Recipients dialogue but that should not be   necessary

Step 4
You can now edit or write the document. At this stage you can insert the merge fields that will say which column is to provide the data. Click the position in the document for the variable data and then on 'More items...'. Find the correct heading text and then 'Insert' and 'Close'. Do this for each field position in the letter.

Step 5
Preview shows how the letter will look with the data filled in

Step 6
Complete the merge. You can now Print the output or produce an output document.

After a few runs through, you might find it just as easy to do the job without the Wizard, using the button on the Mailings tab.

It is relatively easy to automate the merge, or a series of merges once the documents have been set upe, but we'll do that when we have managed to produce those documents.
0

LVL 1

Author Comment

ID: 40454418

So you say it would be easier to create the with mergefields rather then using bookmarks? i'm familiar with using mailmerge in general, so creating the letters is not a problem for me.

I will create the letters and get back asap..

im sick in bed today so will try by tomorrow.

Thank you for all the help so far :)
0

LVL 77

Expert Comment

ID: 40456525
Sorry to hear that were sick, and hope that you are better now. However I have had a chance to write a macro to show how to automate mail merge for a single letter.
The code uses 'Early Binding' because it is  easier to programme and runs slightly faster than 'Late Binding'. It enables the Intellisense facility of context-sensitive suggestions for object properties, but it does mean that you will have to set a Reference to the 'Microsoft Word nn.nn Library'.
Sub RunMerge()
Dim strMainDocName As String
Dim strResDocName As String
Dim docMain As Word.Document
Dim docResult As Word.Document
Dim strOutFolder As String
Dim strInfolder As String
Dim bNewInstance As Boolean
Dim objWord As Word.Application

'set file and folder paths.
strInfolder = "I:\Allwork\ee\28565297"
strOutFolder = "I:\Allwork\ee\28565297\Output"
strMainDocName = "letter-1 with fields.dotx"

'avoid creating multiple instances of the Word application
On Error Resume Next 'suppress error checking for the next instruction
Set objWord = GetObject(, "Word.Application")
On Error GoTo 0 'resume error checking

If objWord Is Nothing Then
'no instance, so make a new one
Set objWord = CreateObject("Word.Application")
bNewInstance = True
End If
objWord.Visible = True

strResDocName = Replace(strMainDocName, ".dotx", "_result.dotx")

' open the main document
Set docMain = objWord.Documents.Open(strInfolder & "\" & strMainDocName)

'do the merge
With docMain.MailMerge
.Execute
End With

'capture the result document into a specific document object variable
Set docResult = objWord.ActiveDocument
'closing and saving
docMain.Close wdDoNotSaveChanges
docResult.SaveAs strOutFolder & "\" & strResDocName
docResult.Close wdDoNotSaveChanges

'close application unless Word was already running
If bNewInstance Then
objWord.Quit
End If
End Sub

When you open a Word document that has already been set up to be a mail merge main document, you get a security warning message. This will prevent the code from running unattended, so you might want to edit the registry as this Microsoft article suggests:  http://support.microsoft.com/?kbid=825765
0

LVL 1

Author Comment

ID: 40461770
Hi Graham,

Sorry for the delayed answer but been really sick.. :(

This code is awesome and works like a charm although I have 2 requests.

1. is it possible to change the code to create the output folder is its not there?
2. is there any other way then making the key in the registry, i'm currently trying to find out of if we can roll the string out by a policy but if not, is there any alternative?

ps. Graham!! you rock!! thank soo much for cutting it out like this. i know i'm a big slow up there but i'm really learning here soo from bottom of my heart thank you for putting the time in! its really appriciated
0

LVL 1

Author Comment

ID: 40461952
one more thing..

I might have been too quick in the trigger... it does do the merge and all.. but i cant understand why i cant open the file
0

LVL 77

Expert Comment

ID: 40462062
Here is some simple code to check for a folder and to create it if necessary.
As it stands, it will only work for the deepest subfolder (FolderC). To create parent folders (Folder A an/ or FolderB) is possible, but needs something more complex.

If Dir("C:\FolderA\FolderB\FolderC", vbDirectory) = "" Then
MkDir "C:\FolderA\FolderB\FolderC"
End If

Registry editing is not done natively in VBA and requires using APIs. It is necessarily quite complex and might be blocked by security settings. I think that it would need you to post another question.

Which file can't you open?
0

LVL 1

Author Comment

ID: 40464022
Okay super! thank you for the code i will try to make it work :)

Regarding the registry setting, no worries i will try to work it out.

although the Result file wont open... it simply opens word but its blank.. i have attached the file and one other thing should the result file be saved as a Dotx file?

I simply made some path changes in the code here is the code:

Sub RunMerge()
Dim strMainDocName As String
Dim strResDocName As String
Dim docMain As Word.Document
Dim docResult As Word.Document
Dim strOutFolder As String
Dim strInfolder As String
Dim bNewInstance As Boolean
Dim objWord As Word.Application

'set file and folder paths.
strInfolder = "t:\Office\Skabeloner\renteaendring\brev"
strOutFolder = "c:\temp\"
strMainDocName = "letter1a.dotx"

'avoid creating multiple instances of the Word application
On Error Resume Next 'suppress error checking for the next instruction
Set objWord = GetObject(, "Word.Application")
On Error GoTo 0 'resume error checking

If objWord Is Nothing Then
'no instance, so make a new one
Set objWord = CreateObject("Word.Application")
bNewInstance = True
End If
objWord.Visible = True

strResDocName = Replace(strMainDocName, ".dotx", "_result.dotx")

' open the main document
Set docMain = objWord.Documents.Open(strInfolder & "\" & strMainDocName)

'do the merge
With docMain.MailMerge
.Execute
End With

'capture the result document into a specific document object variable
Set docResult = objWord.ActiveDocument
'closing and saving
docMain.Close wdDoNotSaveChanges
docResult.SaveAs strOutFolder & "\" & strResDocName
docResult.Close wdDoNotSaveChanges

'close application unless Word was already running
If bNewInstance Then
objWord.Quit
End If
End Sub

letter1a-result.dotx
0

LVL 77

Expert Comment

ID: 40464235
That behaviour is unexpected (by me). It seems to be to do with the fact that the main document is a template (.dotx). Try saving it as a document type with .docx extension. Change the code to suit.

There is no need for a main document to be a Template. The merge process makes copies of it in a template-like manner, but it is not a Template as far as Word is concerned.
0

LVL 1

Author Comment

ID: 40464265
I have tried to change the main document to docx format and edited the code. meaning now i can open the document in the results folder, but its only the first row that is merged what about the other rows? the result file is not anymore in merge mode so i can not see other letters... i hope this made sense?

here is the code now:
Sub RunMerge()
Dim strMainDocName As String
Dim strResDocName As String
Dim docMain As Word.Document
Dim docResult As Word.Document
Dim strOutFolder As String
Dim strInfolder As String
Dim bNewInstance As Boolean
Dim objWord As Word.Application

'set file and folder paths.
strInfolder = "t:\Office\Skabeloner\renteaendring\brev"
strOutFolder = "c:\temp\"
strMainDocName = "letter1a.docx"

'avoid creating multiple instances of the Word application
On Error Resume Next 'suppress error checking for the next instruction
Set objWord = GetObject(, "Word.Application")
On Error GoTo 0 'resume error checking

If objWord Is Nothing Then
'no instance, so make a new one
Set objWord = CreateObject("Word.Application")
bNewInstance = True
End If
objWord.Visible = True

strResDocName = Replace(strMainDocName, ".dotx", "_result.dotx")

' open the main document
Set docMain = objWord.Documents.Open(strInfolder & "\" & strMainDocName)

'do the merge
With docMain.MailMerge
.Execute
End With

'capture the result document into a specific document object variable
Set docResult = objWord.ActiveDocument
'closing and saving
docMain.Close wdDoNotSaveChanges
docResult.SaveAs strOutFolder & "\" & strResDocName
docResult.Close wdDoNotSaveChanges

'close application unless Word was already running
If bNewInstance Then
objWord.Quit
End If
End Sub
`
0

LVL 77

Accepted Solution

GrahamSkan earned 2000 total points
ID: 40464388
In Letters, as opposed to Labels, Directory etc., format, each letter should be in a separate Section, with the Sections separated from each other by a Next Page Section Break. That is what happens in my test.
letter-1-with-fields-result.docx
0

LVL 1

Author Comment

ID: 40464410
You are awesome!!!!

Thank you soo much Graham!! i will be back if there is anything ;) thank you soo much for your time and advise and learning me the procedures!
0

LVL 77

Expert Comment

ID: 40465273
If you can speak half a dozen or so languages, including (US) English, you should soon be able to pick up something simple like VBA.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
###### Suggested Courses
Course of the Month3 days, 15 hours left to enroll