Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Mailmerge and Fax / August 2006

Tip: Looking for answers? Try searching our database.

VBA Code for updating e-mail merge

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Albert - 13 Aug 2006 02:04 GMT
Hello!
I have succesfully created an email merge. However, there is one field which
does not update itself when e-mailed: its an image I included using the path
to the image inside an INCLUDEPICTURE field. All email recipients get the
same image (the one for the first record), and not the intended one for each
record.
Perhaps you could help me with the vba code I need to make the image update
correctly when e-mailed? Maybe something involving an event handler for
mailmerge?
I thank you in advance for your time.
Best Regards,
Albert
Albert - 13 Aug 2006 02:10 GMT
Or perhaps you know a simpler solution to my problem? Something that doesn't
involve vba?
Peter Jamieson - 13 Aug 2006 14:23 GMT
Hi Albert -

Funnily enough I'd just been working on this - unfortunately I won't have
time to complete this for at least a few days but the basic idea works. The
particular version I have to hand does not start with an Includepicture
field - it just gets the path you need from the mail merge data source and
stuffs the image in directly. I was going to look at the other method later
(which will now be at least a week away) but maybe this will be enough for
you. If you have experience with WOrd Events and VBA you can ignore some of
the following.

1. Create a new document, connect it to your data source, and insert one
merge field (otherwise you can't merge) and a bookmark named "mybm" where
you want thte picture to go.

2. Open up the VBA Editor and
a. insert a class module.
b. name it EventClassModule in the properties box
c. Copy the following code into the module:

Private Sub App_MailMergeBeforeRecordMerge( _
 ByVal Doc As Document, _
 Cancel As Boolean)
Dim strImagePath As String
Dim rngBookmarkToReplace As Range
Dim shpToReplace As InlineShape

' This routine replaces bookmark "mybm" by
' an inline picture whose path is speacified in
' the mail merge data source field "imagePath"
' This broadly replaces the need to insert a
' nested { INCLUDEPICTURE "{ MERGEFIELD ImagePath }" }
' field, which does not usually work when you merge to
' e-mail

' set the range variable to our placeholder bookmark
Set rngBookmarkToReplace = _
 Doc.Bookmarks("mybm").Range

' delete any existing text (this is needed for records after record 1)
rngBookmarkToReplace.Text = ""

' construct the image path. In this case we
' just get it from the data source

strImagePath = _
 Doc.MailMerge.DataSource.DataFields("imagePath")

' Insert the image. You might want to have some code
' that does something else if the file cannot be found
Set shpToReplace = _
 Doc.InlineShapes.AddPicture( _
   FileName:=strImagePath, _
   Range:=rngBookmarkToReplace)

' Set mybm to "cover" the inserted link so it is easy to delete the old
Hyperlink

Doc.Bookmarks.Add Name:="mybm", Range:=shpToReplace.Range

Set rngBookmarkToReplace = Nothing
Set shpToReplace = Nothing

End Sub

3. Insert an ordinary module (the name does not matter) and insert the
following code:

Dim x As New EventClassModule

Sub autoopen()
Set x.App = Word.Application
End Sub

4. Save and close the document. Open it to trigger the autoopen, then
perform a test merge.

NB, if you start changing the code you may find that you need to re-run your
autoopen code again, and/or save/close/open the document.

Peter Jamieson

> Hello!
> I have succesfully created an email merge. However, there is one field
[quoted text clipped - 12 lines]
> Best Regards,
> Albert
Albert - 14 Aug 2006 02:29 GMT
Hi Peter.
Let me begin by thanking you for your help.
I followed your instructions with no problems. However, after I did the
changes and saved, I reopened the document and got an error message from the
autoopen procedure.
Also, I included a column on my data table called "imagePath" including the
path to the images I want to send. Should I use "\" or "\\" ?
Finally, I executed a test merge and it sent no picture at all, just the
original bookmark text.
What should I do?
Best Regards and thank you,
Albert
Peter Jamieson - 14 Aug 2006 08:35 GMT
Hi Albert,

Regret for serious follow-up from me you'll have to wait a few days, but my
best guess is that the reason AutoOpen has not worked is because
a. you didn't include the line

Dim x As New EventClassModule
or
b. you created EventClassModule as an ordinary module rather than a Class
Module
or
c. the name in the "Dim" does not match the class module name
or
d. there is an error in the class module that prevents it from running. I
didn't copy the code straight out of my template so there could well be a
typo, or the usual problem with lines of VBA getting screwed up when you
copy from a message into the VB Editor.

Should I use "\" or "\\"

Use \ (single backslash)

If you want to be able to use the path for either an includetext approach or
this approach, you /may/ need to store double backslashes for the sake of
the INCLUDETEXT (although I think it works OK without when the path is
stored in the data source rather than as a literal string in the INCLUDETEXT
field) and process the file name in the VBA to remove the additional
backslashes.

Peter Jamieson
> Hi Peter.
> Let me begin by thanking you for your help.
[quoted text clipped - 10 lines]
> Best Regards and thank you,
> Albert
Albert - 15 Aug 2006 02:50 GMT
Hi Peter,
Checked everything you said, but still doesn't work.
Please let me know when you tackle it...
Best regards,
Albert
Peter Jamieson - 17 Aug 2006 21:27 GMT
My mistake. You need the following line at the top of the EventClassModule
module:

Public WithEvents App As Word.Application

Peter Jamieson
> Hi Peter,
> Checked everything you said, but still doesn't work.
> Please let me know when you tackle it...
> Best regards,
> Albert
Albert - 17 Aug 2006 21:55 GMT
YES YES YES.....
Worked Great...
Thank you sooooooooooo much...
Albert
Peter Jamieson - 17 Aug 2006 21:59 GMT
Great-thanks for the feedback.
Peter Jamieson
> YES YES YES.....
> Worked Great...
> Thank you sooooooooooo much...
> Albert
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.