Mail Merge source Access Database using Excel VBA

[fi.or.jp.de]

Hi All,

I am working on mail merge.

I have access database where all the customer information are stored.
I have one excel file, I have created userform.
This will shows the customer information based on some criteria in
listbox.

User needs to select the listbox item results, may be 1 result or 10
result
( my database contains more than 500 customer information )

If the user clicks on mail merge ( in excel userform ) this perform
some action.

like opening word doc ( source document ) & statring merging with
selected
listbox item.

Eg.. user selects 2 listbox items. I need mail merged letters for
those clients not for all
the clients ( 500 customer available in access database ).

I am using the below code, which pulls out all the details not the
selected listbox items.


With UserForm1.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
litem = .List(i)
Dbase = "D:\share.accdb"
Ssql = "Select * FROM [" & TableName & "] "
Ssql = Ssql & " WHERE ID = " & litem
objword.Application.Visible = True

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" & Dbase & ";"

Set rs = New ADODB.Recordset
rs.Open Ssql, cn, adOpenStatic,
adLockBatchOptimistic
objword.MailMerge.Execute

End If
Next i
End With

 

[Patrick Molloy]

your code loomks fine ... are you running it from the userform?

The only uissue might be the resulting ssQL string. Usually, in T-SQL text
is enclosed in single quotes eh " WHERE [ID] = 'ABC' "
If this is not the case SQL would raise an error - maybe Access doesn't?
So try addign the single quote mark
Ssql = Ssql & " WHERE ID = '" & litem & "'"

also, did you step through it? Put a break point at the start of the loop -
on the WITH line, then press F8 to see if it correctly goes through th eIF
statements

It would be faster too, if you set the connection before the loop. once the
connection is open, you can llop your reciordsets as you do.