Mail-merge dynamic database source

I have created a mail-merge that gets its input from Excel via OLEDB. Each 
month it needs to get the input from a spreadsheet in a folder specific to 
that month. Moreover, although I create and test the merge on my pc, I 
distribute it to others who have a different naming convention for their 
folders. Once I save my merge document, the source folder is saved with it. 
[Although I have examined the merge document carefully, and can't find any 
clear text reference to the database. Does anyone know how that info is 
stored?]

I know that if the database doesn't exist when the merge doc is opened, 
there are prompts to find the new source. I know that even if the database 
exists (but may be an old source), I can choose "Select Recipients" and 
select a new source. 

I have seen an earlier posting which mentions property 
ActiveDocument.MailMerge.DataSource.Name, and I will experiment writing a 
macro to see if that can be changed dynamically. In the meantime:

- Is there any way to reliably force the merge to prompt for the database 
source each time the merge doc is opened?

- Is there any way to make the stored source path relative, e.g., 
\..\DatabaseInput?
0
Utf
1/8/2010 3:25:01 PM
word.docmanagement 5542 articles. 2 followers. Follow

1 Replies
1529 Views

Similar Articles

[PageSpeed] 32

 > - Is there any way to reliably force the merge to prompt for the database
 > source each time the merge doc is opened?

The simplest method is probably to put an AutoOpen macro in your 
document, or in the attached template, if you always want this action 
for documents attached to this template. It could do:

Sub AutoOpen()
Dim dlg As Word.Dialog
Set dlg = Dialogs(wdDialogMailMergeOpenDataSource)
dlg.Show
End Sub

However, if you already have a data source attached
  a. Word will typically issue its SQL warning dialog box and/or
  b. if it can't find the data source, you get to change it - sort of

There is no (simple) way to prevent Word from looking for that data 
source - it does it even before it executes AutoOpen. So whatever you 
do, it's advisable to try to save the document with no data source 
attached. Typically you do that by changing the document type back to 
"Normal Word Document". Although all the fields are retained, Word will 
lose any information about the data source, including filters and any 
sort sequence you specified, and it will forget what type of merge 
(Letter, Directory etc.). If necessary, in Word 2007 you may be able to 
get around that by closing the data source using the following VBA

Sub CloseDataSource()
On Error Resume Next
ActiveDocument.MailMerge.DataSource.Close
Err.Clear
On Error GoTo 0
End Sub

To answer your questions...

 > [Although I have examined the merge document carefully, and can't 
find any
 > clear text reference to the database. Does anyone know how that info is
 > stored?]

The connection information is stored internally - you can't get at it 
directly via the user interface. In the case of an OLE DB connection, 
you typically provide the pathname of the workbook, and a worksheet or 
range name. Word actually stores 3 pieces of information:
  a. ActiveDocument.MailMerge.DataSource.Name contains the pathname of 
the workbook
  b. ActiveDocument.MailMerge.DataSource.ConnectString contains an OLE 
DB connection string which also contains the pathname of the workbook
  c. ActiveDocument.MailMerge.DataSource.QueryString contains a Jet/ACE 
SQL query along the lines of

SELECT * FROM `mysheetorrange`

If you specified filter conditions and/or a sequence in Edit Recipients 
the SQL will also contain a WHERE clause and/or an ORDER BY clause.

There is a fourth property called .TableName but it always appears to be 
set to the same value as .QueryString. I don't know what it is for.

You cannot change either

ActiveDocument.MailMerge.DataSource.Name
or
ActiveDocument.MailMerge.DataSource.ConnectString

directly because they are read-only properties (as you have probably 
discovered by now!)

You can modify

ActiveDocument.MailMerge.DataSource.QueryString

