Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Automate multiple mail merges
I have 40+ mail merge templates in Word 2003 which all share a single Excel
2003 spreadsheet as a data source. I would like to be able to automate the merge of each of these documents from a single macro. For example, I'd like to be able to open a single Word document, run a macro which opens each of the mail merge templates in turn and completes the merge to email. Is this a reasonable request and can anyone point me in the right direction? Thanks in advance for any advice! |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Automate multiple mail merges
The following code has not been tested, but should do what you want if you
supply the correct name for the field containing the email addresses: Dim MyPath As String Dim MyName As String Dim maindoc As Document 'let user select a path With Dialogs(wdDialogCopyFile) If .Display() -1 Then Exit Sub MyPath = .Directory End With 'strip quotation marks from path If Len(MyPath) = 0 Then Exit Sub If Asc(MyPath) = 34 Then MyPath = Mid$(MyPath, 2, Len(MyPath) - 2) End If 'get files from the selected path 'and insert them into the doc MyName = Dir$(MyPath & "*.*") Do While MyName "" Set maindoc = Documents.Open(MyName) With maindoc.MailMerge .MailAddressFieldName = "nameoffieldcontainingemailaddresses" .MailSubject = "subjectofemailmessage" .Destination = wdSendToEmail .Execute End With maindoc.Close wdDoNotSaveChanges MyName = Dir Loop -- 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 "mjkx" wrote in message ... I have 40+ mail merge templates in Word 2003 which all share a single Excel 2003 spreadsheet as a data source. I would like to be able to automate the merge of each of these documents from a single macro. For example, I'd like to be able to open a single Word document, run a macro which opens each of the mail merge templates in turn and completes the merge to email. Is this a reasonable request and can anyone point me in the right direction? Thanks in advance for any advice! |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Automate multiple mail merges
Fantastic! Thank you very much.
2 ongoing issues: 1) I'd like to be able to set the location of the data source using the macro as well. Currently I get prompted with each template to point to the spreadsheet. 2) Each of the templates filters the data in the data source so I get prompted with each to allow the SQL Query to execute. I'd like to be able to either programmatically set the SQL query for each template, or programmatically allow the existing query to run without being prompted. Any ideas? Thanks again. "Doug Robbins - Word MVP" wrote: The following code has not been tested, but should do what you want if you supply the correct name for the field containing the email addresses: Dim MyPath As String Dim MyName As String Dim maindoc As Document 'let user select a path With Dialogs(wdDialogCopyFile) If .Display() -1 Then Exit Sub MyPath = .Directory End With 'strip quotation marks from path If Len(MyPath) = 0 Then Exit Sub If Asc(MyPath) = 34 Then MyPath = Mid$(MyPath, 2, Len(MyPath) - 2) End If 'get files from the selected path 'and insert them into the doc MyName = Dir$(MyPath & "*.*") Do While MyName "" Set maindoc = Documents.Open(MyName) With maindoc.MailMerge .MailAddressFieldName = "nameoffieldcontainingemailaddresses" .MailSubject = "subjectofemailmessage" .Destination = wdSendToEmail .Execute End With maindoc.Close wdDoNotSaveChanges MyName = Dir Loop -- 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 "mjkx" wrote in message ... I have 40+ mail merge templates in Word 2003 which all share a single Excel 2003 spreadsheet as a data source. I would like to be able to automate the merge of each of these documents from a single macro. For example, I'd like to be able to open a single Word document, run a macro which opens each of the mail merge templates in turn and completes the merge to email. Is this a reasonable request and can anyone point me in the right direction? Thanks in advance for any advice! |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Automate multiple mail merges
If the documents were saved after the data source was attached to them you
should be be being asked for the data source unless its the following message that you are getting when you start talking about SQL: "Opening This Will Run the Following SQL Command" Message When You Open a Word Document " If that is the case, see: http://support.microsoft.com?kbid=825765 -- 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 "Matt" wrote in message ... Fantastic! Thank you very much. 2 ongoing issues: 1) I'd like to be able to set the location of the data source using the macro as well. Currently I get prompted with each template to point to the spreadsheet. 2) Each of the templates filters the data in the data source so I get prompted with each to allow the SQL Query to execute. I'd like to be able to either programmatically set the SQL query for each template, or programmatically allow the existing query to run without being prompted. Any ideas? Thanks again. "Doug Robbins - Word MVP" wrote: The following code has not been tested, but should do what you want if you supply the correct name for the field containing the email addresses: Dim MyPath As String Dim MyName As String Dim maindoc As Document 'let user select a path With Dialogs(wdDialogCopyFile) If .Display() -1 Then Exit Sub MyPath = .Directory End With 'strip quotation marks from path If Len(MyPath) = 0 Then Exit Sub If Asc(MyPath) = 34 Then MyPath = Mid$(MyPath, 2, Len(MyPath) - 2) End If 'get files from the selected path 'and insert them into the doc MyName = Dir$(MyPath & "*.*") Do While MyName "" Set maindoc = Documents.Open(MyName) With maindoc.MailMerge .MailAddressFieldName = "nameoffieldcontainingemailaddresses" .MailSubject = "subjectofemailmessage" .Destination = wdSendToEmail .Execute End With maindoc.Close wdDoNotSaveChanges MyName = Dir Loop -- 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 "mjkx" wrote in message ... I have 40+ mail merge templates in Word 2003 which all share a single Excel 2003 spreadsheet as a data source. I would like to be able to automate the merge of each of these documents from a single macro. For example, I'd like to be able to open a single Word document, run a macro which opens each of the mail merge templates in turn and completes the merge to email. Is this a reasonable request and can anyone point me in the right direction? Thanks in advance for any advice! |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Automate multiple mail merges
OK, so I'll resave the templates to the correct source location and consider
either using the reg hack indicated in the KB. This is great progress. One thing I'm still encountering is an error if there are no records for a particular template. I run these merges once a week and there may be zero to many records for each of the merges. Is there a quick way within the loop to programmatically determine the number of records returned by the filter before executing each merge? Thanks "Doug Robbins - Word MVP" wrote: If the documents were saved after the data source was attached to them you should be be being asked for the data source unless its the following message that you are getting when you start talking about SQL: "Opening This Will Run the Following SQL Command" Message When You Open a Word Document " If that is the case, see: http://support.microsoft.com?kbid=825765 -- 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 "Matt" wrote in message ... Fantastic! Thank you very much. 2 ongoing issues: 1) I'd like to be able to set the location of the data source using the macro as well. Currently I get prompted with each template to point to the spreadsheet. 2) Each of the templates filters the data in the data source so I get prompted with each to allow the SQL Query to execute. I'd like to be able to either programmatically set the SQL query for each template, or programmatically allow the existing query to run without being prompted. Any ideas? Thanks again. "Doug Robbins - Word MVP" wrote: The following code has not been tested, but should do what you want if you supply the correct name for the field containing the email addresses: Dim MyPath As String Dim MyName As String Dim maindoc As Document 'let user select a path With Dialogs(wdDialogCopyFile) If .Display() -1 Then Exit Sub MyPath = .Directory End With 'strip quotation marks from path If Len(MyPath) = 0 Then Exit Sub If Asc(MyPath) = 34 Then MyPath = Mid$(MyPath, 2, Len(MyPath) - 2) End If 'get files from the selected path 'and insert them into the doc MyName = Dir$(MyPath & "*.*") Do While MyName "" Set maindoc = Documents.Open(MyName) With maindoc.MailMerge .MailAddressFieldName = "nameoffieldcontainingemailaddresses" .MailSubject = "subjectofemailmessage" .Destination = wdSendToEmail .Execute End With maindoc.Close wdDoNotSaveChanges MyName = Dir Loop -- 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 "mjkx" wrote in message ... I have 40+ mail merge templates in Word 2003 which all share a single Excel 2003 spreadsheet as a data source. I would like to be able to automate the merge of each of these documents from a single macro. For example, I'd like to be able to open a single Word document, run a macro which opens each of the mail merge templates in turn and completes the merge to email. Is this a reasonable request and can anyone point me in the right direction? Thanks in advance for any advice! |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Automate multiple mail merges
One other thing...Is it possible to prevent the templates from being
displayed during the merge? Can this be done behind the scenes? "Doug Robbins - Word MVP" wrote: If the documents were saved after the data source was attached to them you should be be being asked for the data source unless its the following message that you are getting when you start talking about SQL: "Opening This Will Run the Following SQL Command" Message When You Open a Word Document " If that is the case, see: http://support.microsoft.com?kbid=825765 -- 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 "Matt" wrote in message ... Fantastic! Thank you very much. 2 ongoing issues: 1) I'd like to be able to set the location of the data source using the macro as well. Currently I get prompted with each template to point to the spreadsheet. 2) Each of the templates filters the data in the data source so I get prompted with each to allow the SQL Query to execute. I'd like to be able to either programmatically set the SQL query for each template, or programmatically allow the existing query to run without being prompted. Any ideas? Thanks again. "Doug Robbins - Word MVP" wrote: The following code has not been tested, but should do what you want if you supply the correct name for the field containing the email addresses: Dim MyPath As String Dim MyName As String Dim maindoc As Document 'let user select a path With Dialogs(wdDialogCopyFile) If .Display() -1 Then Exit Sub MyPath = .Directory End With 'strip quotation marks from path If Len(MyPath) = 0 Then Exit Sub If Asc(MyPath) = 34 Then MyPath = Mid$(MyPath, 2, Len(MyPath) - 2) End If 'get files from the selected path 'and insert them into the doc MyName = Dir$(MyPath & "*.*") Do While MyName "" Set maindoc = Documents.Open(MyName) With maindoc.MailMerge .MailAddressFieldName = "nameoffieldcontainingemailaddresses" .MailSubject = "subjectofemailmessage" .Destination = wdSendToEmail .Execute End With maindoc.Close wdDoNotSaveChanges MyName = Dir Loop -- 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 "mjkx" wrote in message ... I have 40+ mail merge templates in Word 2003 which all share a single Excel 2003 spreadsheet as a data source. I would like to be able to automate the merge of each of these documents from a single macro. For example, I'd like to be able to open a single Word document, run a macro which opens each of the mail merge templates in turn and completes the merge to email. Is this a reasonable request and can anyone point me in the right direction? Thanks in advance for any advice! |
#7
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Automate multiple mail merges
I've solved this issue with code based on the following KB article:
http://support.microsoft.com/default...d=kb;ko;829121 Thanks "Matt" wrote: OK, so I'll resave the templates to the correct source location and consider either using the reg hack indicated in the KB. This is great progress. One thing I'm still encountering is an error if there are no records for a particular template. I run these merges once a week and there may be zero to many records for each of the merges. Is there a quick way within the loop to programmatically determine the number of records returned by the filter before executing each merge? Thanks "Doug Robbins - Word MVP" wrote: If the documents were saved after the data source was attached to them you should be be being asked for the data source unless its the following message that you are getting when you start talking about SQL: "Opening This Will Run the Following SQL Command" Message When You Open a Word Document " If that is the case, see: http://support.microsoft.com?kbid=825765 -- 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 "Matt" wrote in message ... Fantastic! Thank you very much. 2 ongoing issues: 1) I'd like to be able to set the location of the data source using the macro as well. Currently I get prompted with each template to point to the spreadsheet. 2) Each of the templates filters the data in the data source so I get prompted with each to allow the SQL Query to execute. I'd like to be able to either programmatically set the SQL query for each template, or programmatically allow the existing query to run without being prompted. Any ideas? Thanks again. "Doug Robbins - Word MVP" wrote: The following code has not been tested, but should do what you want if you supply the correct name for the field containing the email addresses: Dim MyPath As String Dim MyName As String Dim maindoc As Document 'let user select a path With Dialogs(wdDialogCopyFile) If .Display() -1 Then Exit Sub MyPath = .Directory End With 'strip quotation marks from path If Len(MyPath) = 0 Then Exit Sub If Asc(MyPath) = 34 Then MyPath = Mid$(MyPath, 2, Len(MyPath) - 2) End If 'get files from the selected path 'and insert them into the doc MyName = Dir$(MyPath & "*.*") Do While MyName "" Set maindoc = Documents.Open(MyName) With maindoc.MailMerge .MailAddressFieldName = "nameoffieldcontainingemailaddresses" .MailSubject = "subjectofemailmessage" .Destination = wdSendToEmail .Execute End With maindoc.Close wdDoNotSaveChanges MyName = Dir Loop -- 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 "mjkx" wrote in message ... I have 40+ mail merge templates in Word 2003 which all share a single Excel 2003 spreadsheet as a data source. I would like to be able to automate the merge of each of these documents from a single macro. For example, I'd like to be able to open a single Word document, run a macro which opens each of the mail merge templates in turn and completes the merge to email. Is this a reasonable request and can anyone point me in the right direction? Thanks in advance for any advice! |
#8
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Automate multiple mail merges
OK, I'm making pretty good progress, but have another question. The machines
that will be used to run these merges are locked down and users wont be able to make changes to the registry. I have adapted the code to programmatically set the data source as well as the specific query string for each template. This has resolved the problem of the prompt each time a template opens and tries to apply the filter. I have been executing the merge to a new document for testing and everything seems to be working beautifully. The problem I'm encountering now is that when I merge to email rather than to a document, Outlook prompts for permission to access the address book and then asks for permission to send each email. The specific dialog is "A program is trying to automatically send e-mail on your behalf. Do you want to allow this?" These merges generate hundreds of emails. Is there a way to overcome this prompt? I have signed the macro and it should be trusted, but that does not stop the security warning. Thanks in advance "Doug Robbins - Word MVP" wrote: If the documents were saved after the data source was attached to them you should be be being asked for the data source unless its the following message that you are getting when you start talking about SQL: "Opening This Will Run the Following SQL Command" Message When You Open a Word Document " If that is the case, see: http://support.microsoft.com?kbid=825765 -- 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 "Matt" wrote in message ... Fantastic! Thank you very much. 2 ongoing issues: 1) I'd like to be able to set the location of the data source using the macro as well. Currently I get prompted with each template to point to the spreadsheet. 2) Each of the templates filters the data in the data source so I get prompted with each to allow the SQL Query to execute. I'd like to be able to either programmatically set the SQL query for each template, or programmatically allow the existing query to run without being prompted. Any ideas? Thanks again. "Doug Robbins - Word MVP" wrote: The following code has not been tested, but should do what you want if you supply the correct name for the field containing the email addresses: Dim MyPath As String Dim MyName As String Dim maindoc As Document 'let user select a path With Dialogs(wdDialogCopyFile) If .Display() -1 Then Exit Sub MyPath = .Directory End With 'strip quotation marks from path If Len(MyPath) = 0 Then Exit Sub If Asc(MyPath) = 34 Then MyPath = Mid$(MyPath, 2, Len(MyPath) - 2) End If 'get files from the selected path 'and insert them into the doc MyName = Dir$(MyPath & "*.*") Do While MyName "" Set maindoc = Documents.Open(MyName) With maindoc.MailMerge .MailAddressFieldName = "nameoffieldcontainingemailaddresses" .MailSubject = "subjectofemailmessage" .Destination = wdSendToEmail .Execute End With maindoc.Close wdDoNotSaveChanges MyName = Dir Loop -- 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 "mjkx" wrote in message ... I have 40+ mail merge templates in Word 2003 which all share a single Excel 2003 spreadsheet as a data source. I would like to be able to automate the merge of each of these documents from a single macro. For example, I'd like to be able to open a single Word document, run a macro which opens each of the mail merge templates in turn and completes the merge to email. Is this a reasonable request and can anyone point me in the right direction? Thanks in advance for any advice! |
#9
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Automate multiple mail merges
See the bit about the Express Click Yes utility in the Preparations section
of the article "Mail Merge to E-mail with Attachments" at: http://word.mvps.org/FAQs/MailMerge/...ttachments.htm -- 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 "mjkx" wrote in message ... OK, I'm making pretty good progress, but have another question. The machines that will be used to run these merges are locked down and users wont be able to make changes to the registry. I have adapted the code to programmatically set the data source as well as the specific query string for each template. This has resolved the problem of the prompt each time a template opens and tries to apply the filter. I have been executing the merge to a new document for testing and everything seems to be working beautifully. The problem I'm encountering now is that when I merge to email rather than to a document, Outlook prompts for permission to access the address book and then asks for permission to send each email. The specific dialog is "A program is trying to automatically send e-mail on your behalf. Do you want to allow this?" These merges generate hundreds of emails. Is there a way to overcome this prompt? I have signed the macro and it should be trusted, but that does not stop the security warning. Thanks in advance "Doug Robbins - Word MVP" wrote: If the documents were saved after the data source was attached to them you should be be being asked for the data source unless its the following message that you are getting when you start talking about SQL: "Opening This Will Run the Following SQL Command" Message When You Open a Word Document " If that is the case, see: http://support.microsoft.com?kbid=825765 -- 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 "Matt" wrote in message ... Fantastic! Thank you very much. 2 ongoing issues: 1) I'd like to be able to set the location of the data source using the macro as well. Currently I get prompted with each template to point to the spreadsheet. 2) Each of the templates filters the data in the data source so I get prompted with each to allow the SQL Query to execute. I'd like to be able to either programmatically set the SQL query for each template, or programmatically allow the existing query to run without being prompted. Any ideas? Thanks again. "Doug Robbins - Word MVP" wrote: The following code has not been tested, but should do what you want if you supply the correct name for the field containing the email addresses: Dim MyPath As String Dim MyName As String Dim maindoc As Document 'let user select a path With Dialogs(wdDialogCopyFile) If .Display() -1 Then Exit Sub MyPath = .Directory End With 'strip quotation marks from path If Len(MyPath) = 0 Then Exit Sub If Asc(MyPath) = 34 Then MyPath = Mid$(MyPath, 2, Len(MyPath) - 2) End If 'get files from the selected path 'and insert them into the doc MyName = Dir$(MyPath & "*.*") Do While MyName "" Set maindoc = Documents.Open(MyName) With maindoc.MailMerge .MailAddressFieldName = "nameoffieldcontainingemailaddresses" .MailSubject = "subjectofemailmessage" .Destination = wdSendToEmail .Execute End With maindoc.Close wdDoNotSaveChanges MyName = Dir Loop -- 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 "mjkx" wrote in message ... I have 40+ mail merge templates in Word 2003 which all share a single Excel 2003 spreadsheet as a data source. I would like to be able to automate the merge of each of these documents from a single macro. For example, I'd like to be able to open a single Word document, run a macro which opens each of the mail merge templates in turn and completes the merge to email. Is this a reasonable request and can anyone point me in the right direction? Thanks in advance for any advice! |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unable to view Mail merges ... | Mailmerge | |||
Mail Merge Templates from Multiple Templates Programmatically | Mailmerge | |||
can multiple open mail merge files access the same data file | Mailmerge | |||
Advice for newbies VBA required mail merges | Mailmerge | |||
Query a mail merge for multiple letters at once? | Mailmerge |