Mailmerge Word used by different users

Question

At the office I created a word-document linked to an excelfile to mailmerge data into the word document. Pretty easy. However, 3 other users need to use the same mailmerge documents. I make a copy of the worddocument and rootfile into a seperate folder for every user. So every time I make a change in the word document, I run a macro to save a copy to the folder of each user (we have access to each other folders) and I link the correct excelfile to Mailmerge correctly for each user. However if I select the excelfile as rootfile, for the other users it will not work since the code says:

ActiveDocument.MailMerge.OpenDataSource Name:= "C:\Users\Louisa\..."

I need something like:

ActiveDocument.MailMerge.OpenDataSource Name:= "C:\Users\CURRENT_USERNAME\..."

Is there a code in Word VBA to insert a current user?

My colleagues are really bad in everything IT, so for them it's confusing selecting the rootfile.

I've tried to insert the username of my colleagues in the macro: f.e.:

ActiveDocument.MailMerge.OpenDataSource Name:= "C:\Users\Sofie\..."

However I get an error, since I can't access "C:\Users\Sofie...

I'm new to VBA so all advice is really welcome!

Thank you!

Answer 1

The way I have done this is to keep both the merge documents and the data source in the Workgroup Templates folder which is updated to each user as part of a network login.

You can get the local location on each system for that folder through vba and use that as part of the path in your macro.

WorkGroupPath = Application.Options.DefaultFilePath(wdWorkgroupTemplatesPath)

Answer 2

I do it this way:

function ex1 ()

dim usr as string
dim usrLen as integer

usr = application.username
usrlen = len(usr)
usr = left(usr, usrLen - 6)

ex1 = usr

end function

sub t ()

dim usrVar as string

usrVar = ex1()

ActiveDocument.MailMerge.OpenDataSource Name:= "C:\Users\" & usrVar & "\..."

End Sub

Yet it works only if you are sure, that the username is standarised in your organisarion and any change in it will cause a crash, so you have to add an error handling functionalities in every sub.

If the file is saved in the location, you can also find it from the file path with function:

function ex2 ()

dim wbkPth as String
dim usrName as string
dim slash as Integer
dim wbkPthLen as Long

wbkPth = ActiveWorkbook.FullPath
wbkPthLen = len(wbkPth)
wbkPth = Right(wbkPth, wbkPthLen - 9)
slash = InStr(1, wbkPth, "\")

ex2 = left(wbkPth, slash - 1)

end function

Nevertheless, if this has to be used only by 3 users, maybe the simplest solution would be to make 3 different files with different paths. You can put path in variable, so you have to change only one line in each file.