Solved: Mail Merge IncludePicture into Mutiple Documents

[MDY]

Hi,
I was wodering if anyone knows how to mail merge mutiple pictures into individual word documents?

What I have is a number of pictures that I need to insert from "Folder - Bridges" into a template Called "Bridge Template". Each of the pictures from this folder needs to be inserted into the template as attached but in seperate documents. The document is then saved and turned into a PDF using a PDF print program.

How can I use VB to automatically go through and insert each of the pictures into the template and to provide the appropriate caption and save the file with the same file name as the caption?

I've attached the Bridge Template.doc and the folders-bridges.jpg below to help explain.I hope I've provided enough for you to go on. I think this one might be a bit difficult but your help would be great.

Thanks

MDY

[MDY]

Hi,
Please find attached here an example of the files which I would like attached into seperate documents. The Caption of the picture and File name should macth the file name of the picture.

Thanks for all your troubles.

Cheers
MDY

[macropod]

Hi MDY,

You can make a data table of the bridge filenames in, say, Excel or a Word table with a heading row. Don't use the filename's extension in the data table. If the images are in different folders, you'll need to have another column in your data table for the filepaths, including the final path separator. This data table then becomes the datasource for a mailmerge using your bridges document as the mailmerge main document.

To use the bridges document as the mailmerge main document, see:
http://www.gmayor.com/mail_merge_graphics.htm

[MDY]

Hi Macropod,
Sorry mate but I'm still trying to get this solution to work. I can get the mail merge to put the file path into the document as required but can not get the picture to display properly. I have the path coming from the excel file and it is surrounded by the ctrl+F9 function using include picture and the file path pastes correctly. I'm pretty stumped on this one and have tried to get the picture to display using a number of different combination of the formula but with no luck. Would you mind having a look at the two files I am using adn provide some suggestions? Excel File with Picture Name and File Path Attached. Word Template Doc in next post.

Thanks
MDY

[MDY]

Hi macropod,
Please find attached the word doc in which I am trying to merge the file path to the picture and display.

Thanks mate.
MDY

[macropod]

Hi MDY,

You should be able to use an INCLUDEPICTURE field coded as:
{INCLUDEPICTURE "C:\\Pictures\Bridges\\«BridgesName».jpg"}

After running the mailmerge, you may also need to select the whole output document (Ctrl-A) then update the fields (F9).

If you want, you can then unlink the fields (Ctrl-A then Ctrl-Shift-F9).

[MDY]

Hi Macropod,
I've got the mail merge to work, funnily enough to get the pictures to show I have to right click on the {INCLUDEPICTURE "C:\\Pictures\Bridges\\«BridgesName».jpg"} field and click edit filed.... then OK that. Once I select OK the picture will then show. unfourtunately as per the attachement using the Ctrl+A and F9 combination does not work properly, I've included a screen dump to show the results. Is there a way to have the pictures show properly as soon as the merge is complete so that I dont have to refresh the IncludePicture link? The other problem I have is that each of the documents is returned in the same file. Is it possible to have each of the "letters" open in seperate documents so that each template can be saved individually?

Thanks
Mdy

[macropod]

Hi MDY,

You *may* be able to force the mailmerge to automatically insert the correcdt pictures and delete the links by embedding your existing field in a simple IF test:
{IF =1 {INCLUDEPICTURE "C:\\Pictures\\Bridges\\«BridgesName».jpg"}}

As for merging to different files, if you're using Word 2003 or later and you've got Adobe Acrobat Professional 7 (I think) or later installed, you could use that to merge the output to separate files (see the Adobe menu in Word). Otherwise you'll need a document splitter macro - there's a few about on the web.

[MDY]

Hi macropod,
I'd really like to use a macro if possible. I found this one by Doug Robbins edited by Graham Mayor from:
http://www.gmayor.com/individual_merge_letters.htm
Sub SplitMerge()
' Macro created 16-08-98 by Doug Robbins to save each letter created by a
' mailmerge as a separate file.
' with modifications by Graham Mayor 16-06-03 & 08-10-04
Dim Title As String
Dim Default As String
Dim MyText As String
Dim MyName As Variant
Dim MyPath As String
Selection.EndKey Unit:=wdStory
Letters = Selection.Information(wdActiveEndSectionNumber)
Selection.HomeKey Unit:=wdStory
Counter = 1
Default = "Merged"
MyText = "Enter a filename. Long filenames may be used."
Title = "File Name"
MyName = InputBox(MyText, Title, Default)
If MyName = "" Then
End
End If
Default = "H:\MPMPictureTemplate\Bridge Templates\"
Title = "Path"
MyText = "Enter path"
MyPath = InputBox(MyText, Title, Default)
If MyPath = "" Then
End
End If
While Counter < Letters
Application.ScreenUpdating = False
Docname = MyPath & LTrim$(Str$(Counter)) & " " & MyName & ".doc"
ActiveDocument.Sections.First.Range.Cut
Documents.Add
'Documents are based on the Normal template
'To use an alternative template follow the link.
With Selection
.Paste
.EndKey Unit:=wdStory
.MoveLeft Unit:=wdCharacter, Count:=1
.Delete Unit:=wdCharacter, Count:=1
End With
ActiveDocument.SaveAs FileName:=Docname, FileFormat:=wdFormatDocument
ActiveWindow.Close
Counter = Counter + 1
Application.ScreenUpdating = True
Wend
End Sub


Problem is that it only splits the doc into 2 rather then splitting the whole document into 3 as required. Is there any way of getting this one to work properly? Sample doc and data attached.

Thanks for all your troubles!
Mdy

[MDY]

Hi All,
Ive created this Macro as a sample of what can be done manually to get the pictures to show properly after the mail merge is complete, basically this is done as above by right clicking on the IncludePicture link, clicking edit and ok, as below:

Sub ShowPicture()
WordBasic.FormatField Field:= _
"INCLUDEPICTURE ""H:\\MPMPictureTemplate\Photographs\\Bridge (100.934 km).JPG"" "
Selection.MoveDown Unit:=wdLine, Count:=9
WordBasic.FormatField Field:= _
"INCLUDEPICTURE ""H:\\MPMPictureTemplate\Photographs\\Bridge (105.002 km).JPG"" "
Selection.MoveDown Unit:=wdLine, Count:=9
WordBasic.FormatField Field:= _
"INCLUDEPICTURE ""H:\\MPMPictureTemplate\Photographs\\Bridge (109.608 km).JPG"" "
End Sub

Unfourtunately Select All and F9 as above wont worked properly. Is there any way to tell word to update the fields as above of the complete template using a macro so that all the pictures show properly?

Thanks
MDY

[macropod]

Hi MDY,

To process the fields after the merge, you could use:

Sub UpdateFields()
Dim oFld as Field
For each oFld in ActiveDocument.Fields
oFld.Update
oFld.Unlink
Next
End Sub
As for Doug Robbins' macro, I suspect all you need to do is to change Counter = 1 to Counter = 0.

[MDY]

Hi macropod,
Thanks for trying but unfortunately netiher of the solutions worked.

For splitting the pages I tried chnaging the counter = 1 as below to Counter = 0 but unfourtunately I still recieved the same reult. The Splitter would split out the first page but would not go any further.
Selection.EndKey Unit:=wdStory
Letters = Selection.Information(wdActiveEndSectionNumber)
Selection.HomeKey Unit:=wdStory
Counter = 1

Also the code:


Sub UpdateFields()
Dim oFld as Field
For each oFld in ActiveDocument.Fields
oFld.Update
oFld.Unlink
Next
End Sub

Returned a very similar problem to those above, the pictures would come out as being blank. Do you have any other suggestions for either of these problems?

Thanks

Mdy

[macropod]

Hi MDY,

Your continuing problems suggest:
1. The INCLUDEPICTURE fields have an error (eg filenames and/or paths for the pictures are incorrect)
2. The merged output has only two Sections - which implies only two records were merged.

Re: 1. A look at your Wordbasic code (why are you using Wordbasic in 2009?) shows significant errors in the formulation of the INCLUDEPICTURE fields. What you're getting shows:
"INCLUDEPICTURE ""H:\\MPMPictureTemplate\Photographs\\Bridge (100.934 km).JPG"" "
for example. What you should have is more like:
INCLUDEPICTURE "H:\\MPMPictureTemplate\\Photographs\\Bridge (100.934 km).JPG"
Note the change of 'MPMPictureTemplate\Photographs' to 'MPMPictureTemplate\\Photographs' - I suspect this is due to replicating an error in my post of 19 March. Just to confirm, what do you see as the INCLUDEPICTURE field coding if you press Alt-F9 after running the merge but before trying to do any further processing?

[MDY]

Hi Macropod,
Brilliant mate!!! The reason why i couldn't refresh the pictures was becasue in my code I did only had areas where I only had a single "\" rather then double "\\" through the file path. By pressing altA + F9 it also shows all of the code that the document uses to process the function. I found that code which was hidden was still contained in the background. By making this hidden code show I was able to remove this extra code and stop the document from producing the pictures which wouldn't show properly due to an incorrect file path being held in the document.

And guess what! Now that we have "\\" in all the file paths of the merge document the pictures now refresh properly when selecting ctrl a + F9 to refresh all the letters.

Now the only problem I am having is splitting the documents out using the macro. I am still having the problem where the macro is only splitting out the first page which starts at number 1. the second document that is saved contains the rest of the mail merge letters. Could you possibly have a look at the macro and see what is going wrong. I will be sure to post a copy of the complete solution for all to use with a process sheet and all relevant files once we have a solution. Please find the word doc attached.

Thanks Macropod, were nearly there!

Mdy

[macropod]

Hi MDY,

Three problems:
1. Your code is in the file containing the merged output
2. Your code is in the 'Document' module, not a code module
3. Three variables were not defined.

To solve these:
1. Add the code to a 'normal' module in your 'Normal.dot' file
2. Add the following variables to your code:
Dim Letters As Integer
Dim Counter As Integer
Dim Docname As String
Then, when you've run the mailmerge, you should be able to press Alt-F8, select the 'SplitMerge' macro and run it.

FWIW, I'd suggest changing the ''Docname =' line to 'Docname = MyPath & Trim(MyName) & " " & Format(Counter, "000") & ".doc"'. That means all files will start with the same name, rather than the Counter value. Using 'Format(Counter, "000")' also means a name sort for the split files will sort the first 1000 split files correctly (that should be enough for most purposes).

[MDY]

Hi Macropod,
This is almost complete but I just had one last question. Is it possible when saving the individual files, for the file name of the picture that is inserted into the template to form part of the file path of the new doc that is being saved?

Thanks

Mdy

[macropod]

Hi MDY,

The only way to get the picture name after the mailmerge has been run is by querying the INCLUDEPICTURE field for the filename, before the field has been unlinked. Alternatively, if you insert the «BridgesName» mergefield somewhere in the document, you could query that location (not the field, since it'll be gone) as part of your naming process.

[MDY]

Sounds like it might be a little bit complicated, any suggestion on how the code might look or what the best way of achieving this might be?

Thanks Heaps

Mdy

[macropod]

Hi MDY,

Not all that complicated, really.

If you make a copy of your «BridgesName» mergefield the first paragraph in each Section (ie with nothing else in that paragraph), you can then use the following, modified version of the macro:

Sub DocSectionSplitter()
' Macro created 16-08-98 by Doug Robbins to save each letter created by a
' mailmerge as a separate file.
' with modifications by Graham Mayor 16-06-03 & 08-10-04
' further modifications (file naming, based on 1st paragraph) by macropod 14 Apr 2009
Dim Title As String, Default As String, MyText As String, MyName As String, MyRange As Range
Dim MyPath As String, Letters As Integer, Counter As Integer, DocName As String
Letters = ActiveDocument.Sections.Count
Counter = 1
Default = "H:\MPMPictureTemplate\Bridge Templates"
Title = "Path"
MyText = "Enter path"
MyPath = InputBox(MyText, Title, Default)
If MyPath = "" Then End
While Counter < Letters
Application.ScreenUpdating = False
Set MyRange = ActiveDocument.Paragraphs(1).Range
MyRange.End = MyRange.End - 1
MyName = MyRange.Text
DocName = MyPath & "\" & MyName & ".doc"
ActiveDocument.Paragraphs(1).Range.Delete
ActiveDocument.Sections.First.Range.Cut
Documents.Add
'Documents are based on the Normal template.
With Selection
.Paste
.EndKey Unit:=wdStory
.MoveLeft Unit:=wdCharacter, Count:=1
.Delete Unit:=wdCharacter, Count:=1
End With
ActiveDocument.SaveAs FileName:=DocName, FileFormat:=wdFormatDocument, AddToRecentFiles:=False
ActiveWindow.Close
Counter = Counter + 1
Application.ScreenUpdating = True
Wend
DocName = MyPath & "\" & MyName & ".doc"
ActiveDocument.SaveAs FileName:=DocName, FileFormat:=wdFormatDocument, AddToRecentFiles:=False
End Sub

[MDY]

Hi,
Please find attacehd a work instruction for the steps to get this to work. The rest of the posts willl contain the files required.

Some of this process was also taken from:
http://www.gmayor.com/individual_merge_letters.htm

Sorry for the lateness but hope it helps.

Thanks
Mdy

[MDY]

Hi,
Attached are the rest of the files required for this to work. You will need to change some of the file paths to match your systems and add 2 folders where required called:
1. Pictures to be Merged - Where the pictures you want to merge need to be placed &
2. Merged Templates - where the new merged files are placed.

Thanks
Mdy

[MDY]

Hi,
Ive found a little bug in this process. On the last merge document after the macro is run you have to then press (Ctrl + V) to paste the content. Once this is complete you can then save the document with the correct detail. Is there any way to get the document to automatically paste the content as part of the process for the last document during the save?

Thanks
Mdy

[macropod]

Hi MDY,

Try changing:
While Counter < Letters
to:
While Counter <= Letters

[MDY]

Hi Macropod,
Excellent the code now pastes the last bit of text. the only problem now is that you have to debug the code on the last page:

ActiveDocument.SaveAs FileName:=DocName, FileFormat:=wdFormatDocument, AddToRecentFiles:=False

is there any way of getting the code to stop the document creation and close the page that the code was running on automatically?

Thanks
MDY

[macropod]

Hi MDY,

Try inserting:
If Counter < Letters Then _
as a new line before:
ActiveDocument.SaveAs FileName:=DocName, FileFormat:=wdFormatDocument, AddToRecentFiles:=False

Update: If the error is being thrown on the 'ActiveDocument.SaveAs' line just before 'End Sub', you could probably now delete that line.