Mail Merge Functions
- 4 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.
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.
In the template’s mail merge design view (Mail Merge Design View on the Mail Merge Ribbon page), a mail merge field is displayed as the name of the specified data field enclosed in square brackets. If a mail merge field is used in a cell formula as one of its elements, the entire formula is displayed in the cell.
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.
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.
Target_range is a reference to the cell range in which a picture should be inserted.
Width and height are values that specify the desired width and height of the picture in pixels.
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.
