I can conWord Mailmerge wants to make its own connection to the data.
If you can extract the relevant info. that you used to connect via ADO
then I would guess a connection is feasible. Unfortunately I do not have
access to iSeries or AS/400 (or System 38 :-) ) so can only make some
guesses here. So let's step through some stuff.
1. When you connect via ADO, you "really" connect via OLE DB.
2. IMO the best place to start is to see if you can make a connection to
your data source using the OLE DB provider (which is what ADO uses).
3. there are several possible ways to do that (use a .udl file with
connection info., use a .odc file with connection info., and use an
empty .odc file with some VBA). I suggest that in the first instance you
try using an empty .odc file, as follows:
a. create a complete empty file - e.g. using Notepad, then rename it
to "empty.odc". Let's say it's in c:\a\empty.odc
b. create a new Word VBA subroutine as follows:
Sub ConnectToiSeries()
Dim strFolder As String
Dim strODCFile As String
Dim strConnection As String
Dim strQuery As String
' Use your folder name...
strFolder = "c:\a\"
' Use your .odc name...
strODCFile = strFolder & "empty.odc"
' Build the connection string
' You may well need moe here, but I am following
' your ADO connection string
strConnection = "Provider=IBMDA400;Data Source=iSeries;"
' Build the Query string
' I do not know what your query string actually says
' but you /may/ need [mylib].[myfile]
strQuery = "SELECT * FROM mylib.myfile"
' Open the data source
With ActiveDocument.MailMerge
.MainDocumentType = wdNotAMergeDocument
.MainDocumentType = wdFormLetters
.OpenDataSource _
Name:=strODCFile, _
Connection:=strConnection, _
SQLStatement:=strQuery
End With
End Sub
c. Play around with that - if you do manage to connect, there are other
possibilities. If not, there are also other possibilities...
Peter Jamieson
http://tips.pjmsn.me.uk
Diane wrote:
Group,
I am wanting to create a macro that will connect to a file in code and
perform a mail merge to outlook, the file I connect to will always be the
same file.
I am currently connecting with an ODBC connection, I want to change
this....I don't want to be running around installing this on many pc's. If I
can create an ado connection in code, I can eliminate this problem. Also,
my current macro displays the mail merge dialogue box, I don't want that
either, my goal is to connect to my file, complete a mail merge, all within
code, without user intervention.
I can connect to my database, but I can't seem to get the document to merge
with it??
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strsql As String
Set rst = New ADODB.Recordset
Const cnnstring = "Provider=IBMDA400; Data Source=iSeries"
strsql = "select * from mylib.myfile"
---(this doesn't work for me)---ActiveDocument.mailmerge.OpenDataSource
name:=cnnstring
---this is step #2 that I want to work-----
With ActiveDocument.mailmerge
.Destination = wdSendToEmail
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute pause:=False
End With
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Any examples of creating this in code would be appreciated!!