VBA Word: create hyperlink with data from dropdown fields?

Question

We are currently using a Word template for online meeting agendas. Every participent must confirm their attendance by email after the meeting. To make things easier, the agenda contains some dropdown lists for "type of meeting", "sequential number of meeting", "meeting date", "meeting time" etc. Also there is a button linked to a VBA macro that collects the values from these dropdown fields and puts them into an empty mail, so the result was:

From: [email protected]
Subject: Attendence of <XYZ> meeting number <x>/2023 on <DD/MM/YYYY>
Body: Some legalese blah

The VBA code looks like:

Private Sub CommandButton1_Click()
strCount = ActiveDocument.Range.ContentControls(1).Range
strTeam = ActiveDocument.Range.ContentControls(2).Range
strDate = ActiveDocument.Range.ContentControls(3).Range
strSubject = "Attendance of "&strTeam&" meeting number "&strCount&"/2023 on "&strDate
strBody = "Some legalese blah"

Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(0)

With objMail
   .To = "[email protected]"
   .Subject = strSubject
   .Body = strBody
   .Display
End With

Used to work fine until some time ago when Microsoft changed security restrictions. Now when attendees open the agenda they get the error message:

SECURITY RISK: Microsoft has blocked macros from running because the source of the file is untrusted

As i have not found a way to run this macro reliably the recipient side, i am thinking about replacing the CommandButton with a hyperlink like:

a href="mailto:[email protected]?subject=Attendence of <XYZ> meeting number <x>/2023 on <DD/MM/YYYY>&body=Some legalese blah"

(Example contains spaces for the sake of readability, I know i have to replace " " with %20)

For this to work, I have to put the values of the drop down fields into the hyperlink. What is the best way to achieve this? Is there a way to reference the value (like e.g. a cell value in Excel)? Or do i have to write another macro? Can macros in word be triggered by on "on change" event?

Any help would be greatly appreciated!

Answer 1

The 'Microsoft has blocked macros from running because the source of the file is untrusted' warning (referred to as the 'Mark of the Web' or MOTW) relates to this announcement from Microsoft. This was rolled-out over the last 18 months or-so.

You can still 'reliably run the macro on the recipient side' but (assuming you do not have a code signing certificate, which comes with a cost) your recipients need to be educated on how to do that, in summary the options are:

• To first 'Unblock' the Word document/template file, or
• To save it in a 'Trusted Location' (having created that 'Trusted Location'), or
• You can Zip (and they Unzip) the file

Other than for the last of these options, see the 'Steps to take to allow VBA macros to run in files that you trust' section of the above linked web page.

For the Zip/Unzip option, it is as simple as the sender Zipping the file before distributing it and the recipients Unzipping the file when they have received it. However, it is important to use a Zip tool that does not support MOTW propagation - you can see a list of Zip tools and whether or not they support MOTW propagation here https://github.com/nmantani/archiver-MOTW-support-comparison (note that this Zip/Unzip option is not an 'official' solution, hence it not being on the web page, and the viability of this solution may change if Microsoft changes its approach or if the providers of Zip software change how their software works).

You ask two other questions which I'll cover briefly:

1. Your potential 'hyperlink solution' is likely to suffer from the same problem as above (ie you will need a VBA macro in order to create the hyperlink meaning you have the same MOTW problem)
2. Word documents / templates do have 'events' (see Using Events with the Document Object) but none of these is an "on change" event for which you will need to start creating and distributing an AddIn which is a more complex solution ... there are many sites discussing creation of Word AddIns (aka 'global templates') on the web.