Mail Merge that only acts upon rows that contain data and not functions

chipsworld

Board Regular
Joined
May 23, 2019
Messages
120
All,
I am confident that someone has a solution to the below...

I have a mail merge that works perfectly except...

It will run each row regardless of content in trigger cell (Name)

The data in the sheet being used is all copied form other sheets and consolidated on this one. When I run the mail merge, it works as it should, but it works on ALL rows...even the ones that have just a function.

I have seen various examples of code to deal with this, but can't for the life of me figure out how to make it work and it is the last thing I need to call this project done!

Here is the code that I have now...have tried a few things, but none of them have worked... Thanks in advance for any help!

Code:
Private Sub btn_Annexmerge_Click()
Dim Sheet As Worksheet, wsName As String, DataSource As String, WordPath As String
Dim WordApp As New Word.Application, WordDoc As Word.Document, StrName As String
Dim myvalue As String, mergeselect As String

Dim fd As FileDialog
Dim strShortName As String
Dim strInitialDir As String
Dim wb As Workbook
strInitialDir = CurDir  
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.InitialFileName = CurDir & "\"
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "All Word Files", "*.doc; *.docx"
If .Show = False Then
MsgBox "User cancelled without selecting. Process terminated."
ChDir (strInitialDir)
Exit Sub
End If

strPathAndFile = .SelectedItems(1)
End With

myvalue = InputBox("Enter Unit Name for Output File name", "FILE SAVE NAME")

With ActiveWorkbook
DataSource = .FullName
WordPath = strPathAndFile
wsName = .Sheets("FINANCE MEMO ROSTER").Name
StrName = myvalue
SavePath = .Path & "\"
End With


With WordApp
.Visible = True
.DisplayAlerts = wdAlertsNone
Set WordDoc = .Documents.Open(WordPath, AddToRecentFiles:=False)
With WordDoc

'Select Data Source and Complete Mail Merge

With .MailMerge
.MainDocumentType = wdFormLetters
.Destination = wdSendToNewDocument
.OpenDataSource Name:=DataSource, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PassWordDocument:="", PasswordTemplate:="", WritePassWordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, SubType:=wdMergeSubTypeAccess, _
Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=DataSource;Mode=Read;" & _
"Extended Properties=""HDR=YES;IME", SQLStatement:="SELECT * FROM `" & wsName & "$`", SQLStatement1:=""
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
.ActiveRecord = wdDefaultActiveRecord
End With
.Execute Pause:=False
End With
.Close SaveChanges:=False
End With
Exit Do



With .ActiveDocument

NewFileName = StrName & " - FINANCE FORM - " & Format(Date, "dd mmm yyyy") & ".docx"
.SaveAs SavePath + NewFileName, FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False

' and/or:
'.SaveAs SavePath + NewFileName, FileFormat:=wdFormatPDF, AddToRecentFiles:=False

Application.Dialogs(xlDialogPrinterSetup).Show

'.PrintOut

  
    '.Close SaveChanges:=False
End With
.DisplayAlerts = wdAlertsNone
.Quit
End With
End Sub
 
Last edited by a moderator:

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,300
Office Version
  1. 365
Platform
  1. Windows
Hi chipsworld,
your solution probably lies in changing the SQLStatement:="SELECT * FROM `" & wsName & "$`" part, e.g. adding a WHERE clause. Having said that, your problem is not 100% clear to me:
"When I run the mail merge, it works as it should, but it works on ALL rows...even the ones that have just a function." -> from that I should deduct that there is something that defines the records that you want to use for the mailmerge vs the ones that don't want to use? Please elaborate a bit.
What you could do: record a macro where you manually select the right records, probably with a filter while you are doing the mailmerge, that might give you the right bit of code to update your code.
Cheers,
Koen
 

chipsworld

Board Regular
Joined
May 23, 2019
Messages
120
Rijnsent,
What I mean by "All Rows" is that it applies the code to the rows that only contain formulas (i.e. blank as formula is an if statement that returns "" is nothing found in source doc.

I am trying to make it stop after the last row that contains an actual name...

What type of WHERE could I include to accommodate for this issue? Never ran into this before.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,530
Messages
5,625,356
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top