directly (e.g. to point to a diferent sheet in the same workbook, but 
since in the case of Excel the query string does not specify the work 
book pathname, AFAIK you cannot use that to change the workbook. The 
only way to do that is either to get the user to select a new data 
source, or to use

ActiveDocument.MailMerge.OpenDataSource

to do it.

Peter Jamieson

http://tips.pjmsn.me.uk

On 08/01/2010 15:25, RizzKid wrote:
> I have created a mail-merge that gets its input from Excel via OLEDB. Each
> month it needs to get the input from a spreadsheet in a folder specific to
> that month. Moreover, although I create and test the merge on my pc, I
> distribute it to others who have a different naming convention for their
> folders. Once I save my merge document, the source folder is saved with it.
> [Although I have examined the merge document carefully, and can't find any
> clear text reference to the database. Does anyone know how that info is
> stored?]
>
> I know that if the database doesn't exist when the merge doc is opened,
> there are prompts to find the new source. I know that even if the database
> exists (but may be an old source), I can choose "Select Recipients" and
> select a new source.
>
> I have seen an earlier posting which mentions property
> ActiveDocument.MailMerge.DataSource.Name, and I will experiment writing a
> macro to see if that can be changed dynamically. In the meantime:
>
> - Is there any way to reliably force the merge to prompt for the database
> source each time the merge doc is opened?
>
> - Is there any way to make the stored source path relative, e.g.,
> \..\DatabaseInput?
0
Peter
1/9/2010 11:28:48 AM
Reply:

Similar Artilces:

Dynamic Xml editor
Hi, I am writing a user control that will help a user that know (almost) nothing about Xml to build a Xml file based on a Xsd schema that is given to the user control. The idea is to have a treeview on the left and a property grid on the right. The treeview is loaded with the root elements from the schema. When the user right click on a node, a context menu allow him to add subnodes if schema allow it. This way, the user can create items, attributes, ... The property grid is filled by the user with data. At the end, the Xml file is generated and validated. My problem is about the design....

Merging Duplicate Contacts
Is there an option in V3.0 to merge selected accounts/contacts/leads that have been selected as duplicates? yes, from the Contact List or from an Advanced Find for contacts, you can select multiple contacts and click the Merge icon at the top of the list (the merge icon looks like two small pieces of paper becoming one larger piece of paper). When you click it, a merge dialog will appear and help you with the process. Sorry for the weak description of the icon by the way :) Dave "Mandy" <Mandy@discussions.microsoft.com> wrote in message news:32468F52-66E7-4738-9148-5...

customizing quote merge template
I have 2 major problems that I just cannot seem to be able to resolve. When using the mail merge template to do a quote it seems that there are only a very limited range of "fields" that are made available to the user. These DO NOT include the actual accounts "Address" only shipping and billing address - 99% of our clients don't use these address's so all our data is in "Address_line1" etc Neither do the fields for "product description" appear in the list only the products name. All our quotes have always included a short four or five line desc...

Can E-mail recipient policy be linked to the OU membership?
Running Exchange 2003 (mixed mode) and Windows 2003 server (Windows 2000 mixed mode): I'd like to create a e-mail recipient policy that is linked to the different OU's that are created on the system. Is it possible to link the policy to a specific OU or do I need to create groups on the AD to control which recipients a policy applies to? Martin Moustgaard Yes, you can create a recipient policy that looks to just a specific OU. It is done through LDAP queries. Here are some references to articles and whitepapers that discuss how to work with recipient policies. 249299.KB.EN-US HO...

Outlook Send/Receive mail SLOW
When I receive/send email, it loads sends/receives extremely slow. The percentage indicator when receiving/sending starts at 3-5% and stays that way for awhile. After several minutes, the mail eventually comes through. Any reason for this? ...

Setting up a new e-mail account
I'm trying to set up my yahoo account so that I receive my e-mail messages in my Outlook Inbox. I went to Accounts, and servers, and put in mail.yahoo.com for incoming mail (POP3) and smtp.yahoo.com for outgoing mail (smtp). When I click on the send/receive button I get an error message. Dave <anonymous@discussions.microsoft.com> wrote: > I'm trying to set up my yahoo account so that I receive > my e-mail messages in my Outlook Inbox. I went to > Accounts, and servers, and put in mail.yahoo.com for > incoming mail (POP3) and smtp.yahoo.com for outgoing mail &...

Receiving POP3 mail into Outlook without Send/Receive
I want the POP3 mail to be received into Outlook (If open) when the mail arrives in POP3 account. Is that possible? Set an Automatic Polling Interval. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, delanda asked: | I want the POP3 mail to be received into Outlook (If open) when the | mail arrives in POP3 account. Is that possible? ...

Replying to an Outlook e-mail
When replying to e-mail in Outlook, everything works fine. But when I try to reply to an e-mail that has an embedded picture in it, Outlook won't send it. I get the message that the e-mail was sent, but it stays in the outbox and never sends. Any suggestions? -- Jack "Jack Johnson" <hotshotz@comcast.net> wrote in message news:D6C50AC7-EB37-4B54-9611-F9F42F1F1BA0@microsoft.com... > When replying to e-mail in Outlook, everything works fine. But when I try to > reply to an e-mail that has an embedded picture in it, Outlook won't send > it. &...

Error in database....
A user posted a batch in payables management. After posting, there was an error encountered. It displays that the table updating was interrupted, use batch recovery to continue the posting. But when I used the batch recovery, it was not successful to continue the update process. When I click the "More Details" button it displays, A save operation on table 'PM_Transaction_WORK' caused a sharing error. How can I resolve this issue? Thanks, John John, it is a db sharing violation. Have all users logout DELETE tempdb..DEX_LOCK DELETE tempd..DEX_SESSION DELETE dynami...

compact database in VBA access 2007
Hello, I have recently upgrade to office 2007 from office 2003. To compact a database from within the database itself, I used the follwing code. Unfortunately it no longer works in access 2007. Is there some similar code that will work? Public Function FncCompactTheCurrentDB() CommandBars("Menu Bar"). _ Controls("Tools"). _ Controls("Database utilities"). _ Controls("Compact and repair database..."). _ accDoDefaultAction End Function Thank You, SL On Thu, 28 Jan 2010 17:34:01 -0800, SL <SL@discussions.microsoft....

Problem with Database Wizard
I'm trying to generate a diagram based on the contents of an Access database, using the database to provide x,y coords for instances of a Master shape. It seems I need to run the wizard twice, first to link a master, then to generate the drawing. The first bit appears to work OK, but when I do the second bit, Visio says that there is no master in the stencil that it can use. But I know the master is connected, because if I modify the database, then refresh the shapes, they change accordingly. Does anyone have any idea why this isn't working for me ? I'm using Visio 20...

I want to add a mail account without setting a SMTP server. Is it possible?
I want to add a second mail account that is outside my company. Since the company firewall does not allow us to connect to SMTP-servers I want to add this account without setting the SMTP propertiy. I cannot specify the company server as it is an Exchange server and the outside one an IMAP one. Currently I have specified the outside SMTP server since outook does not allow me to add an account wihtout setting this. I keep getting annoying error messages as my computer can't connect to the outside SMTP. Any one know a way around this? cheers, mortb The company server has SMTP enabled, u...

Merge code issues with Publisher 2003
It seems whenever the data source is altered, the merge code fields have to be reinserted in the Publisher document. Is this normal? The data source starts in Excel and is then saved as a .txt file to retain number formatting on final merge. ...

Saving e-mail & attachments when upgrading
I'm upgrading from NT4 to XP Home and the only data I need to keep are the emails & attachments. I have to format the hard drive to eliminate the partitions. This machine is on a network and I can save to the other machune. Look here for all the information you need on backup and restoration of Outlook data: http://www.slipstick.com/config/backup.htm -- Jocelyn Fiorello MVP - Outlook *** Replies sent to my e-mail address will probably not be answered -- please reply only to the newsgroup to preserve the message thread. *** "Mike" <mike@hosemart.com.au> wrote ...

