Consulting

Results 1 to 4 of 4

Thread: Solved: Using Excel To Perform a Word Mailmerge

  1. #1
    VBAX Regular
    Joined
    Sep 2006
    Posts
    13
    Location

    Solved: Using Excel To Perform a Word Mailmerge

    Hi there,

    I'm working on a little project to automate a mailmerge from within Excel.

    I'm trying to create several buttons that do different tasks on my Excel sheet.

    I have one that Prints the document, (nice and straight forward)

    [vba]Private Sub PrintButton_Click()
    Worksheets("INPUT FORM").PrintOut Copies:=1, Preview:=True, Collate:= _
    True
    End Sub[/vba]

    One that saves the Excel sheet as a name based on some cells values (using a macro that I found)

    [vba]Private Sub SaveButton_Click()

    Dim vFile As Variant
    Dim sName As Variant

    PrimaryName = ActiveSheet.Range("B1")
    CMN_Num = ActiveSheet.Range("D1")

    sName = "A" & CMN_Num & " - " & PrimaryName & " - Overview Sheet.xls"

    vFile = Application.GetSaveAsFilename(InitialFileName:=sName, _
    fileFilter:="Excel files (*.xls), *.xls", _
    Title:="")
    If vFile <> False Then
    ThisWorkbook.SaveAs Filename:=vFile
    Else
    MsgBox "Not a valid path" 'cancel
    End If

    End Sub[/vba]

    and the tricky one - one that will open a word mailmerge document and merge with the previously saved Excel sheet.

    My question is (well my first question).... how do I get the name of the file path that I saved the spreadsheet to(C:\blah blah\blah), so that word knows where the source data is?

    What I am managing to do is open Word and autorun a macro, but the macro needs the information (C:\blah blah\blah). This is where I'm stuck.

    Sorry if my post seems a bit vague, this is all kind of new to me.

    Thanks much in advance

    T

  2. #2
    VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Maybe something like:
    [VBA]
    Dim FName As String

    FName = "f:\AAA\Data1.xls"
    Set wb = objExcel.Workbooks.Open(FName)
    [/VBA]
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  3. #3
    VBAX Regular
    Joined
    Sep 2006
    Posts
    13
    Location
    Thanks for that.

    Maybe I?ve been looking at it from the wrong side.

    Would it be easier to have Word get the name and file path of the open Excel Spreadsheet or have Excel ?pass? the name and file path to Word?

    I thought that seeing as I have a function already in Excel to save the spreadsheet to specific name, the variable is already there and I just need Word to know what it is?.

    So I guess that what I want is for Excel to pass Word a variable that Word can then use (its going to be the source file for a mail. Is that easy to do or straight forward?

    Cheers

    Trubble

  4. #4
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •