Macro to automatically mail merger and print - Code Needed

[charp021]

Hello,

I have a word doc that I have set up as a mail merge from my excel file. I would like to be able to select a cell (a single recipient) in excel and have a user click a button ("Print") and have the paperwork print out for the single recipient that is selected. Is there a way to do this? I've tried searching for this but can't find anything if I want to print only one recipient based on selected cell.

Thanks,
Becca

 

[Macropod]

For code to run a mailmerge from Excel, see: https://www.mrexcel.com/forum/gener...vidual-pdf-files-post4796480.html#post4796480. There are two ways you could approach the single-record merge issue:
1. Apply an IF test to the '.Execute Pause:=False' line to check whether the current record matches your selected record; or
2. Apply a mailmerge filter so that only your chosen record is processed. Applying such a filter manually, then running the GetSQL macro in the above thread will show you how the SQL statement would need to be modified.

 

[charp021]

I'm running into an issue. The code runs until the red highlighted portion. But it is also popping up a prompt for me to select a table. I have to select a workbook also but the workbook I want is not an available option.

Sub RunMerge()
'Note: this code requires a reference to the Word object model
Application.ScreenUpdating = False
Dim StrMMSrc As String, StrMMDoc As String, StrMMPath As String, StrName As String
Dim i As Long, j As Long
Const StrNoChr As String = """*./\:?|"
Dim wdApp As New Word.Application, wdDoc As Word.Document
wdApp.Visible = False
wdApp.DisplayAlerts = wdAlertsNone
StrMMSrc = ThisWorkbook.FullName
StrMMPath = ThisWorkbook.Path & ""
StrMMDoc = StrMMPath & "HA Paperwork 2019.docx"
Set wdDoc = wdApp.Documents.Open(Filename:=StrMMDoc, AddToRecentFiles:=False, ReadOnly:=True, Visible:=False)
With wdDoc
  With .MailMerge
    .MainDocumentType = wdFormLetters
[COLOR=#ff0000]    .OpenDataSource Name:=StrMMSrc, ReadOnly:=True, AddToRecentFiles:=False, _
      LinkToSource:=False, Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;" & _
      "Data Source=StrMMSrc;Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
      SQLStatement:="SELECT * FROM `Sheet1$`"[/COLOR]
    For i = 1 To .DataSource.RecordCount
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      With .DataSource
        .FirstRecord = i
        .LastRecord = i
        .ActiveRecord = i
        If Trim(.DataFields("Name")) = "" Then Exit For
        StrName = .DataFields("Name")
      End With
      .Execute Pause:=False
      For j = 1 To Len(StrNoChr)
        StrName = Replace(StrName, Mid(StrNoChr, j, 1), "_")
      Next
      StrName = Trim(StrName)
      With wdApp.ActiveDocument
        .SaveAs Filename:=StrMMPath & StrName & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
        ' and/or:
        '.SaveAs Filename:=StrMMPath & StrName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
        .Close SaveChanges:=False
      End With
    Next i
    .MainDocumentType = wdNotAMergeDocument
  End With
  .Close SaveChanges:=False
End With
wdApp.DisplayAlerts = wdAlertsAll
wdApp.Quit
Set wdDoc = Nothing: Set wdApp = Nothing
Application.ScreenUpdating = False
End Sub

 

[Macropod]

The code is designed to run the mailmerge from you workbook it's been added to. It also uses a basic SQL statement to get all records from the worksheet named 'Sheet1'. You'll need to change the SQL statement if you want to filter the data, or order the output, or if you're using a worksheet with a different name (see comment 2 in my previous reply).

 

[charp021]

So I run the GetSQL macro in the mailmerge file and get the location for the Select statement. Put it in the RunMerge macro and I'm still getting flagged at the same line. Do I need to designate the sheet within the spreadsheet? It still pops up the prompt asking me to select a table with a list of no tables?

.OpenDataSource Name:=StrMMSrc, ReadOnly:=True, AddToRecentFiles:=False, _
LinkToSource:=False, Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;" & _
"Data Source=StrMMSrc;Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
SQLStatement:="SELECT * FROM G:\Audiology\Hearing aids & Cochlear Implants\Claim Requirement Spreadsheet\Claims Spreadsheet 2019.xlsm"
​

 

[Macropod]

Your modified SQL statement is NOT what you'd get by following the advice in comment 2 in my initial reply.

 

[charp021]

I'm clearly not understanding this. Maybe I can try to find another post where it is explained a little more. I don't understand a large chunk of the code and unfortunately your replies just restate the comments that I'm not getting.

 

[Macropod]

It's really not that difficult... What is you worksheet's name?