how to install Microsoft Mail to Outlook 2003
pls help I found 'this' (http://www.outlook-tips.net/howto/msmail.htm) on www.outlook-tips.net I don't know if it works... ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ It does. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Search for answers: http://groups.google.com Most recent posts to the Outlook newsgroups: ...

Duplicate E-Mail #4
Every time I get e-mail from certain users I get their e-mail twice. I have not been able to figure out why this is happening. Is it their fault or my fault and if it is my fault, how do I fix it? Thanks, Al Alfred Kaufmann <al_kaufmann@hotmail.com> wrote: > Every time I get e-mail from certain users I get their e-mail twice. I > have not been able to figure out why this is happening. Is it their > fault or my fault and if it is my fault, how do I fix it? See if this helps: http://www.howto-outlook.com/faq/duplicates.htm -- Brian Tillman [MVP-Outlook] ...

Exchange 2003, Two Sites, Two Smarthosts, One Domain, No Internal mail!!
Hi, I hope there is someone out there who can point me in the right direction. We have two sites, Brisbane and Sydney, connected by a frame realy connection. Sydney is currently running exchange 2003 (upgraded by migration from exchange 5.5 about a month ago). Brisbane is currently running their mail through sendmail and sendmail only. We have just installed exchange 2003 on server 2003 box in Brisbane and are trying to get the two sites working in two routing groups. The topology will be the same in both Brisbane and Sydney, in that each city will have an external mail server running sendma...

Odd e-mail duplication problem
All e-mails promoted or Tracked in CRM Outlook client are being copied to one particular Account that is unrelated. The e-mails are copied correctly to the history of the desired accounts and Contacts but also copy to one particular Accountl as well. As anyone seen anything like this and have any ideas what could be wrong? -- Darryl - dh@mtccrm.com -www.mtccrm.com - Only Microsoft CRM ...

Windows mail asking to verify username and password
I was in Windows Mail when suddenly a box popped up asking for my username and password from Windows Mail.. Although I attempted correctly several times it still will not work. I contacted my kerrlake server and we tried several things including deleting the account and resetting it back up but it still will not open. I can receive my emails on my blackberry and can get on the internet. Also, a protocal error message pops up. Please help! -- Diamond & Associates When you get that repeated prompt for username and password, click = Cancel, which should then produce an erro...

Exchange database forensic
I would like to know if I can with vbscript, and WMI access an exchange database and search for a specific string in either the subject field or the body? I would also like to know if I use exmerge to extract to a PST then use a script or command line tool to find a string in the message of subject or in the body I would also be interested in third party software vendors that can offer this functionality Please include a link if one is available for reference Thank you, John You might want to see if exmerge alone will do what you want: http://support.microsoft.com/kb/328202/en-u...

Start macro creating a mail with contact data and autotext
Hallo, I am working with an user form. The developing of that form started with Outlook XP with a lot of code inside for different buttons. I changed to Outlook 2007 and unfortunately the code of the form was not longer displayed. What I learned about this is that MS does not support to much code in the form (or maybe a bug). They also do not support any longer. I was sending this form to MS support but they told it is do much code inside and they do not know, why the code is not displayed. In Outlook 2003 the code is displayed as in Outlook XP. Because I do not know real...

Dynamic Context Menu
Happy New Year Everyone! I wrote a small program to process some data files. I would like to integrate its functionality to the Windows shell, so I can right-click on these files and select options from the menu (just like WinZip does). I have been playing with the registry, and successfully added right-click functionality, but I can only do static menus. I would like the menus to be dynamic, since the options will vary depending on the content of the data file. Is there any sample code that illustrates this? I have been searching a lot, and all I can find are examples in ...

List the UNIQUE certain fields from the database
Dear Experts, Please help related to the above subject. From this discussion, got the following formula which is work well and faster for small of database. =OFFSET($G$1,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE(DATA)=I$1:I1),ROW(INDIRECT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1) where $G$1, the first row of DATA. how to convert it to VBA as this time i have a huge database. for Tab Data, Advanced, Unique Record only (works well also, but i need the VBA). basically we would like to do similar like this: Data Sample Vendor Name A B...

Loading Adventure Networks Database fails
I am following the instructions page 331 to load the Adventure Networks database. I am running on an absolutely fresh Win2003 SBS and have followed all the instructions. A couple of problems (referring to the bullets in the manual): 2. My Administrator account has domain and local admininstrator privileges and was used to install MS CRM. MS CRM works perfectly. So far so good. 7. I cannot confirm that the five users were created? Why is that? What should I do about it? 10. After entering URL I get an error: That I do not have a MS CRM license??? Help, - Jakob Jakob, ...

Anyone use Promys CRM software integrated with GP Dynamics?
Our company is considering using Promys as a CRM to create quotes and sales orders for the sales team. I am concerned about the integration with GP and what the pitfalls may be. Is anyone here currently using Promys with GP Dynamics? ...