Excel VBA to Run Mail Merge with Word

[cvincent]

I have been able to use vba somewhat, by lookiing into other file codes, but I need a lot of help!

I have an excel file, "Estimate of Charges Worksheet.xlsm", with calculations on one tab. Those calculations transfer over to a hidden tab in the file with named columns for the data. That tab name is “Ltr Data”.

I have a word document that is set up as a merge letter, named "Estimated Charges - Letter.doc"

I want to run a macro in excel that opens the Word document and merges the data into a letter, then closes the merge file.

I will only be doing one letter at a time, so it only needs to pull one line of data from the spreadsheet for the letter. New info would need to be entered to create a second letter, etc.

I am using Excel 2010, and Word 2010 (but it needs to be compatible with Word 97-03).

Because people will be putting the files into their own folders, I need to set paths (which I picked up from another file), as such:

excelPath = ThisWorkbook.Path & "\Estimate of Charges Worksheett.xlsm"
wordPath = ThisWorkbook.Path & "\Estimated Charges - Letter.doc"

I have been working on this all day and getting nowhere. Your assistance would be greatly appreciated!


Thanks and have a blessed day!

 

[Macropod]

Your specifications for:
excelPath = ThisWorkbook.Path & "\Estimate of Charges Worksheett.xlsm"
wordPath = ThisWorkbook.Path & "\Estimated Charges - Letter.doc"
look fine (except for Worksheett, perhaps) and that, if you're referring to the workbook running the code, it might be better to use simply ThisWorkbook.FullName.

It's a bit hard to tell you how to solve a problem when you haven't actually said what it is. That said, if you need something compatible with Word 97-03, you might consider whether it's advisable to be using an xlsm workbook.

For a few hints, see: http://www.mrexcel.com/forum/genera...ing-mailmerged-details-excel.html#post4675634

 

[cvincent]

Thank you for your assistance Paul.

Below is my code. The macro runs, bringing up my Word merge document, but it does not merge the letter with the data. I'm not getting an error message, it just is not merging.
Then if I try to run it a second time, it tells me the Word letter is locked for editing.

Sub OpenWordandMerge()

Dim wordApp As Word.Application
Dim wordDoc As Word.Document
Dim wordMailMerge As Word.MailMerge
Dim wordMergeFields As Word.MailMergeFields
Dim wordPath As String
Dim excelPath As String

CurrentWorksheet = ActiveSheet.Name
excelPath = ThisWorkbook.Path & "\Estimate of Charges Worksheet.xlsm"
wordPath = ThisWorkbook.Path & "\Estimated Charges - Letter.doc"

'Ltr Data
Worksheets("Ltr Data").Visible = True
Sheets("Ltr Data").Select

Set wordApp = CreateObject("Word.Application")
Set wordDoc = wordApp.Documents.Open(wordPath)
Set wordMailMerge = wordDoc.MailMerge

wordApp.Visible = True

Worksheets("Ltr Data").Visible = False
Worksheets("Estimate Worksheet").Select


End Sub

 

[Macropod]

There is a lot missing from your code to make it work, including the all-important MailMerge.Execute method. Did you look at the link I provided? The reason you're told the mailmerge main document is locked the second-time around is that you never closed it the first time around. That too is demonstrated in the link...

 

[cvincent]

I so much appreciate your help on this - I'm sure I am missing a lot, as I am really struggling with this. I have not had any formal training, just tried to pick up info where I could. This code runs fine, opens the word merge document, but it does not merge. Yes, I did look at the link, but looks like it was trying to open the data source, which is already open, as I am merging from the excel file. My form letter is already linked to the data source, and will merge after excel calls it up, but won't merge from the vba. I need to be able to just put a button on the excel file that will merge the appropriate letter, then close the form letter.

Sub OpenWordandMerge1()

Dim wordApp As Word.Application
Dim wordDoc As Word.Document
Dim wordMailMerge As Word.MailMerge
Dim wordMergeFields As Word.MailMergeFields
Dim wordPath As String
Dim excelPath As String


CurrentWorksheet = ActiveSheet.Name
DataSource = ThisWorkbook.Path & "\Estimate of Charges Worksheet.xlsm"
wordPath = ThisWorkbook.Path & "\Estimated Charges - Letter.doc"

'Ltr Data
Worksheets("Ltr Data").Visible = True
Sheets("Ltr Data").Select

Set wordApp = CreateObject("Word.Application")
Set wordDoc = wordApp.Documents.Open(wordPath)
Set wordMailMerge = wordDoc.MailMerge

wordApp.Visible = True
With wordDoc.MailMerge
.MainDocumentType = wdFormLetters
.Destination = wdSendToNewDocument
.SuppressBlankLines = True

With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With

Worksheets("Ltr Data").Visible = False

Worksheets("Surgery Estimate Worksheet").Select

End With

End Sub

 

[Macropod]

I did look at the link, but looks like it was trying to open the data source, which is already open, as I am merging from the excel file. My form letter is already linked to the data source, and will merge after excel calls it up, but won't merge from the vba. I need to be able to just put a button on the excel file that will merge the appropriate letter, then close the form letter.

It opens the data source from Word because that's how a mailmerge works. If you had simply taken the code in the link and changed the name of the document to open, you'd probably find it goes close to doing what you want - all you might need to add is the logic to restrict it to merging just the 'current' record instead of all records. I could add that myself, but you've not given any indication yet as to how that record might be identified.

PS: When posting code, please use the code tags, indicated by the # button on the posting menu. Without them, your code loses much of whatever structure it had.

 

[cvincent]

Cheers, Paul!!

I was finally able to get the macro working. This is the vba I used, taken from another file. I noticed on the other file, under Tools, References, they had the Microsoft Forms 2.0 Object Library added, which I didn't. After adding it, it worked. Don't know if that was the trick or not, or if I just finally got the vba right, but sharing what worked. Thank you so much for all the time you took to help this novice. Have a great day!

Sub EstimatedChgsLtr()

    Dim wordApp As Word.Application
    Dim wordDoc As Word.Document
    Dim wordMailMerge As Word.MailMerge
    Dim wordMergeFields As Word.MailMergeFields
    Dim wordPath As String
    Dim excelPath As String
       
    CurrentWorksheet = ActiveSheet.Name
    excelPath = ThisWorkbook.Path & "\Estimate of Charges Worksheet.xlsm"

        Worksheets("Ltr Data").Visible = True
        Sheets("Ltr Data").Select
        Dim LtrFinalRow As Long
        LtrFinalRow = Cells(Rows.Count, 1).End(xlUp).Row
        
            If LtrFinalRow > 1 Then
                'Clear any extra, blank rows.
                Range("B" & LtrFinalRow + 1).Select
                Range(Selection, Selection.End(xlToRight)).Select
                Range(Selection, Selection.End(xlDown)).Select
                Selection.Delete Shift:=xlUp
                
                wordPath = ThisWorkbook.Path & "\Estimated Charges - Letter.doc"
                Set wordApp = CreateObject("Word.Application")
                Set wordDoc = wordApp.Documents.Open(wordPath)
                Set wordMailMerge = wordDoc.MailMerge
                
                wordMailMerge.OpenDataSource Name:=excelPath, SQLStatement:="SELECT * FROM `'Ltr Data$'`"
                wordMailMerge.Execute
                wordDoc.Close
                wordApp.Visible = True
            End If
        Worksheets("Ltr Data").Visible = False
        
    Set wordMailMergeFields = Nothing
    Set wordMailMerge = Nothing
    Set wordDoc = Nothing
    Set wordApp = Nothing
    Sheets(CurrentWorksheet).Select
 
End Sub