Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    mountains, North Carolina, USA
    Posts
    64
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Inserting an image via Mail Merge

    Using Office 2007. I have a database set up in an Excel spreadsheet. Setting up the text data to insert into a Word Formletter is no big deal. What I'm not sure how to accomplish is how to insert images into the form letter.

    Can I insert a full size image into a cell in Excel then mail merge that cell just like it was text?

    Is there some other method for automatically inserting images into a Formletter?

    Thanks in advance for any help.

    Larry P

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi Larry,

    AFAIK you can't insert the images directly from cells in the workbook - the images need to be in external files, whose names (and, perhaps, paths) appear in the workbook.

    When embedding a mailmerge field in an INCLUDEPICTURE field for the purpose of merging graphics:
    1. the filepaths to the fields ordinarily need to have the separators expressed as '\\' or '/' instead of the usual '\';
    2. the pictures usually won't show until you refresh the fields (eg Ctrl-A, then F9); and
    3. even after updating the images, they remain linked to the image files, which can be an issue if you later delete the image or you need to send the merged output to someone else.

    The following field construction addresses all three of the above issues:
    {IF {INCLUDEPICTURE {IF TRUE "C:\Users\My Document Path\Pictures\«Image»"} \d} {INCLUDEPICTURE {IF TRUE "C:\Users\My Document Path\Pictures\«Image»"} \d}}
    or
    {IF {INCLUDEPICTURE {IF TRUE "C:\Users\My Document Path\Pictures\{MERGEFIELD Image}"} \d} {INCLUDEPICTURE {IF TRUE "C:\Users\My Document Path\Pictures\{MERGEFIELD Image}"} \d}}
    After running a mailmerge coded this way, you'll have the correct, unlinked image for each record.

    This form of field construction can be very useful where the filepath for the images is held in the mailmerge data source, in which case you could use:
    {IF {INCLUDEPICTURE {IF TRUE "«FilePath»\«Image»"} \d} {INCLUDEPICTURE {IF TRUE "«FilePath»\«Image»"} \d}}
    or
    {IF {INCLUDEPICTURE {IF TRUE "{MERGEFIELD FilePath}\{MERGEFIELD Image}"} \d} {INCLUDEPICTURE {IF TRUE "{MERGEFIELD FilePath}\{MERGEFIELD Image}"} \d}}

    Note 1: You need a path separator between the filepath mergefield and the image mergefield. If that separator is included in the source data, it can be omitted from the field above construction but leaving it there has no adverse effects either.

    If the path data are included with in the image field, you can use:
    {IF {INCLUDEPICTURE {IF TRUE «Image»} \d} {INCLUDEPICTURE {IF TRUE «Image»} \d}}
    or
    {IF {INCLUDEPICTURE {IF TRUE {MERGEFIELD Image}} \d} {INCLUDEPICTURE {IF TRUE {MERGEFIELD Image}} \d}}
    For what it's worth, *provided* the path has the separators expressed as '\\' or '/', you can retain the links by omitting the all-encompassing IF test and the images will display correctly without the need to refresh the fields after completing the merge. For example:
    {INCLUDEPICTURE {IF TRUE "C:\\Users\\My Document Path\\Pictures\\«Image»"} \d}

    Note 2: The field brace pairs (ie '{ }') for the above examples are created in the body of the document via Ctrl-F9 - you can't simply type them or copy & paste them from this message. Likewise, you can't type or copy & paste the chevrons (ie '« »') - they're part of the actual mergefields, which you can insert from the mailmerge toolbar.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    New Lounger
    Join Date
    Mar 2013
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have followed the instructions above, but still am unable to move the document on to a different PC with out the images disappearing.

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by Nevstar View Post
    I have followed the instructions above, but still am unable to move the document on to a different PC with out the images disappearing.
    The only version of the above field coding that is possible with is the last one - because the links remain in the file. Before moving the file to another PC, you should break the links (eg Ctrl-A, Ctrl-Shift-F9), which is what the other field constructions do for you.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    New Lounger
    Join Date
    Mar 2013
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    An almost simple explanation of how to merge graphics using Excel and Word

    I use this all day long to send postcards, envelopes and letters with different before and after pictures of mobile sites of mobile phone images and also to sell SEO services.
    WSO6seopostcards.png WSO3-env-postcard1.png


    I have mastered it now but the merge code is notoriously unstable. Even after you get it working if you save your template under a different name the code comes undone. If you merge to a different spreadsheet it comes undone. If you try to use the position tool on a graphic it comes undone. If you open a template on a different computer it comes undone. Having said that and after playing with it for several months I discovered all the quirks and rules to using it and got it working smoothly.

    The basic steps go like this:
    1. Open a document and save it right away as a .doc file not a .docx file
    2. Link a spreadsheet to the document with standard mailmerge functions
    3. Insert a picture of the size you expect to output into the document using the "link to file" function, don't use "link" which is the first option.
    4. Hit alt f9 and you will see something like this {INCLUDEPICTURE"../../../../../../prospects/Atlanta/dentist/cell/cosmeticaft.JPG"\* MERGFORMAT \d} where cosmeticaft.JPG is a picture I choose that is the approximate size of all the ensuing pictures that will fill this spot in the document.
    5. Edit it so it looks like this :{INCLUDEPICTURE"c:\\prospects\\Atlanta\\dentist\\ cell\\{ MERGEFIELD cellaft}.JPG"\* MERGFORMAT \d} where { MERGEFIELD cellaft} is the mergefield column in the spreadsheet. The graphics have to have the same name as the names in this column. You have to insert this mergefield from the mailmerge menu where it says insert merge field.
    6. Hit alt f9 again and the document will revert to its normal appearance.
    7. Output the document using edit documents not email or print documents.
    8. Use ctrl A to select all the documents in the document output file and then hit f9. If you have used a text box to hold the document image you have to click on the picture and then hit f9. The images will all change. If they didn't then you named them wrong either in the graphics file or the spreadsheet or you didn't set up the merge code correctly. If you accidentally insert a space somewhere in the code it won't work.

    You don't need the IF TRUE function if you do the above steps.

    Rule: Your graphics have to be in a lettered drive like c: or d: or f: etc. I have never been able to get them to work from a "my documents" file.

    Once you do get it working it works fantastic and getting around the quirks becomes second nature. You can buy an add on <product 'advertisement' deleted>.

    Hope that helps
    Rainer
    Last edited by macropod; 2013-03-31 at 06:34. Reason: Deleted product 'advertisement'

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi Rainer,

    Re Step 1: Since mailmerge works quite happily with .doc files (and has done so for over 20 years), I question the validity of this step.

    Re Step 4: Selecting a field and pressing Shift-F9 to toggle its display is preferable to using Alt-F9. The former toggles only the selected field, whilst the latter toggles all fields and can confuse users when the field they were interested disappears off screen in a document with lots of fields. The insertion of an INCLUDEPICTURE field does not generate path separators denoted by '/' - they're always denoted by '\\'. The fields will work equally well with '\\' and '/'. Furthermore, you don't need the '\* MERGFORMAT' switch.

    Re Step 6: Pressing Alt-F9 after editing a field per your step 5 will leave it displaying the original graphic - hardly what one would want. You'd need to select the field and press F9 again to get it to update to reflect the edit. If Shift-F9 is used to toggle the display 'on', all that's needed to toggle it 'off' and update it is F9.

    Re Steps 7 & 8: Your assertion that you don't need the IF TRUE function merely demonstrates that you don't understand what it does, which is to eliminate your step 8 (which you otherwise need to remember to do after every mailmerge) and to prevent errors when sending direct to printer at step 7.

    I can't imagine why anyone would want to buy an addin for this when it can be done quite easily for free.

    Finally, none of what you've posted addresses point 3 of post #2 above.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    New Lounger
    Join Date
    Mar 2013
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you Paul,
    I wrote this post for those who are not technically inclined as a simple procedure that works.

    I would guess only 20% of us out there would be willing to write a conditional IF TRUE statement when editing the merge code so I did this for the other 80% because it works and is relatively easy to demonstrate by using a video to show the steps. As for trying to describe it here in writing, not so much. If you have a YouTube or other video that shows how to do this I would love to see it.

    I am not sure why you made the comment about Step 1. When you create a new document in Word 2007 for example the default is a .docx document. I am simply saying save it as a .doc and work in the compatible mode when you are doing this. If you are doing this in Word 2003 then yes you don't need that step.

    Good point about shift f9 to view. I should have mentioned that.
    As for your points about INCLUDEPICTURE and MERGEFORMAT switch, true as to what you said if you are editing this as one who is experienced and doesn't mind playing around with it.
    As for those who are not as experienced, the method works. Word writes those default codes for you including INCLUDEPICTURE and the MERGEFORMAT switch when you use "link to file" to insert the picture. I don't touch it to keep it simple with one less instruction for someone new to this. Another bonus once you get used to doing it this way, is you can store the replacement text that is needed to edit the mergecode in notepad and use MS Word's search/ replace function to fix it as needed through the whole document by copying and pasting from notepad. Not sure if that would work with how you are showing to do it as I have never tried it. Can you use search/replace doing it as you describe?

    As for not addressing point 3 of the post as you mentioned, true again. This is an alternate method and wasn't relevant to that.

    As for paying for it, I agree, if your technically inclined and you can do this, don't pay for it.


    Cheers from Canada
    Rainer

  8. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by rainer0651 View Post
    I am not sure why you made the comment about Step 1. When you create a new document in Word 2007 for example the default is a .docx document. I am simply saying save it as a .doc and work in the compatible mode when you are doing this. If you are doing this in Word 2003 then yes you don't need that step.
    And what, exactly, is the point of saving it in the doc format if you're using Word 2007 or later? Apart from anything else, doing so denies access to any content controls one might want to embed in the output. It is a pointless and possibly harmful step.
    You Step 4, about the path separators in INCLUDEPICTURE fields inserted by Word is simply wrong and users do not need to change them to the format you suggested - that's how they appear anyway. Your 'if you are editing this' is irrelevant - even if you're not editing it your construction is wrong. As for:
    Word writes those default codes for you including INCLUDEPICTURE and the MERGEFORMAT switch when you use "link to file" to insert the picture
    It never uses the path separators you posted and, with Word 2007 & later, it also doesn't even generate an INCLUDEPICTURE field at all in the docx format. Furthermore, your advice does not address what to do when the path is included in the mergefield or is a separate mergefield.

    In summary, your 'An almost simple explanation' is incomplete, inaccurate and more convoluted than doing it as outlined in my post for whichever of the various possible scenarios matches the user's needs. No particular 'technical' skills are required. For example, take the first scenario (one of the most complex) from my first post:
    • Position the insertion point where you want the image to appear and press Ctrl-F9 three times to produce three nested fields, thus { { { } } }.
    • fill in the fields so you get {INCLUDEPICTURE {IF TRUE "C:\Users\My Document Path\Pictures\"} \d}, keeping the spaces where indicated.
    • insert the mergefield for the image names, thus {INCLUDEPICTURE {IF TRUE "C:\Users\My Document Path\Pictures\«Image»"} \d}.
    • Select the fields & copy them to the clipboard.
    • Press Ctrl-F9 to wrap the existing fields in another field, thus { {INCLUDEPICTURE {IF TRUE "C:\Users\My Document Path\Pictures\«Image»"} \d} }.
    • Type 'IF' inside the left-most field brace, keping the space to the right of the 'IF' and paste the clipboard content inside the right field brace, keeping the space to the left of where you paste (the pasted field quite possibly won't be visible You should now see:
    {IF {INCLUDEPICTURE {IF TRUE "C:\Users\My Document Path\Pictures\«Image»"} \d} }
    or:
    {IF {INCLUDEPICTURE {IF TRUE "C:\Users\My Document Path\Pictures\«Image»"} \d} {INCLUDEPICTURE {IF TRUE "C:\Users\My Document Path\Pictures\«Image»"} \d}}.
    • Select the whole field and press F9 (this step is optional).
    • Run your mailmerge.
    A comprehensive, relatively simple and reliable solution that does not require manual intervention after every mailmerge.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  9. #9
    New Lounger
    Join Date
    Mar 2013
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hey Paul,
    I think I see what is happening here. I have not tried doing it your way before and I can see from what you are writing you have never tried it my way before. Just to make it clear using Word 2007 "my way" is putting it in .doc compatible mode and letting Word do its thing to put in its own coding and then editing that. Your way is to leave it in .docx mode as the default and then manually insert coding.
    So I tried it your way only because prior to this I never had any real clear instructions as to how to do it. Also prior to this I think I was putting in an extra space somewhere and screwing it up. But I did succeed per your instructions this time (I think).

    So I created a video using both methods and comparing the two. If you watch it all the way through to the second half it shows how I am doing it.
    There are pros and cons to both ways.

    The Pros for "your way" is that you don't have to use ctrl A and f9 on the batch output file and you can see the images change as you flip through the records from the template file. The Cons are you have to be dead on with the way you enter your coding, space it the wrong way and it doesn't work. Also you can't resize the images by grabbing the image handles. So you are stuck with having to use an image resizer program to resize a large amount of graphics in a batch file which would create pixel degradation or you would have to use some other program to resize which is too much work.

    The Pros for "my way" is you only have to edit one small portion of the code that MS Word creates for you. You don't have to type it out from scratch. It takes only 10 seconds or so to do this and it is a whole easier not to make a mistake. As for it being harmful, my computer would have been dead a year ago if that were the case. I have been using this successfully for over a year and training other to do it using a video explanation. The other plus point is that you can actually resize the image by grabbing the image handles and it preserves image quality for printing. Trying to explain this in a post in text form has simply never worked for me. The Cons for this method is the extra step of having to select the graphics and use f9 to get them to appear. If the image is in a text box (for when you are inserting an image into another image) then you can't use Ctrl A, you have to tediously click on each image and hit f9.

    So if you know of a way of reszing the images without losing quality doing it your way I would sincerely love to hear about it.
    Here is the video comparing the two ways: http://youtu.be/22YhcJyjVMk
    Cheers again
    Rainer

  10. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by rainer0651 View Post
    I can see from what you are writing you have never tried it my way before.
    Wrong. How else do you suppose I know you're wrong about the path separators Word uses? If you spent more that a few nanoseconds investigating what I have written about field usage in Word (including tutorials on the subject), you might have refrained from such a rash claim.
    Also you can't resize the images by grabbing the image handles. So you are stuck with having to use an image resizer program to resize a large amount of graphics in a batch file which would create pixel degradation or you would have to use some other program to resize which is too much work.
    The proper way to constrain image sizes via a mailmerge, bearing in mind that the images one deals with may come in a variety of aspect ratios, is to create a single-cell table with a fixed column width and row height, then insert the fields into that. The particular benefit of this approach is that regardless of the aspect ratio, every image will be allocated the same vertical & horizonal space in the document and, even if a given image doesn't use all the horizontal or vertical space available, the layout of the remainder of the document will be unaffected.

    I note you still haven't addressed the inability to insert content controls into a mailmerge main document using the .doc format. Given that you also can't use text or dropodown formfields in either format, that can represent a huge advantage in sticking with the .docx format in Word 2007 & later.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  11. #11
    New Lounger
    Join Date
    May 2013
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    First of All, I wanted to thank both of you for your posts. They allowed me to decipher somethings.
    Paul, ultimately I went with your setup and everything seems functional - but I do have a couple questions.

    Notes you should know:
    Office 2010
    .Docx format

    1. I set up my image code as follows: {INCLUDEPICTURE C:\Users\Arad\Dropbox\Daras Images\Earrings\Round\{ MERGEFIELD Image}"} \d}

    When I went to Inset My Image from the menu instead of what you show of: «Image» I got: { MERGEFIELD Image}

    Is there a difference? Also as soon as I did it it turned into an image.

    Later I was also not able to nest the additional if formula you outlined in the 6th bullet point. Word automatically turned it again into an image and I was not able to go back to the formula like I was for the first nested IF. So despite it still working for me with the above formula are there any drawbacks?

    2. I am able to go to "edit individual documents" and all the images are different. However if I save the file and reopen at a later date, Word says image not found. Do the images not save in the word file? Any possible way to do so?

    Thank you so much for your help.

  12. #12
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Re 1: That only suggests you had Word's filed code display toggled 'on' whilst doing the process. It's of no particular consequence.

    The inability to access the field code for INCLUDEPICTURE fields is one of the quirks of using docx files. I don't know why MS did that to them, as other 'link-type' fields don't behave that way.

    Re 2: If you used the process I outlined, your mailmerge output files should have the images, but no links to them. So I wouldn't expect the 'image not found' message from those. You are liable to get that message from the mailmerge main document, though, as it will be unable to resolve the field code until the merge is executed.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  13. #13
    New Lounger
    Join Date
    Jun 2013
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I hope I can use this thread to help educate me and also solve my problem - it seems very similar. I want to create Name Tags using the Avery 5147 sticky tag label. And of course I want to have Word 2003 print both a name (coming from Excel 2003) and a .jpg of each attendee.

    The .jpgs are individual files with file names "similar" to those that are in the Excel DB.

    I had read thru the steps in above posts but am not too sure those will work with my situation. I'm sure hoping you knowledgeable folks can help me get this done! Thanks.

  14. #14
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Willow Grove, Pennsylvania, USA
    Posts
    205
    Thanks
    4
    Thanked 49 Times in 40 Posts
    Quote Originally Posted by hotkey View Post
    The .jpgs are individual files with file names "similar" to those that are in the Excel DB.
    Unfortunately, that isn't quite good enough. The fields that macropod showed in post #2 require that the data in the Excel DB are exactly the file names of the pictures, or else the merge won't find the files. Your choice is whether to rename the files or to make the DB match the existing file names.

    This is just another example of how computers are fast but stupid. You can't tell a computer program, "You know what I meant, so fix it!"

  15. #15
    New Lounger
    Join Date
    Jun 2013
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by jjfreedman View Post
    Unfortunately, that isn't quite good enough. The fields that macropod showed in post #2 require that the data in the Excel DB are exactly the file names of the pictures, or else the merge won't find the files. Your choice is whether to rename the files or to make the DB match the existing file names.
    "
    OK. Thanks for the reply and help! I'm willing to rename 400 .jpg files since our class reunion committee will likely use again and again. But I have questions for sure.

    (1) If the Excel DB has FIRST and LAST names in separate cells, how do I name the .jpg files?

    (2) The steps above seem like I can follow them, once I know where to start. How do I make a "custom" mail merge anyway?

Page 1 of 3 123 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •