Mail Merge Functions
- 5 minutes to read
To specify where data values of the bound data source will appear in a resulting document after mail merge is performed, add special placeholders to a template. These placeholders are called mail merge fields, and they are created via the special functions listed below. After a mail merge is complete, these functions are replaced with their return values and are not stored in the merged document.
FIELD
The FIELD function is replaced by a data value from the corresponding field in the data source. Use this function in a cell formula to add a mail merge field to the template using the following syntax.
FIELD(“data_field_name”)
Data_field_name is the name of the data source field from which the value should be retrieved.
Result: In a mail merge template, this function inserts the name of the specified data field enclosed in square brackets: [data_field_name]. After a mail merge is complete, this function returns real data values from the data source. If a field doesn’t exist or contains a null value, an empty string returns.
FIELDPICTURE
The FIELDPICTURE function retrieves a picture from the specified data field, resizes it according to the function parameters and inserts the picture into the specified position in the merged document.
Apply the FIELDPICTURE function using the following syntax.
FIELDPICTURE(“data_field_name”, “picture_placement”, target_range, [ignore_aspect_ratio], [offsetX], [offsetY], [width], [height] )
Data_field_name is the name of the data source field from which the picture should be retrieved.
Picture_placement is a case-insensitive string that specifies where a picture should be inserted in the resulting document after a mail merge is performed.
- “range” - The picture is scaled to fit the specified target range.
- “topleft” - The picture is inserted so that its top left corner is located at the specified cell or at the top left cell of the specified range.
Target_range is a reference to the cell range in which a picture should be inserted.
Ignore_aspect_ratio is an optional Boolean parameter that indicates whether a picture’s original aspect ratio should be ignored. If this parameter is not specified or set to FALSE, the picture’s aspect ratio is retained.
OffsetX and OffsetY are values that specify the distance from the left and top of the target range in pixels. These parameters take effect only if picture_placement is set to “range” and ignore_aspect_ratio is set to FALSE.
Width and height are values that specify the desired width and height of the picture in pixels.
Result: In a mail merge template, this function inserts the name of the specified data field enclosed in square brackets: [data_field_name]. After a mail merge is complete, field images appear in the resulting document and the function returns an empty string. If the function parameters are incorrect or an image does not exist in the data source, the default string that is the field name in square brackets returns.
Example
Description
“=FIELDPICTURE(“Picture”, “range”, A1:B2)”
“=FIELDPICTURE(“Picture”, “range”, A1:B2, FALSE)”
Inserts a picture and scales it to fit in the specified range of cells, locking the aspect ratio.
“=FIELDPICTURE(“Picture”, “range”, A1:B2, TRUE)”
Inserts a picture to fit in the specified range of cells without locking the aspect ratio.
“=FIELDPICTURE(“Picture”, “topleft”, A1)”
“=FIELDPICTURE(“Picture”, “topleft”, A1:B2)”
Inserts a picture so that its top left corner is located at the specified cell or at the top left cell of the specified range.
“=FIELDPICTURE(“Picture”, “topleft”, A1, 100)”
“=FIELDPICTURE(“Picture”, “topleft”, A1:B2, 100)”
Inserts a picture so that its top left corner is located at the specified cell or at the top left cell of the specified range, and sets the image width to 100 pixels without retaining the aspect ratio.
“=FIELDPICTURE(“Picture”, “topleft”, A1, 0, 100)”
“=FIELDPICTURE(“Picture”, “topleft”, A1:B2, 0, 100)”
Inserts a picture so that its top left corner is located at the specified cell or at the top left cell of the specified range, and sets the image height to 100 pixels without retaining the aspect ratio.
“=FIELDPICTURE(“Picture”, “topleft”, A1, 120, 100)”
“=FIELDPICTURE(“Picture”, “topleft”, A1:B2, 120, 100)”
Inserts a picture so that its top left corner is located at the specified cell or at the top left cell of the specified range, and sets the image width to 120 pixels and height to 100 pixels without retaining the aspect ratio.
RANGE
At the template creation stage of the mail merge, you can access the range into which the specified template cell will be expanded in a merged document after a template is repeated for each data record, and inserted one under the other in a single worksheet. To do this, use the RANGE function.
RANGE(abs_cell_reference)
Abs_cell_reference is an absolute reference to a cell in a template that will be copied for each data source record in the resulting worksheet.
Result: In a mail merge template, this function returns a value of the cell used as its parameter. In the resulting document, this function returns a cell range into which the template cell was expanded. If an error occurs when the target cell range is retrieved (e.g., when the specified cell reference points to a range that doesn’t belong to a detail, header or footer range), the RANGE function returns the ParameterValue.ErrorInvalidValueInFunction value.
PARAMETER
The PARAMETER function retrieves a mail merge parameter value and inserts it into the merged document.
PARAMETER(“parameter_name”)
Parameter_name is the name of the parameter whose value should be embedded into the resulting document.
Result: In a mail merge template, this function inserts the parameter name with the “Parameters.” prefix enclosed in square brackets: [Parameters.parameter_name]. If the parameter value cannot be retrieved (e.g., when the parameter name is incorrect), this function returns the default string that is [Parameters.parameter_name]. Otherwise, the parameter value is returned.