#1
|
|||
|
|||
Mail Merge from Excel to Separate Documents and Different Folders
Hello,
May I please get some help in forming a VBA script meeting the following Process Flow. I've tried searching online for something to work out of since I have VBA knowledge basics. However, I think I'm unable to find something similar I can use to work around with because I'm lacking in being able to put into words through a google search what could relate to this request. Process Flow Excel file with data is sent to or downloaded by User responsible for running script. Excel file is placed unto User's work station where script can call it from (locate it). User opens word file template existing within work station. User locates button within template. <Hypothetically> Button triggers script. Merging and exporting takes place. User goes to designated location to retrieve files. Refer to Powerpoint for depiction of process flow. Attached Files - Just Form - Just Data - Original (All-in-One) - PAF_ppt Note: In the past, I was able to accomplish this entirely through a script I built when the form (template) existed within Excel and not through a word file externally. One can see this from the Original file I've attached. The script is within the file. Please check it out. This I'm sure will help a lot in understanding what I'm trying to output. Make sure to edit path strings as needed. I don't think this is something that someone hasn't thought of doing in the past. Thus, if anyone knows a of an article, forum post, etc. I could follow to accomplish this and can point me to it, I'd very much appreciate it. Making this happen will definitely help out a whole lot. Thanks so much in advance! |
#2
|
||||
|
||||
This is addressed in detail in the Mailmerge Tips and Tricks 'Sticky' thread at the top of this forum: Mailmerge Tips & Tricks
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Hello,
Thanks for the article. I'm having a difficult time understanding a lot of it since my knowledge of VBA is basic. Sorry about that. I have a few questions. Where do I indicate the Data Source in this script? Is there a way I can call it directly by it? For example, I tell the script the data source is file of name "Data Source" and it is located at X path. Code:
Sub Merge_To_Individual_Files() Application.ScreenUpdating = False Dim StrFolder As String Dim StrName As String Dim MainDoc As Document Dim i As Long Dim j As Long Const StrNoChr As String = """*./\:?|" Set MainDoc = ActiveDocument With MainDoc 'StrFolder = .Path & "\" With .MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True On Error Resume Next For i = 1 To .DataSource.RecordCount With .DataSource .FirstRecord = i .LastRecord = i .ActiveRecord = i StrFolder = .DataFields("Supervisor") & "\" StrName = i & "-" & .DataFields("Name") End With .Execute Pause:=False If Err.Number = 5631 Then Err.Clear GoTo NextRecord End If For j = 1 To Len(StrNoChr) StrName = Replace(StrName, Mid(StrNoChr, j, 1), "_") Next StrName = Trim(StrName) With ActiveDocument 'Add the name to the footer '.Sections(1).Footers(wdHeaderFooterPrimary).Range.InsertBefore StrName .SaveAs FileName:=StrFolder & StrName & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False ' and/or: .SaveAs FileName:=StrFolder & StrName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False .Close SaveChanges:=False End With NextRecord: Next i End With End With Application.ScreenUpdating = True End Sub |
#4
|
||||
|
||||
You don't - the code is intended to be run from a mailmerge main document, in which case the document is automatically connected to the data source when you answer 'Yes' to the SQL prompt Word gives when you open it.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Mail merge separate recipients same file | Cafeaulait | Outlook | 6 | 05-10-2018 08:00 PM |
Mail merge into separate list | mds | Mail Merge | 5 | 04-06-2016 02:17 AM |
Merge info into 5 separate documents | kenjmaraty | Word | 3 | 07-06-2015 07:30 AM |
Split each page of mail merge into separate pdf | Declan | Mail Merge | 1 | 05-28-2014 02:02 AM |
Creating separate file for each mail merge | doshshirl | Mail Merge | 3 | 02-15-2014 08:49 PM |