Run Word Mailmerge from Excel VBA

[stockman256]

I have an excel spreadsheet with VBA code to generate a list and data for a word mailmerge. I've gotten the following code to work but I'd like to be able to use a variable to insert the filename instead of having to use a hyperlink and put the filename in the code. Is there a way to do that?

'Print Letters
Application.DisplayAlerts = False
Letter = Sheets("Control").Range("B17").Value


ActiveWorkbook.FollowHyperlink Address:="D:\OneDrive\My Docs\Letter 1.docx", NewWindow:=True


Dim DocName As String

DocName = ActiveDocument.Name

With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.Execute
End With

ActiveDocument.PrintOut Range:=wdPrintAllDocument, Item:= _
wdPrintDocumentWithMarkup, Copies:=1, Pages:="", PageType:= _
wdPrintAllPages, Collate:=True, Background:=True, PrintToFile:=False, _
PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
PrintZoomPaperHeight:=0

ActiveDocument.SaveAs2 Filename:=Letter
ActiveDocument.Close
ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges

 

[earthworm]

I have an excel spreadsheet with VBA code to generate a list and data for a word mailmerge. I've gotten the following code to work but I'd like to be able to use a variable to insert the filename instead of having to use a hyperlink and put the filename in the code. Is there a way to do that?

'Print Letters
Application.DisplayAlerts = False
Letter = Sheets("Control").Range("B17").Value


ActiveWorkbook.FollowHyperlink Address:="D:\OneDrive\My Docs\Letter 1.docx", NewWindow:=True


Dim DocName As String

DocName = ActiveDocument.Name

With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.Execute
End With

ActiveDocument.PrintOut Range:=wdPrintAllDocument, Item:= _
wdPrintDocumentWithMarkup, Copies:=1, Pages:="", PageType:= _
wdPrintAllPages, Collate:=True, Background:=True, PrintToFile:=False, _
PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
PrintZoomPaperHeight:=0

ActiveDocument.SaveAs2 Filename:=Letter
ActiveDocument.Close
ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges

Try below i provided that your file path is static.

Dim fileName, filePath As String
filePath = "D:\OneDrive\My Docs\"
fileName = Dir(filePath)

Application.DisplayAlerts = False

Letter = Sheets("Control").Range("B17").Value
ActiveWorkbook.FollowHyperlink Address:="D:\OneDrive\My Docs\" & fileName, NewWindow:=True

Dim DocName As String

DocName = ActiveDocument.Name

With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.Execute
End With

ActiveDocument.PrintOut Range:=wdPrintAllDocument, Item:= _
wdPrintDocumentWithMarkup, Copies:=1, Pages:="", PageType:= _
wdPrintAllPages, Collate:=True, Background:=True, PrintToFile:=False, _
PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
PrintZoomPaperHeight:=0

ActiveDocument.SaveAs2 fileName:=Letter
ActiveDocument.Close
ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges

 

[stockman256]

@earthworm that still has the path coded into the VBA code. I'm looking for a solution to reference a cell within the worksheet to get the entirety of the file location/name for the word mail merge file so that I can use different templates to mail different letters. That may not be possible as every solution I have found has the location in the code...

I am able to open a specific word file but then I can't get it to do the mail merge - or I can hardcode the filename in the code and do the mailmerge but to change it, I have to change the code every time.

I've got a marketing sequence of 6 different letters that are mailed to each prospect over a 6 month period. If I can't get this to work, I can write six macros and code the file location for each letter into it, but I'd like to be able to just reference a cell that has the filename/location in it for each letter.

 

[earthworm]

@earthworm that still has the path coded into the VBA code. I'm looking for a solution to reference a cell within the worksheet to get the entirety of the file location/name for the word mail merge file so that I can use different templates to mail different letters. That may not be possible as every solution I have found has the location in the code...

I am able to open a specific word file but then I can't get it to do the mail merge - or I can hardcode the filename in the code and do the mailmerge but to change it, I have to change the code every time.

I've got a marketing sequence of 6 different letters that are mailed to each prospect over a 6 month period. If I can't get this to work, I can write six macros and code the file location for each letter into it, but I'd like to be able to just reference a cell that has the filename/location in it for each letter.

The code that i have shared will open any file present in that folder.

How will computer know from where to fetch the file without giving path.