Mail Merge VBA + Excel + Word!?

[jorgep.tec]

Hello!!!

I have written a macro in a word file to automate the creation of mail merge and run this macro as I open the file:


The database to fill this mail merge comes from the excel file.


This is the code:

Sub run_mail()
'
' run_mail Macro
'
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = 1
.LastRecord = 3
End With
.Execute Pause:=False
End With
Windows("ARcm").Close
End Sub


My doubt is how to use the value of an excel cell to fill the field where the number 3


.LastRecord = 3

How to make this link?

PS: I'm sorry for my english and thanks in advanced!

[jorgep.tec]

Hello!!!

I have written a macro in a word file to automate the creation of mail merge and run this macro as I open the file:


The database to fill this mail merge comes from the excel file.


This is the code:

Sub run_mail()
'
' run_mail Macro
'
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = 1
.LastRecord = 3
End With
.Execute Pause:=False
End With
Windows("ARcm").Close
End Sub


My doubt is how to use the value of an excel cell to fill the field where the number 3


.LastRecord = 3

How to make this link?

PS: I'm sorry for my english and thanks in advanced!

Somebody knows if is it possible?

[Kenneth Hobs]

Welcome to the forum!

Tips:
1. Do not quote all of a post or all code. Just say see the code in post #1 though that is not even needed.
2. When pasting code, post between code tags. Either type them in or click the "Go Advanced" button in the lower right of a reply or if there already, click the # icon to insert code tags.

The solution depends. Are you running the macro from Excel or from MSWord? If the former, obviously your code will need to be modified. If the latter, you can use this.

Sub test_GetValue()  Range("B" & Rows.Count).End(xlUp).Offset(1).Value = GetValue("c:\", "test.xls", "Sheetx", "A1")
End Sub


'http://www.vbaexpress.com/kb/getarticle.php?kb_id=454


'http://spreadsheetpage.com/index.php/tip/a_vba_function_to_get_a_value_from_a_closed_file/
'=GetValue("c:\files", "budget.xls", "Sheet1", "A1")
Function GetValue(path, File, sheet, ref)
'    path = "d:\files"
'    file = "budget.xls"
'    sheet = "Sheet1"
'    ref = "A1:R30"


    Dim arg As String
     
    If Right(path, 1) <> "\" Then path = path & "\"
     
    If Dir(path & File) = "" Then
        GetValue = "file not found"
        Exit Function
    End If
     
    arg = "'" & path & "[" & File & "]" & sheet & "'!" & _
    Range(ref).Range("a1").Address(, , xlR1C1)
    GetValue = ExecuteExcel4Macro(arg)
End Function

[jorgep.tec]

I'm sorry for my mistakes and thank you for your help!!!!