Conditional mail merge assistance request

Hi Excel expert friends,

I want to create separate letter for each person who has 'W' remark in column 'J' on 'Data' sheet. The person's name in is in column 'F'. I want the name of person 'column F' as a file name for each one. If there is a dash sign in column 'J' then I don't want to create a letter for that person. I want to do it by using mail merge function in word.

I'm in a urgent need of a macro to perform this mail merge to create letters for the persons who has a 'W' remark. Therefore, I request you experts to assist me to achieve this target at the earliest positively.

The word file is in 'INPUT' folder.
I want to save all letters for the persons in 'OUTPUT E' folder.

Any help will be highly appreciated.

Thanking you inn antici-0ation.

Originally Posted by mso3

I'm in a urgent need of a macro to perform this mail merge.

It's rude to wait until something becomes 'urgent' then expect volunteers to drop whatever else they're doing just to help you. Furthermore, there is plenty of code about - both here and elsewhere - that already does most of what you say you want. All you need do is adapt it to suit your needs. For example, the code in the Send Mailmerge Output to Individual Files topic in the the Mailmerge Tips and Tricks threads at:
http://www.msofficeforums.com/mail-m...ps-tricks.html
and:
http://windowssecrets.com/forums/sho...ips-amp-Tricks
could be adapted in a couple of minutes.

Hi Paul,

Extremely sorry for the word 'urgent'. I always appreciate all the members who assist me to solve the problems and respect them. I don't mean to be rude manner. I'm ready to wait for solution patiently. Please don't mind.

I visited the sites you suggested but didn't find a solution to meet my requirement of conditional mail merge if there is a 'W' remark in column 'J'. Will you please suggest me a code to achieve it?

Thanking you,

You didn't really expect to find a macro pre-coded to look for 'W' remark in column 'J' did you? As I said, the code would need to be adapted, which is a trivial undertaking:

Sub Merge_To_Individual_Files()
Application.ScreenUpdating = False
Dim StrFolder As String, StrName As String, MainDoc As Document, i As Long, j As Long, BlnEx As Boolean
Const StrNoChr As String = """*./\:?|"
Set MainDoc = ActiveDocument
With MainDoc
  StrFolder = Replace(.Path, "\INPUT", "\OUTPUT E\")
  For i = 1 To .MailMerge.DataSource.RecordCount
    BlnEx = True
    With .MailMerge
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      With .DataSource
        .FirstRecord = i
        .LastRecord = i
        .ActiveRecord = i
        If Trim(.DataFields("NAME_E")) = "" Then Exit For
        StrName = .DataFields("NAME_E")
      End With
      If Trim(.DataFields("REMARK")) = "-" Then BlnEx = False
      If BlnEx = True Then .Execute Pause:=False
    End With
    If BlnEx = True Then
      For j = 1 To Len(StrNoChr)
        StrTxt = Replace(StrName, Mid(StrNoChr, j, 1), "_")
      Next
      StrName = Trim(StrName)
      With ActiveDocument
        .SaveAs FileName:=StrFolder & StrName & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
        ' and/or:
        .SaveAs FileName:=StrFolder & StrName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
        .Close SaveChanges:=False
      End With
    End If
  Next i
End With
Application.ScreenUpdating = True
End Sub

Hi paul,

Thank you for the code. I’m getting error as:

‘Method of data member not found’ on the following line

      If Trim(.DataFields("REMARK")) = "-" Then BlnEx = False

Thanking you,

Typo. Move that line to precede the 'End With'.

Note: None of this additional code would be required if you were to use Word's mailmerge filter to filter out the unwanted records...

Hi Paul,

Thank you.
Now the macro is running without error but not creating the letters.

Option Explicit

Sub Merge_To_Individual_Files1()
Application.ScreenUpdating = False
Dim StrFolder As String, StrName As String, MainDoc As Document, i As Long, j As Long, BlnEx As Boolean
Const StrNoChr As String = """*./\:?|"
Dim StrTxt As String
Set MainDoc = ActiveDocument
With MainDoc
  StrFolder = Replace(.Path, "\INPUT", "\OUTPUT E\")
  For i = 1 To .MailMerge.DataSource.RecordCount
    BlnEx = True
    With .MailMerge
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      With .DataSource
        .FirstRecord = i
        .LastRecord = i
        .ActiveRecord = i
        If Trim(.DataFields("NAME_E")) = "" Then Exit For
        StrName = .DataFields("NAME_E")
        If Trim(.DataFields("REMARK")) = "-" Then BlnEx = False
      End With
      'If Trim(.DataFields("REMARK")) = "-" Then BlnEx = False
      If BlnEx = True Then .Execute Pause:=False
    End With
    If BlnEx = True Then
      For j = 1 To Len(StrNoChr)
        StrTxt = Replace(StrName, Mid(StrNoChr, j, 1), "_")
      Next
      StrName = Trim(StrName)
      With ActiveDocument
        .SaveAs FileName:=StrFolder & StrName & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
        ' and/or:
        .SaveAs FileName:=StrFolder & StrName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
        .Close SaveChanges:=False
      End With
    End If
  Next i
End With
Application.ScreenUpdating = True
MsgBox "The task completed successfully."
End Sub

Thanking you,

The code in post #7 works with your data and folder structure as depicted in your attachement to post #1. If you're not actually using that structure - in terms of the folders, files and data therein - you can't expect to get the right results. Neither would it be reasonable to expect anyone else to figure out what the issue is.

Furthermore, in post #7 you said:

Originally Posted by mso3

Now the macro is running without error but not creating the letters.

But now you're saying:

Originally Posted by mso3

I changed the path but in vain. Still I’m getting error ‘method of data member not found’ on the same line.

Why did you change the path? Unless it was wrong to begin with, all that would do is create problems.

How about making sure your folders, files and data therein are exactly the same as what you depicted in post #1 and get that working before making changes...

The code in your last post works just fine for me. Did you look in the 'OUTPUT E\' folder?

Hi Paul,

Greetings from me!

Thank you.

I checked the ‘OUTPUT E’ folder. It’s empty. I changed the path but in vain. Still I’m getting error ‘method of data member not found’ on the same line. Please see the code below and suggest me what’s wrong.

Option Explicit

Sub Merge_To_Individual_Files1()
Application.ScreenUpdating = False
Dim StrFolder As String, StrName As String, MainDoc As Document, i As Long, j As Long, BlnEx As Boolean
Const StrNoChr As String = """*./\:?|"
Set MainDoc = ActiveDocument
With MainDoc
  StrFolder = Replace(.Path, "c:\WORD\INPUT", "c:\WORD\OUTPUT E\")
  For i = 1 To .MailMerge.DataSource.RecordCount
    BlnEx = True
    With .MailMerge
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      With .DataSource
        .FirstRecord = i
        .LastRecord = i
        .ActiveRecord = i
        If Trim(.DataFields("NAME_E")) = "" Then Exit For
        StrName = .DataFields("NAME_E")
      End With
      If Trim(.DataFields("REMARK")) = "-" Then BlnEx = False 'Getting error method of data member not found
      If BlnEx = True Then .Execute Pause:=False
    End With
    If BlnEx = True Then
      For j = 1 To Len(StrNoChr)
        StrTxt = Replace(StrName, Mid(StrNoChr, j, 1), "_")
      Next
      StrName = Trim(StrName)
      With ActiveDocument
        .SaveAs FileName:=StrFolder & StrName & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
        ' and/or:
        .SaveAs FileName:=StrFolder & StrName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
        .Close SaveChanges:=False
      End With
    End If
  Next i
End With
Application.ScreenUpdating = True
MsgBox "The task completed successfully."
End Sub

Thanking you,

Hi Paul,

Greetings from me!

Sorry! The error was from me and not by the code. After opening the word document the message pop up 'Opening this document will run the following sql command .... ... do you want to continue? No Yes
Here I click on 'No' so the letters were not created. If I click on 'Yes' then the letters are creating successfully as per requirement.

To avoide this mistake from the user can we make default setting to 'Yes' after opening the word document and make display alerts to false? So that there will be no problem.

Everything else is fine. I appreciate you for the same.

Thank you.

Have a nice day.

That can't be automated without a major re-write of the code - as much of a re-write as if the merge was being executed from Excel. Alternatively, a registry edit could be made - for every user who is likely to need to use the macro and that same edit would remove the prompt for every other merge they do, as well. Instead, a little user education is all that's required. After all, the same consideration arises for every mailmerge

Hi Paul,

Excellent! Thank you for your kind cooperation and guidance.

I appreciate you for the same.

Thank you and have a nice day.

Hi Paul,

Excellent! Thank you for your kind cooperation and guidance.

I appreciate you for the same.

Thank you and have a nice day.