Mail Merge in Word (Sort of)

chipsworld

Board Regular
Joined
May 23, 2019
Messages
92
I have created a Mail Merge form that we use here at work, but one field is a "Control Number" which has four parts.

Part one: Year signifier
Part two: Julian Date (day of year)
Part three: Number (001, 002, 003, 004, etc)
Part four: Unit # (Military)

What I am trying to do is write a VBA Function that will allow the user to enter the Unit name and the Date (auto generate the Julian, which I have figured out), and then incorporate the Merge Field (number) into a single string. THis is not as easy as I thought it would be.

I can do all of the parts except for the MergeField...

How can I incorporate a MergeField into the String for the Control Number?

The end result should look like the below:

1st BN, 75th INF
U20 - 217 - 001

Here is what I have so far, but as you can probably guess, it doesn't work. Can't get past this MergeField thing...

VBA Code:
Private Sub Document_Open()
Dim MyValue As String
Dim Julian As String
Dim jdate As String
Dim ifField As MailMergeField
Set ifField = ActiveDocument.MailMerge.Fields.AddIf(Range:=Selection.Range, MergeField:= _
"NUM"), Comparison:=wdMergeIfNotEqual, CompareTo:="", TrueText:="FieldIfTrue", _
FalseText:="FieldIfFalse")

MyValue = InputBox("Enter the Unit Name", "Enter Control Number Info", "")
Julian = InputBox("Enter the Julian Date", "JULIAN DATE", "")

jdate = DateDiff("d", DateSerial(Year(Julian) - 1, 12, 31), Julian)

Me.CNTRLINPUT.Text = MyValue & vbCr & "U20 - " & jdate & " - " & ifField


End Sub
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

chipsworld

Board Regular
Joined
May 23, 2019
Messages
92
Anyone have any ideas? Maybe I am approaching the process wrong? I know someone on here knows how to do this...
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,930
Messages
5,508,171
Members
408,669
Latest member
AgsikapAko

This Week's Hot Topics

Top