VBA for Excel to Word Mail Merge- Mail merge already set up

[hlhelterbrand]

Hello All!

I have been trying to find a solution to this problem for a while, but to no avail.

I have data in an excel spreadsheet with names addresses and such and I already have a word doc set up and it is linked through a mail merge. What I need is to have a macro that I can added on to what I already have that opens the two mail merges and then ideally prints and saves them as PDFs (all one PDF for each word doc)

I think I have an idea on how to save the PDFs the big issues is opening word through a macro in excel and having it open as a mail merge. Any suggestions would be great!

 

[hlhelterbrand]

So I figured out part of my problem. I was able to find some code (and do some macro recording) and figured out how to open the word document in the merge format... but when it opens it is pulling up blank letters as well.

It seems that the SQL statement is not actually working, but if I try to change anything in that SQL statement, then the next section, 'opens with preview results selected' gives me an error...

So my end goal is to press a button in excel and the word doc will open with only the letters meeting the criteria, then I want to pint all of the letters to pdf.

Here is what I have so far, any help would be great!

Sub AlphaTest()

Dim TextEnter As String
Dim RowNum As Integer
Dim WordApp As Word.Application
Dim WordDoc As Word.Document


Set WordApp = CreateObject("Word.Application")
WordApp.Visible = True


'opens word doc
Set WordDoc = WordApp.Documents.Open("S:\Return Items\Automated NSFs\NSF Merge for CERT.docx")


With WordDoc
.MailMerge.MainDocumentType = wdMailingLabels
.MailMerge.OpenDataSource Name:= _
"S:\Return Items\Automated NSFs\Mail Merge.xlsm", ConfirmConversions:= _
        False, ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
        PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
        WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
        Connection:= _
        "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=S:\Return Items\Automated NSFs\Mail Merge.xlsm;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database " _
        , SQLStatement:="SELECT * FROM `Sheet1$`WHERE `Line 1`NOT LIKE "" And `Certified Mail?`NOT LIKE """, SQLStatement1:="", SubType:= _
        wdMergeSubTypeAccess
     End With
     
     'opens with preview results selected
    ActiveDocument.ActiveWindow.View.ShowFieldCodes = False
Set myMerge = ActiveDocument.MailMerge
If myMerge.State = wdMainAndSourceAndHeader Or _
 myMerge.State = wdMainAndDataSource Then
 myMerge.ViewMailMergeFieldCodes = False
End If
     
End Sub