Excel mail merge code has stopped working

Titus · #1 10-23-2023, 03:49 PM

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

zpy2 · #2 10-25-2023, 12:03 AM

https://www.msofficeforums.com/mail-...tml#post177587

all datas split to multiple .docx?

zpy2 · #3 10-25-2023, 12:05 AM

Extended Properties=""HDR=NO;

How about this?

Titus · #4 10-25-2023, 01:58 AM

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.

Titus · #5 10-25-2023, 02:02 AM

Quote:

Originally Posted by zpy2

Extended Properties=""HDR=NO;

How about this?

No, sadly that did not work either, but thanks for trying.

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