Macro to save mail merged word docs to users desktop and with a desired file name

[bonny24tycoon]

Hi Team,

As we are aware, when we perform a mail merge and preview the letters, the multiple records get printed into a single document.

I have the below macro that saves each individual record separately on my desktop in a Mail Merged Folder.

Sub BoniekMagicMerge()

Dim Title As String
Dim Default As String
Dim MyText As String
Dim MyName As Variant
Dim MyPath As String
Dim Letters As String
Dim Counter As Long
Dim docName As String
Dim oDoc As Document
Dim oNewDoc As Document
Dim DTAddress As String

Set oDoc = ActiveDocument
oDoc.Save
Selection.EndKey Unit:=wdStory
Letters = Selection.Information(wdActiveEndSectionNumber)
Selection.HomeKey Unit:=wdStory
Counter = 1
DTAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
Default = "Merged"
MyText = "Employee1"
Title = "File Name"
MyName = InputBox(MyText, Title, Default)
If MyName = "" Then End
Default = "C:\Users\Boniek.Carvalho\Desktop\Mail Merged folder"  'Replace Boniek ID with code tht would automatically save on users desktop
Title = "Path"
MyText = "C:\Users\Boniek.Carvalho\Desktop\Mail Merged folder" 'Replace Boniek ID with code tht would automatically save on users desktop
MyPath = InputBox(MyText, Title, Default)
If MyPath = "" Then End
While Counter < Letters
Application.ScreenUpdating = False
docName = MyPath & MyName & " " & _
LTrim$(Str$(Counter)) & ".doc"
oDoc.Sections.First.Range.Cut
Set oNewDoc = Documents.Add 
'Documents are based on the Normal template
'To use an alternative template follow the link.
With Selection
.Paste
.EndKey Unit:=wdStory
.MoveLeft Unit:=wdCharacter, Count:=1
.Delete Unit:=wdCharacter, Count:=1
End With
oNewDoc.SaveAs FileName:=docName, _
FileFormat:=wdFormatDocument, _
AddToRecentFiles:=False
ActiveWindow.Close
Counter = Counter + 1
Application.ScreenUpdating = True
Wend
oDoc.Close wdDoNotSaveChanges
End Sub

My Concern - The macro would now be used on many users desktop and as you can see I have my desktop path in the macro that would affect the macro if used on other users desktop.

What I need -

1. I want the macro to automatically create a folder on the users desktop as "Merged Docs" and direct the macro to save all the docs in that path.
2. The macro currently saves each document as "Merged1", "Merged2", "Merged3"........... I would like the file name to be saved as per column A (Employee Number) on my excel list used for mail merge.. So that the word docs are saved as "1000000", "10000002", "10000003"......

Thanks in advance.

[bonny24tycoon]

Hi Every1,

I was able to solve my 2nd requirement. Can any1 help me with a macro code for word that would create a folder on any user desktop (so that the code would not have any reference to my desktop and instead be a universal code that can be used on any users desktop)

[bonny24tycoon]

I dont think I wud get an answer. Would close the thread in sumtime..

[bonny24tycoon]

Closingggg