Seeking VBA code to open MailMerge .docm from Excel

[melpa]

Hi,

My second question for the evening is: how do I open a Word document that is a mail merge template and also has a macro in it?
All attempts so far using code found on the internet have resulted in a range of errors.

On opening, the Word document requires permission for a SQL command to run, and if it's opened for the first time - permission to enable macros.

At this stage, all I want to achieve is to open it.

Any help appreciated...

[6StringJazzer]

Please show the code you are using to open the Word document for a complete answer. I have not tested this but this appears to be the solution:

Please Login or Register  to view this content.

[melpa]

Thanks for the reply, I appreciate it, but I'm sorry - I've got no code to show you, I don't know what early or late bindings are, and I have no clue as to how to go about applying the code snippet you offered. I haven't made any progress.

Using other people's code found on the internet I can open the mail merge document, but the window that prompts the SQL command to run does not show (see below) and the data from the excel database has not been loaded.

"Opening this document will run the following SQL command:
'SELECT * FROM MailMerge_DB$'
Data from your database will be placed in the document. Do you want to continue?" (Yes/No)


I'm in over my head.

[ByteMarks]

You can suppress this message (security alert) with VBA, but it'll have the effect of responding "No", which isn't what you want.

I think the only option is to disable the alert in the registry.

Opening this will run the following SQL command

[melpa]

Thank you, again, for coming up with the answer, ByteMarks. I read the linked article and it speaks to the problem exactly.

As my project is something I’m making for a friend this solution is not an option for me, but it is a solution nonetheless.

[ByteMarks]

It might be possible to use vba to manipulate the registry key. So you'd disable it before the mailmerge and re-enable it after.

[melpa]

Unbelievable! It worked! Thank you! The link you provided and the extra hints held the key. And in the end the code is not complex.

This works to open the MailMerge document and run the macro inside it:

Please Login or Register  to view this content.

However, I find the idea of giving a friend an excel workbook that has the power to manipulate the registry of their computer a little terrifying: Have you got any pointers as how to safeguard the code so it doesn't do any damage?

[ByteMarks]

What are the routines CreateRegistryKey DeleteRegistryKey ?
I was thinking more of changing the value rather than deleting the key itself.

[melpa]

I may be using the wrong terminology...

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[ByteMarks]

The presence of the key disables the check so that's ok. For a moment I thought it was the other way round.

I don't think there's too much to safeguard against. You can let your friend know, or build in some msgbox and make sure any error handling deletes the key to reinstate the check.
E.g.

Please Login or Register  to view this content.