VBA macro form/bulk letter with Excel data - If clauses (MergeField)

Hi,

I want to write a form letter with data of an Excel sheet (Name, Address, etc.).
I found the following very helpful document:

But there are not any if-not conditions. Since I need to divide between female, male, company  (concerning salutatory) etc., I need to 
query the values of the Excel sheet fields and use an if-then condition to decide if something should be written to the form letter.
Is that possible?

According to the example above, I would need something like the following pseudo code:

if MergeField("NAME") IS NOT NULL then                       ''e.g. if a company is entered in another field, the name field is empty
       word.InsertMergeField MergeField:="NAME"
end if

Thanks in advance,

Nathalie
If you are using VBA for the primary reason of adding If criteria, then perhaps you should have a look at the following article.


Adding something like,

Dear { IF { MERGEFIELD Gender } = "Male" "Mr." "Ms." } { MERGEFIELD Name };

... would seem to make more sense (to me at least). The form letter stays as one while the output is dynamically generated upon the state and content of the data being brought in from Excel. From your description that would have to be expanded one level in order to compensate for Company but that shouldn't be too hard if one knew the specifics.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Assuming the 'Gender' field is empty both for companies and people of unknown gender, try:

 

Dear {IF{MERGEFIELD Gender}= "" "Sir/Madam" "M{IF{MERGEFIELD Gender}= M r s}. {MERGEFIELD LastName}"}

 

Note: The field brace pairs (ie '{ }') for the above example are created in the body of the document via Ctrl-F9 (Cmd-F9 on a Mac) - you can't simply type them or copy & paste them from this message. Note also that you cannot have fewer spaces than are shown above.

Cheers
Paul Edstein
(Fmr MS MVP - Word)

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi,

the primary reason for VBA is not the gender division.
(How) can I use your statement in VBA and not inserted in a word document directly?

Nathalie

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

I don't code for Word macros very much as the imprecision of the insertion point as a target drives me into conniptions. MSDN covers the fundamentals and basic application of mail merge fields fairly well.


Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi,

the primary reason for VBA is not the gender division.
(How) can I use your statement in VBA and not inserted in a word document directly?

Nathalie

What is the primary reason for VBA?
Volunteering to "pay forward" the help I've received in the Microsoft user community.


Charles Kenyon
Sun Prairie, Wisconsin
wordfaq[at]addbalance[dot]com

Legal site: https://addbalance.com

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

There are various ways of inserting simple field codes into a Word document. The link you posted shows one way. Here's another that can handle complex field codes, including those that have preceding, intervening and following text:

 

Sub Demo()
Dim StrTxt As String, Rng As Range

' Text for field code:  {IF{MERGEFIELD Gender}= "" "Sir/Madam" "M{IF{MERGEFIELD Gender}= M r s}. {MERGEFIELD LastName}"}
StrTxt = "Dear {IF{MERGEFIELD Gender}= """" ""Sir/Madam"" ""M{IF{MERGEFIELD Gender}= M r s}. {MERGEFIELD LastName}""}"
With ActiveDocument
  Set Rng = ActiveDocument.Range(0, 0)
  Call FieldStringToCode(ActiveDocument, StrTxt, Rng)
End With
End Sub

 

Sub FieldStringToCode(wdDoc As Document, StrCode As String, wdRange As Range)
' Converts "textual" field codes into real field codes
' in the specified document at the designated range/bookmark
Dim RngFld As Range, RngTmp As Range, oFld As Field, StrTmp As String
With wdDoc
  Set RngFld = wdRange
  With RngFld
    .Text = StrCode
    Do While InStr(1, .Text, "{") > 0
      Set RngTmp = wdDoc.Range(Start:=.Start + _
        InStr(.Text, "{") - 1, _
        End:=.Start + InStr(.Text, "}"))
      With RngTmp
        Do While Len(Replace(.Text, "{", vbNullString)) <> _
            Len(Replace(.Text, "}", vbNullString))
          .End = .End + 1
          If .Characters.Last.Text <> "}" Then .MoveEndUntil cset:="}", _
            Count:=Len(ActiveDocument.Range(.End, RngFld.End))
        Loop
        .Characters.First = vbNullString
        .Characters.Last = vbNullString
        StrTmp = .Text
        Set oFld = .Fields.Add(Range:=RngTmp, _
          Type:=wdFieldEmpty, Text:="", PreserveFormatting:=False)
        oFld.Code.Text = StrTmp
      End With
    Loop
  End With
End With
Set RngTmp = Nothing: Set RngFld = Nothing: Set oFld = Nothing
End Sub

 

With the above example, what you input is a textual representation of what the field code should look like once you're done (after allowing for the VBA requirements for special characters such as quote marks). The above 'Demo' example inserts the field code from my previous post at the start of the document. If you want to insert it elsewhere, you'll need to designate the applicable range (e.g. by passing the range of a bookmark, as in 'Set Rng = ActiveDocument.Bookmarks("MyBookmark").Range' ).

 

None of the available methods for inserting compound fields codes into a document is straightforward, and the task becomes quite convoluted once you start dealing with multiple nestings. Accordingly, I do have to ask why you'd want to go about inserting the fields programmatically  rather than simply creating a document with the mergefields in-situ, using whatever code you need to open that, attaching a data source (if not already attached) and executing the merge.

Cheers
Paul Edstein
(Fmr MS MVP - Word)

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated December 15, 2022 Views 2,633 Applies to: