#1
|
|||
|
|||
Excel mail merge code has stopped working
I have been using a version of Macropod's Mail Merge Tips and Tricks code (https://www.msofficeforums.com/mail-...ps-tricks.html) thread for years to run bulk mail mergers in my work.
I have produced individual pdf letters for everyone in entire organisations with this macro; it's absolutely brilliant. I have even worked out how to use multiple Word merge templates in the same mail merge run. Recently, my favourite macro has stopped working. I have downloaded the original macro from the post and it won't work either. It fails at this point in the code - when the SQL is run at the OpenDataSource command: Code:
StrMMSrc = ThisWorkbook.FullName StrMMPath = ThisWorkbook.Path & "" StrMMDoc = StrMMPath & "MailMergeMainDocument.doc" Set wdDoc = wdApp.Documents.Open(Filename:=StrMMDoc, AddToRecentFiles:=False, ReadOnly:=True, Visible:=False) With wdDoc With .MailMerge .MainDocumentType = wdFormLetters .OpenDataSource Name:=StrMMSrc, ReadOnly:=True, AddToRecentFiles:=False, LinkToSource:=False, Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;" & "Data Source=StrMMSrc;Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _ SQLStatement:="SELECT * FROM `Sheet1$`" I notice when I remove the capitals from the .OpenDataSource command, VB re-capitalises it, so it appears to be recognising the command. Same for the wdDoc object. The Word mail merge template even opens, so I'm guessing it's an issue running the SQL. As best I can tell, it seems to be related to the MS DAO 3.6 Object Library - which is now apparently obsolete. (Redirecting). The file is no longer available on my PC and I don't think reinstalling it will do the trick. I have installed the MS Office 16.0 Access database engine Object Library as advised in the above post, but this does not fix the issue. I am currently working on O365 with Windows 10 but I have also tried running my original macro on a Windows 11 PC with Office 2018, no luck, same error. I'm just an amateur macro user, solving this problem is well beyond me. @Macropod, if you are out there, please can you help? I'm desperately missing my macro. many thanks, Titus, Melbourne, Australia Last edited by Titus; 10-23-2023 at 08:37 PM. |
#2
|
|||
|
|||
|
#3
|
|||
|
|||
Extended Properties=""HDR=NO;
How about this? |
#4
|
|||
|
|||
Hi zpy2, yes the macro produces a separate file for each letter; Word or Word and Acrobat or sends to printer, but not much call for that anymore.
|
#5
|
|||
|
|||
No, sadly that did not work either, but thanks for trying.
|
Tags |
excel macro broken, macropod, mailmerge tips & tricks |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Excel stopped working. | nodsirrah | Excel | 9 | 07-01-2017 04:58 AM |
microsoft excel 2016 has stopped working | ropipo | Office | 2 | 10-27-2016 10:38 PM |
Excel error window: Excel has stopped working | MunganBrus | Excel | 1 | 01-13-2015 11:19 AM |
Code for mail merge to reference saved excel file | jtemp57 | Word VBA | 10 | 12-16-2013 11:02 PM |
Word and Excel stopped working | judyn | Office | 9 | 04-23-2013 05:10 AM |