Greetings all
My team regularly has to prepare proposals for new work using a standard word template. Typically we include a list of similar jobs (an "Expertise List") previously undertaken, as an appendix to such proposals. We currently maintain an Access database which stores all information relating to past jobs and I have set this up so that I can run a query and have it save a selected list of jobs to an excel file. Separately I have setup a mail merge document which reads from the excel file and puts the references into a Word document in the specific layout and format that I need. So far, so good.

I now want to create a macro to automate the creation of the Expertise List in Word, using my mail merge document, which can then be pasted into the proposal. Through my usual process of recording macros and the tweaking the resultant code based on advice in various posts, etc, I have cobbled together the following code which seems to work... sort of. 

Sub CreateExpertiseFromBlank()
'
' CreateExpertise Macro
' Uses mail merge template to create a formatted expertise list from an Excel source table into a blank doc
'
    Documents.Open ("C:\Users\ShogunPatch\Dropbox\MyDesktop\ExpertiseList.docm")
    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With
    Selection.WholeStory
    Selection.Fields.Update
'    Application.DisplayAlerts = True
End Sub
My problem is that my mail merge document uses a defined source which is currently a test excel sheet in a specific location (currently the same location as the mail merge document, my desktop). In practice I want the mail merge template to be stored in a permanent location on a shared drive, and users to create a bespoke source spreadsheet, exporting the specific list of jobs suitable for that particular proposal into a new sheet to be given a standard name ("ExpertiseSourceList.xlsx") and saved into a separate, proposal-specific sub-folder elsewhere on our shared drive. My thinking being that users would draft a bespoke proposal in Word then save it to that same proposal-specific sub-folder before running the macro to produce the mail merge. The mail merge could then default to using the Excel source document in that current folder (i.e. the proposal-specific sub-folder).

Does any of this make sense? If so, can anybody please help me tweak my code to achieve this?

Many thanks.