April 9, 2024
Contribute to the Microsoft 365 and Office forum!
Click here to learn more 💡
May 10, 2024
Word Forum Top Contributors:
VBA macro form/bulk letter with Excel data - If clauses (MergeField)
Report abuse
Thank you.
Reported content has been submitted
Report abuse
Thank you.
Reported content has been submitted
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.
Paul Edstein
(Fmr MS MVP - Word)
Report abuse
Thank you.
Reported content has been submitted
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.
Report abuse
Thank you.
Reported content has been submitted
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.
Report abuse
Thank you.
Reported content has been submitted
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?
Charles Kenyon
Sun Prairie, Wisconsin
wordfaq[at]addbalance[dot]com
Legal site: https://addbalance.com
Report abuse
Thank you.
Reported content has been submitted
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.
Paul Edstein
(Fmr MS MVP - Word)
Report abuse
Thank you.
Reported content has been submitted
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: