Filtering MailMerge Word from a hyperlink

[smilla]

I wonder if someone can help me. I have been trying to do this for a couple of days to no avail.

I am using a Word Mailmerge Template to connect to a data source from Access. The Word document will be opened by a hyperlink.



At the moment I have set a macro to merge automatically if the template is opened which is ok but it merges all records.



Ideally, what I would like to do it is to pass a value/bookmark from the hyperlink (i.e. c:/DocA.doc#CompanyA) to the merge template which would then use that value at the criteria of the merge.



This is the code I am playing with at the moment:-



ActiveDocument.MailMerge.DataSource.QueryString = _
"select * from [tblGoEast] where (([GEDID = [value/bookmark))" & ""



What I would like to happen is click on the hyperlink which merges the template with the data from the criteria i.e. Document A with Company A's data only.



Any ideas/advice would be appreciated.



Regards



Smilla

[jamescol]

Smilla,
If I understood your request, I think the following sample should help. The sample code assumes:

1. The document containing the bookmark is MyHyperlink.Doc
2. The bookmark is named MyBookmark

The code places the bookmark's text into a variable named strMyCriteria, then builds a SQL query called strMyQuery using the bookmark.


[vba]

Sub GetBookmark()

Dim strMyCriteria, strMyQuery As String


Documents("MyHyperlink.Doc").Bookmarks("MyBookmark").Select
strMyCriteria = Selection.Text

strMyQuery = "select * from [tblGoEast] where (([GEDID = " & strMyCriteria & "))"


ActiveDocument.MailMerge.DataSource.QueryString = _
"select * from [tblGoEast] where (([GEDID = [value/bookmark))"


End Sub
[/vba]

Let us know if this sample helps.

Cheers,
James

[smilla]

Thanks for your reply, i think you are on the right track but if I have 200 bookmarks for instance, where do I put them? Just at the top of the report?

Let me try to understand how it will work, please bear with me or correct me if I am wrong.

From a webpage the hyperlink to the mailmerge template would be c://MyHyperlink.doc#BookmarkA

When the mailmerge template opens, the BookmarkA is put into the variable "strMyCriteria". The macro then kicks in and merges with a new document but queries the data on "strMyCriteria".

Is this how you have understood it?

Thanks
Smilla

[smilla]

Another issue has arisen:-


"Word could not re-establish a DDE Connection to Microsoft Access to complete the current task".

Any ideas?

[jamescol]

Smilla,
You would basically need to loop through your bookmarks at the beginning of the code and concatenate each contact to strMyCriteria. Then run the SQL query. I modified my sample code to do this for you.

I don't know about the DDE message. When does it appear?

James


[vba]
Sub GetBookmark()

Dim strMyCriteria, strMyQuery As String
Dim bmMyBookmark As Bookmark
Dim blFirstTime As Boolean


With Documents("MyHyperlink.doc")
blFirstTime = True
For Each bmMyBookmark In .Bookmarks


.Bookmarks(bmMyBookmark).Select

If blFirstTime = True Then

strMyCriteria = strMyCriteria & Selection.Text
blFirstTime = False

Else

strMyCriteria = strMyCriteria & "," & Selection.Text

End If


Next
End With

strMyQuery = "select * from [tblGoEast] where (([GEDID = " & strMyCriteria & "))"

ActiveDocument.MailMerge.DataSource.QueryString = _
"select * from [tblGoEast] where (([GEDID = [value/bookmark))"

End Sub
[/vba]

[smilla]

Thank you James for your time on this.

With regards to the DDE Connection Error, I had been using a complete db (i.e. not split) so I think it was opening too many versions of the db which then locked me out.

I have now tried your code and it is merging but not by criteria.

This is the complete code I am using:-

Private Sub Document_Open()



Dim strMyCriteria, strMyQuery As String

Dim bmMyBookmark As Bookmark

Dim blFirstTime As Boolean



With Documents("Test.doc")

blFirstTime = True

For Each bmMyBookmark In .Bookmarks



.Bookmarks(bmMyBookmark).Select



If blFirstTime = True Then



strMyCriteria = strMyCriteria & Selection.Text

blFirstTime = False



Else



strMyCriteria = strMyCriteria & "," & Selection.Text



End If



Next

End With



strMyQuery = "select * from [tblDetails] where (([Authname = " & strMyCriteria & "))"



ActiveDocument.MailMerge.DataSource.QueryString = strMyQuery

With ActiveDocument.MailMerge

.Destination = wdSendToNewDocument

.MailAsAttachment = False

.MailAddressFieldName = ""

.MailSubject = ""

.SuppressBlankLines = True

With .DataSource

.FirstRecord = wdDefaultFirstRecord

.LastRecord = wdDefaultLastRecord

End With

.Execute Pause:=True

End With

End Sub


Thanks
Smilla

[jamescol]

Smilla,
It looks like the problem with your query is that the criteria need quote marks. In the structure you are using the query looks like this to the program:

[vba]
strMyQuery = strMyQuery = "select * from [tblDetails] where (([Authname = John, Susy))"
[/vba]

It needs to have quotes around the criteria so it looks like:
[vba]
strMyQuery = strMyQuery = "select * from [tblDetails] where (([Authname = "John, Susy"))"
[/vba]

So try contructing your query with the following sample:

[vba]
Private Sub Document_Open()

Dim strMyCriteria, strMyQuery As String
Dim bmMyBookmark As Bookmark
Dim blFirstTime As Boolean
Dim strQuoteMark As String


strQuoteMark = """"




With Documents("Test.doc")

blFirstTime = True

For Each bmMyBookmark In .Bookmarks



.Bookmarks(bmMyBookmark).Select



If blFirstTime = True Then



strMyCriteria = strMyCriteria & Selection.Text

blFirstTime = False



Else



strMyCriteria = strMyCriteria & "," & Selection.Text



End If



Next

End With



strMyQuery = "select * from [tblDetails] where (([Authname = " & _
strQuoteMark & strMyCriteria & strQuoteMark & "))"


ActiveDocument.MailMerge.DataSource.QueryString = strMyQuery

With ActiveDocument.MailMerge

.Destination = wdSendToNewDocument

.MailAsAttachment = False

.MailAddressFieldName = ""

.MailSubject = ""

.SuppressBlankLines = True

With .DataSource

.FirstRecord = wdDefaultFirstRecord

.LastRecord = wdDefaultLastRecord

End With

.Execute Pause:=True

End With

End Sub
[/vba]

[smilla]

It is coming up with an error:-

run-time error "438"
object doesn't support this object or method and is highlighting the .Bookmarks(bmMyBookmark).Select


I have changed the code slightly from what you sent me but this shouldn't affect anything. Here's the code:

Option Explicit
Const DSource = "D:\Documents and Settings\sam\Desktop\GOEAST\Details.rtf"
Const DFile = "Details.rtf"
Private Sub Document_Open()

Dim strMyCriteria, strMyQuery, thisfile As String
Dim bmMyBookmark As Bookmark
Dim blFirstTime As Boolean
Dim strQuoteMark As String
Dim k As String

thisfile = ActiveDocument.Name

strQuoteMark = """"
With Documents(thisfile)
blFirstTime = True
For Each bmMyBookmark In .Bookmark

.Bookmarks(bmMyBookmark).Select

If blFirstTime = True Then

strMyCriteria = strMyCriteria & Selection.Text
blFirstTime = False

Else

strMyCriteria = strMyCriteria & "," & Selection.Text

End If

Next

End With


k = Trim(Documents(thisfile).MailMerge.DataSource.DataFields("AC_ID").Value)

strMyQuery = "SELECT * FROM " & DSource & " WHERE ((k = " & _
strQuoteMark & strMyCriteria & strQuoteMark & "))"

ActiveDocument.MailMerge.DataSource.QueryString = strMyQuery

With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument

With .DataSource

.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord

End With

.Execute Pause:=True

End With

End Sub

With my bookmarks, I am just going to Insert, Bookmark, typing in Bedford, Add to Location and then repeating myself for Luton etc. Is this the correct way.

From my webpage my link is c:/MyHyperlink.doc#Bedford

Your advice would be appreciated.

Regards
Smilla

[jamescol]

Smilla,
You need to change one of your lines of code. Change
[vba]
For Each bmMyBookmark In .Bookmark
[/vba]

to
[vba]
For Each bmMyBookmark In .Bookmarks
[/vba]

The extra "s" makes a big difference. Your code is telling the progam to search the actual bookmark instead of the bookmarks collection.

The way you are inserting the bookmarks sounds OK.

Try the change and let us know how it works.

Cheers,
James

[smilla]

thank you, it fixed that problem but now I have another saying string too long for my ActiveDocument.MailMerge.DataSource.QueryString = strMyQuery.

I have attached the word doc & rtf if you could have a look at the error for me please. I just can't work out why the string is too long. I shall try something else in the meantime.

Please save it to your C Drive and you may have to test a hyperlink from word to open the doc my a bookmakr to test.

Thanking you
Smilla

[mdmackillop]

Hi Smilla,
Change your document_open code to the following, and step through it watching the Immediate window, with a view of your document in the background and you'll see where things go astray.
MD

[VBA]Private Sub Document_Open()

Dim strMyCriteria, strMyQuery, ThisFile As String
Dim bmMyBookmark As Bookmark
Dim blFirstTime As Boolean
Dim strQuoteMark As String
Dim k As String

ThisFile = ActiveDocument.Name

strQuoteMark = """"
With Documents(ThisFile)
blFirstTime = True
For Each bmMyBookmark In .Bookmarks

.Bookmarks(bmMyBookmark).Select
Debug.Print (bmMyBookmark)
If blFirstTime = True Then

strMyCriteria = strMyCriteria & Selection.Text
blFirstTime = False
Debug.Print strMyCriteria

Else

strMyCriteria = strMyCriteria & "," & Selection.Text
Debug.Print strMyCriteria

End If

Next

End With



strMyQuery = "SELECT * FROM " & DSource & " WHERE (([AC_ID = " & _
strQuoteMark & strMyCriteria & strQuoteMark & "))"
Documents(ThisFile).Activate
ActiveDocument.MailMerge.DataSource.QueryString = strMyQuery

With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument

With .DataSource

.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord

End With

.Execute Pause:=True

End With

End Sub
[/VBA]

[mdmackillop]

Ok, Looked a little further
All your bookmarks are the same location, the "home" position on the document, except MHead which is the whole document (This causes the too long problem). Your code is not actually picking up the bookmark value. You need to create a bookmark by selecting the whole of the first merge field, not the start of it. Call this "Smilla", Replace your criteria code as below. You only need the one bookmark, as this value will be changed with each record, so stepping through bookmarks is not the correct approach
[VBA]
ThisFile = ActiveDocument.Name

strQuoteMark = """"
With Documents(ThisFile)
.Bookmarks("Smilla").Select
End With

strMyCriteria = Selection.Text
Debug.Print strMyCriteria
[/VBA]
There is still a problem in that your query will not parse into a valid SQL, (no time to look into that), but I note you're missing a "]" and possibly a closing ";" (not sure about that)
MD

[mdmackillop]

Another approach to try is to read in all the cell values from your datasource into an array (unique values only), you can then try to create your strQuery using each of the array values in turn. See my file posted here http://www.vbaexpress.com/forum/showthread.php?t=318
MD