Link to home
Start Free TrialLog in
Avatar of Harsh Kumar
Harsh KumarFlag for Denmark

asked on

VBA - Mail merge from Excel into a word letter,

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

Thank you in advance
Sample-file-19.11.2014-V06.xlsm
Avatar of GrahamSkan
GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of Harsh Kumar

ASKER

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?
would it be easier to merge with fieldcodes or bookmarks?
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

        .Documents.Add "C:\Temp\Document\Template\letter 1.dotx"
        
        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

Open in new window

Sample-file-19.11.2014-V06.xlsm
letter-1.dotx
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.
Thank you for your reply Graham.

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 :)
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

Open in new window

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

Open in new window

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?
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

Open in new window

letter1a-result.dotx
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.
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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of GrahamSkan
GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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!
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.