Hi
1.Is there a way to stop the SQL popup box at all on mail merge with VBA?
You receive the "Opening this will run the following SQL command" message when you open a Word mail merge main document that is linked to a data source
I've tried creating this code below with the hope I can do that, but it doesn't work Is something fundamentally wrong with the code below? I think it comes up with a type mismatch error on the red part.
Sub RunMailMerge()
Dim wdOutputName, wdInputName As String
wdOutputName = "L:\MyPath\test_pdf.pdf"
wdInputName = "L:\MyPath\Referencing\Templates\Letters\Outgoing_Ref_Template_mmb.docx"
strWorkbookName = ThisWorkbook.Path & "\" & ThisWorkbook.Name
Dim wd As Object
Dim wdocSource As Object
Dim wdDoc As Object
' open the mail merge layout file
' On Error Resume Next
Set wd = GetObject(, "Word.Application")
'If wd Is Nothing Then
' Set wd = CreateObject("Word.Application")
'End If
'On Error GoTo 0
Set wdDocSource = wd.Documents.Open("L:\MyPath\Referencing\Templates\Letters\Outgoing_Ref_Template_mmb.docx")
With wdDocSource
.Application.Visible = True
.MailMerge.OpenDataSource _
Name:=strWorkbookName, _
AddToRecentFiles:=False, _
Revert:=False, _
Format:=wdOpenFormatAuto, _
Connection:="Data Source=" & strWorkbookName & ";Mode=Read", _
SQLStatement:="SELECT * FROM `outgoing$` WHERE `MM_Next` = 'Y' AND `Send Email?` = 'Y'"
.MailMerge
.MainDocumentType = wdFormLetters
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
.Execute Pause:=False
.MailMerge.DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
' save output file
wd.Application.ActiveDocument.SaveAs wdOutputName
' cleanup
wd.Close SaveChanges:=False
wd.Application.Close
Set wdDoc = Nothing
End Sub
2.
I've created the following:
Function RunSimpleMailMerge(TCell As Range)
Dim wdOutputName, wdInputName, PathM, OutM As String
Dim wd As Object
Dim wdocSource As Object
Dim wdDoc As Object
strWorkbookName = ThisWorkbook.Path & "\" & ThisWorkbook.Name
'wdOutputName = "L:\mypath\test_pdf.pdf"
wdInputName = "L:\mypath\Referencing\Templates\Letters\Outgoing_Ref_Template_mm2.docx"
PathM = "L:\mypath\Referencing\Outgoing references 2014\"
OutM = PathN & TCell.Offset(0, 3).Value & " _Outgoing.pdf"
' open the mail merge layout file
Dim wdDoc As Object
' On Error Resume Next
' Set wdDoc = GetObject(wdInputName, "Word.Application")
' If wdDoc Is Nothing Then
' Set wd = CreateObject("Word.Application")
' End If
' On Error GoTo 0
Set wdDoc = GetObject(wdInputName, "Word.document")
wdDoc.Application.Visible = True
With wdDoc.MailMerge
.MainDocumentType = wdFormLetters
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
.Execute Pause:=False
End With
' show and save output file
wdDoc.Application.Visible = True
wdDoc.Application.ActiveDocument.SaveAs PathM & OutM, FileFormat:=wdFormatPDF
' cleanup
wdDoc.Close SaveChanges:=False
'wdDoc.Application.Visible = False
Set wdDoc = Nothing
End Function
But
1)Requires word to already be open with no document loaded
or
2)Opens word properly and opens the document but doesn't run the mailmerge on opening the document.
Bookmarks