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

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

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

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.


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147644

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

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



On Oct 25, 3:30*pm, joel wrote:
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.

--
joel
------------------------------------------------------------------------
joel's Profile:http://www.thecodecage.com/forumz/member.php?userid=229
View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=147644

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

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 ?



On Oct 25, 8:29*pm, "fi.or.jp.de" wrote:
Thanks joel,
I will try as said below, if any problem comes i will get back to you
soon.

On Oct 25, 3:30*pm, joel wrote:

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.

--
joel
------------------------------------------------------------------------
joel's Profile:http://www.thecodecage.com/forumz/member.php?userid=229
View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=147644

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

You need a where clause (A filter) in the SQL. The Query wizard allows
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.`Due
Date`, Submissions.`Actual Date`, Submissions.`Date Difference`
FROM `C:\temp\submission`.Submissions Submissions
WHERE (Submissions.`Client Name`='Test')
ORDER BY Submissions.`Effective Date`


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147644

Microsoft Office Help