How to use embedded Outlook OFT in Excel to send emails?

[elitef]

Hi All,

I've been looking online for an answer but have not been able to find one.

I have a local Outlook Template OFT file and the script works fine with it, but I want to eliminate the need to share the template file as well, so I wanted to know if there was a way to use the macro on an embedded OFT file instead.
I've attached a sample of the spreadsheet.

Hope someone can shed some light on this.

Sheet1!A1 = Subject of Email
Sheet1!A2 = First Name
Sheet1!A3 = Last Name

The code I have so far is:

Private Sub SendEmail()
  Dim NewEmail As MailItem
  Dim PathFileName As String
   
  PathFileName = "HOW_TO_LINK_TO_EMBEDDED_OBJECT_OFT_THAT_IS_ON_SHEET1???"
  Set NewEmail = CreateItemFromTemplate(PathFileName)
    
    With NewEmail
    .Subject = Worksheets("Sheet1").Range("A1").Value
    .HTMLBody = Replace(.HTMLBody, "#FNAME", Worksheets("Sheet1").Range("A2").Value)
    .HTMLBody = Replace(.HTMLBody, "#LNAME", Worksheets("Sheet1").Range("A3").Value)
    .SentOnBehalfOfName = "test@test.com"
    BodyWithoutSignature = .HTMLBody
    .Display
    .HTMLBody = BodyWithoutSignature
  End With
  Next
End Sub

Embedded on Sheet1 is an Outlook OFT template file, so thats the file that I want to use for the above code instead of having a local path to the OFT file.

Hope someone can help

 

[Grasor]

elitef,

As I understand your problem you have an .OFT file embedded as an object on a worksheet in your workbook. You want to attach the embedded .OFT to an Outlook e-mail that is created from Excel using VBA.

To attach a file to an Outlook email you would use the Attachments.Add method. However, that method requires the object be located in the filesystem or an Outlook item (contact card, existing e-mail, etc.).


Per the Microsoft Documentation:

.Add Source Parameter: The source of the attachment. This can be a file (represented by the full file system path with a file name) or an Outlook item that constitutes the attachment.

I don't believe it's possible to directly attach a file embedded within Excel to an Outlook e-mail.


You could copy/paste the OFT to disk, then reference the path.

Sub CopyEmbeddedObjectDisk()
 
 'Copy Object
 ActiveSheet.OLEObjects("Object 1").Copy
 
 'Paste Copied Object to Same Directory as Active Workbook
 CreateObject("Shell.Application").Namespace(ActiveWorkbook.Path).Self.InvokeVerb "Paste"
 
End Sub

If you are trying to apply the embedded OFT as your e-mail template, I don't believe that's directly possible either. In the past I accomplished a similar task by creating a worksheet in the workbook, inserting Text Boxes, and working with those as my "templates". Then loading the HTML Body referencing the text boxes.