To access OLE object inserted to an excel sheet

[sugaprasad]

hello,

I am building a macro using excel VBA.

Objective of the macro is to send out mails using a particular standard template. I saved the template to a local folder in my computer and written the code. It works just fine.

But the requirement changed all of a sudden. Now I need to attach that template into the excel sheet (Insert --> Object--> create from file ) and use that as a source (say I am attaching the template as an object in Sheet3)

So now my question is, How to access the object (outlook template) which is attached in the excel sheet as a source template and send out mails

My code for your reference.


<code style="margin: 0px; padding: 0px; font-style: inherit;">

Dim OutApp As Object
    Dim Itm As Object

    Set OutApp = CreateObject("Outlook.Application")
   Set Itm = OutApp.CreateItemFromTemplate("C:\Users\sun\Desktop\format1.oft")  '(this needs to be accessed from the excel itself not from local folder)

          With Itm
                .To = "abc@def.com"
                .Subject = "Subject"
                .Body = "Txt goes here"
                .Send 
            End With

Thank you in advance

Raised the same question in another forum too. Link</code>