Mail Merge source Access Database using Excel VBA... Pls Anyone

[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

 

[joel]

Are you asking a question? I saw this posting yesterday and thought you
were just showing results of your macro!

The title of you request said you wanted to order by date so here is an
example of a SQL to retrieve a record set in order

SELECT Submissions.Task_ID, Submissions.`Client Name`,
Submissions.`Effective Date`, Submissions.`Imp Mgr`, Submissions.`Due
Date`, Submissions.`Actual Date`, Submissions.`Date Difference`
FROM `C:\temp\submission`.Submissions Submissions
ORDER BY Submissions.`Due Date`


What I recommend to help you get the syntax correct is to perfrom a
database query manually. The Query wizard allows you to manually set up
a SQL.

Go to worksheet menu

Data - Import External Data - Import Data - New Database query.

Selct the database. Then in first menu select the column(s) you need.
The
2nd menu allows you to add any filtering. The 3rd menu allows you to
select the Sort field(s). Then in the 4th menu select Edit Query radio
button and press Finish. The query editor will appear. Press the SQL
button and copy the SQL. You can edit the SQL as required but it will
give you the syntax for the SQL.

 

[fi.or.jp.de]

Thanks joel,
I will try as said below, if any problem comes i will get back to you
soon.

 

[fi.or.jp.de]

Hi Joel,

i am not getting the result.

Assume I have 10 results in my listbox ( excel userform ).

I have selected only one item from the listbox.

When click merge, it merge for the results ( 100 records - which is
stored in Access Database)

How do i limit to selected records ?

Thanks joel,
I will try as said below, if any problem comes i will get back to you
soon.

Are you asking a question?  I saw this posting yesterday and thought you
were just showing results of your macro!

The title of you request said you wanted to order by date so here is an
example of a SQL to retrieve a record set in order

SELECT Submissions.Task_ID, Submissions.`Client Name`,
Submissions.`Effective Date`, Submissions.`Imp Mgr`, Submissions.`Due
Date`, Submissions.`Actual Date`, Submissions.`Date Difference`
FROM `C:\temp\submission`.Submissions Submissions
ORDER BY Submissions.`Due Date`

What I recommend to help you get the syntax correct is to perfrom a
database query manually.  The Query wizard allows you to manually setup
a SQL.

Go to worksheet menu

Data - Import External Data - Import Data - New Database query.  

Selct the database.  Then in first menu select the column(s) you need..
The
2nd menu allows you to add any filtering.  The 3rd menu allows you to
select the Sort field(s).  Then in the 4th menu select Edit Query radio
button and press Finish.  The query editor will appear.  Press the SQL
button and copy the SQL.  You can  edit the SQL as required but it will
give you the syntax for the SQL.

 

[joel]

You need a where clause (A filter) in the SQL. The Query wizard allow
allows you to add a filter. I beliee its the 3rd window in the wizard.
the Where need to be aon a seperate line like this

SELECT Submissions.Task_ID, Submissions.`Client Name`
Submissions.`Effective Date`, Submissions.`Imp Mgr`, Submissions.`Du
Date`, Submissions.`Actual Date`, Submissions.`Date Difference`
FROM `C:\temp\submission`.Submissions Submissions
WHERE (Submissions.`Client Name`='Test')
ORDER BY Submissions.`Effective Date