Attaching Macro to object when emailing workbooks

[geekgirlau]

I have been struggling with a problem and was unable to find a direct solution in the forum. However some of the posts did point me in the right direction, so I thought I'd post the answer (and hopefully save you the hair-pulling and muttered four-letter words I went through!)

Problem:

I have a workbook in which an individual sheet is to be sent to various users. This sheet has a drawing object with a macro attached. The sheet is copied to a new workbook and saved on a network drive, then sent as attachment in Outlook.

The macro resides on the sheet that is being copied rather than within a module, so the code is copied along with the sheet.

The macro works fine if you open the saved workbook. However when you open the attachment in Outlook, it redirects the attached macro to the temporary location used for all Outlook attachments, even if you have hard-coded the full path for the macro.

Solution:

Make sure that you attach a shortcut to the workbook, rather then embedding the workbook in the email message.

' make sure full path to macro is attached to the button
 ' (macro is in current sheet rather than module)
Worksheets(1).Shapes(1).OnAction = _
 "'" & strAttachPath & "'!shBuyer.SubmitToCapture"
...
With objMail
    .To = strBuyer
    .Subject = "Allocate New Stores/Fixtures Allowance"
    .Attachments.Add strAttachPath, olByReference
    .Send
End With

[Zack Barresse]

Nice one!

Hey, if you don't mind, could you make this as a Knowledge Base submission? I think others would find this very useful.

[Anne Troy]

Hey, girl.
Do you mind adding your code to the knowledgebase? There's a link on every page here at the forum to KBase.

Your login is the same as for the forum and, like the forum, you get a cookie so you don't have to keep logging into it. You don't need to login to see existing kb entries, only to add new ones.