Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
mail merge code template with connection strings
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!! |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
mail merge code template with connection strings
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!! |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
mail merge code template with connection strings
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" wrote in message ... 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!! |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
mail merge code template with connection strings
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. "Doug Robbins - Word MVP on news.microsof" wrote: 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" wrote in message ... 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!! |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mail Merge connection is lost | Mailmerge | |||
when using mail merge why can't I re-establish a DDE connection | Mailmerge | |||
Mail merge via dde connection | Mailmerge | |||
Mail merge : DDE connection | Microsoft Word Help | |||
Make Word displays strings of text, not strings of code | Microsoft Word Help |