mail merge code template with connection strings

[Diane]

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!!

 

[Peter Jamieson]

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

 

[Doug Robbins - Word MVP on news.microsoft.com]

If you manually attach the data source, you could then use the following
code to get the details of the connection

'Get the details of the datasource

With ActiveDocument.MailMerge.DataSource

MsgBox .Name

MsgBox .QueryString

End With


There's a few other things that you need to supply to execute the merge to
email - the MailAddressFieldName as a minimum.

With ActiveDocument.MailMerge
.Destination = wdSendToEmail
.MailAddressFieldName = "emailfield"
.MailAsAttachment = True 'or False
.MailFormat = wdMailFormatHTML 'or wdMailFormatPlainText
.MailSubject = "some string"
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute pause:=False
End With

Plus, you may have to deal with Outlook Security issues for which you can
check out ExpressClickYes (via Google)
--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[Diane]

Doug & Peter,
OK - so now I understand why I was not finding any example to follow with
all my searches. Not quite as simple as what I thought it would be, but from
what the both of you have provided, (.udl/odc - ??) just another challenge
for me to try.

Thanks for the info, I'll see how far I can get.