VBA: Mailscript with copied sheet with button reffering to new copied macro/workbook

[arnoudholtzer]

Hi ALL,

I couldn't find a workable solution anywhere for my issue.
I have a working VBA script triggered by a button on sheet 1 to generate a mail by the click on a button and attach a copy of sheet 2 as a new workbook that holds a button with another maco.

Problem arrises when i try to make the attached newly generated workbook (copy of sheet 2) a macro enabled workbook with a clickable button in it.
When the script copies sheet 2 along with the button and assigned macro sub in sheet 2 towards a newly generated workbook it will attach that new workbook to the generated mail.
However the button in the newly generated workbook still refers towards the assigned macro of the initial workbook.

See below attachment for a testfile containing the full issue. When you press the button on sheet 1 a 'display' version of a generated mail is created with sheet 2 as attachment but with incorrect macro button link to previous workbook
Link to excel testfile

When i open the attached newly generated excel book from the mail the assigned button refers to the previous workbook like shown in below screenshot

Can anyone help me solve this issue to make the button refer towards it's copied version of the sub macro of sheet 2?

Thanks in advance for thinking along

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA: Mailscript with copied sheet with button reffering to new copied macro/workbook
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[arnoudholtzer]

I crossposted this post to expand the range of audiance in the hope to get a sollution.
Same question is also posted here. (no solution yet) : Link to excel forum post
*** I didn't see an option to edit the original posting above.

 

[arnoudholtzer]

Hi ALL,

I notice no one has tried to solve my excel challenge yet.
I found below links that might hold some part of the solution, but i'm personally not expert in excel enough to turn this into a working script.
Can anyone look into this please?

Below some forums with similar issues and possible a partly described solution...
option 1: Correcting Shape Assigned Macro Links After Copying Worksheet VBA
option 2: https://www.mrexcel.com/board/threads/xl-vba-assigning-macro-to-button-in-programmatically-created-sheet-for-emailing.749258/
option 3: Moving sheets with VBA codes and buttons to a new Workbook
option 4: https://stackoverflow.com/questions/30317817/create-a-new-workbook-import-macro-and-assign-button

Thanks in advance for trying to solve this issue.

Kind regards,

Arnoud