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
– rakesh seebaruth, Commented Mar 18, 2018 at 11:17
dropbox.com/s/my62tw9jsfktwo9/fixedcharge16032018.xls?dl=0 dropbox.com/s/h5wb91ymd39h3tr/mailmargeflo1.docx?dl=0 — rakesh seebaruth
– rakesh seebaruth, Commented Mar 18, 2018 at 11:29
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
– Cindy Meister, Commented Mar 18, 2018 at 12:30
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
– Cindy Meister, Commented Mar 18, 2018 at 12:32

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

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

Thanks Cindy it works .When i click on preview results ,letters are displayed by clicking next and next. When i click finish and merge , word does not respond. can you help.thanks rakesh

Collectives™ on Stack Overflow

mail merge excel vba

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related