mail merge excel vba

Question

I have my excel data sheet and my word document as per link below

https://www.dropbox.com/s/my62tw9jsf...32018.xls?dl=0

https://www.dropbox.com/s/h5wb91ymd3...flo1.docx?dl=0

My word document is already configured with the merge fields layout. I have tried mail merge to word but unfortunately the Word application becomes "not responding" each time I try mail merge.

Here it is the recorded macro:

Sub Macro1()
'
' Macro1 Macro
'
'
    ActiveDocument.MailMerge.OpenDataSource Name:= _
        "C:\Users\[userName]\Desktop\New folder (18)\fixedcharge16032018.xls", _
        ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
        AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
        WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
        Format:=wdOpenFormatAuto, Connection:= _
        "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\[UserName]\Desktop\New folder (18)\fixedcharge16032018.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;Je" _
        , SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="", SubType:= _
        wdMergeSubTypeAccess
End Sub

actually i have tried the vba codes on stackoverflow.com/questions/1357121/executing-word-mail-merge. it's not working — rakesh seebaruth
Click the edit link below your question to add code or other information to your original question. If the macro does not work explain exactly HOW it's not working. If there are errors, give the exact error message and specify which line is causing the error. In addition: Did the mail merge you ran when recording the macro work? — Cindy Meister
A tip for communicating on StackOverflow: When you answer someone in a discussion, reference their name using @ for example: @rakeshseebaruth. That will put a notification in their inbox - otherwise people won't know you answered them. — Cindy Meister
@rakeshseebaruth Since you're new to StackOverflow: Note that you should up-vote (click the up-arrow) any contribution (whether question or answer) that you find useful. And if any contribution answers a question you ask you should click the checkmark next to it. This "thanks" the person and tells others looking for help with a similar problem that this did answer the question. — Cindy Meister

Cindy Meister Cindy Meister · Accepted Answer · 2018-03-18T12:59:52

The connection string for the data source is longer than the macro recorder is able to handle. Notice how it cuts off very strangely:

"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\[UserName]\Desktop\New folder (18)\fixedcharge16032018.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;Je" _

That it ends with ;Je" is not normal. I see something similar when I try recording in Word 2016. (The difference is due to the length of the file path.)

Luckily you don't need all the information at the end of the standard connection string. It can be edited / pared down to a form that Word's mail merge can work with.

The following worked for me - cutting off the connection string after IMEX=1;". Note that for your security I replaced the part of the folder path that contains your name! You need to modify this back to your path.

Sub MergeMacro
' Macro1 Macro
'
'
    ActiveDocument.MailMerge.OpenDataSource Name:= _
        "C:\Users\[userName]\Desktop\New folder (18)\fixedcharge16032018.xls", _
        ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
        AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
        WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
        Format:=wdOpenFormatAuto, Connection:= _
        "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\[UserName]\Desktop\New folder (18)\fixedcharge16032018.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;", _
        SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="", SubType:= _
        wdMergeSubTypeAccess
End Sub

mail merge excel vba

1 Answers