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$`"
This results in error 5941: 'The requested member of the collection does not exist.'
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