VBA open workbook attached to outlook template

Question

Is there any way that one can open a workbook attached to an email template, edit, and save it prior to sending the message? I've created the mailitem object using Set Mesg = OutlookAp.CreateItemFromTemplate("C:\Template.oft") and I can see the attachment, but I can't see a way to open it thus far. If anyone has suggestions, or knows that this simply can't be done, I'm all ears.

Looks like I may have to save and edit the file prior to sending... Still open to ideas, but it looks like it simply isn't possible to open the attachment through VBA

Yes, I did, and I selected it as the correct answer. Technically though I wanted to simply grab the attatchment object and open / edit it in the current instance of excel without having to save it to a temp folder, etc. It doesn't seem like the first is possible, so I did end up using a variation of this method. — MattB
This answer should be opening the file in the current instance of Excel. Is it not? As for the temporary folder... the answer below doesn't use a temporary folder, but it does require to save and open the file from a known location. It may be possible to use a true temporary folder, if you can replicate the double-click action (that would open the file from a temp folder) but that would need to be done some other way. the Attachment object only supports two methods (.Delete and .SaveAsFile), I was looking for something like OLEFormat.DoVerb but that doesn't seem to be an option here. — David Zemens

David Zemens David Zemens · Accepted Answer · 2014-01-14T20:50:55

I assume you are automating Outlook from Excel. This solution may work for you, but as you note it does rely on saving the attachment and re-attaching the manipulated version of the file. Assuming you can write the code which will "edit" the Workbook attachment, this should work for you.

Sub TestOutlookTemplate()

Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim att As Outlook.Attachment
Dim templatePath As String
Dim tempFileName As String
Dim attWorkbook As Workbook


templatePath = "C:\users\david_zemens\desktop\Untitled.oft"
tempFileName = "C:\users\david_zemens\desktop\tempexcelfile.xlsx"

Set MyOutlook = CreateObject("Outlook.Application")

Set MyMail = MyOutlook.CreateItemFromTemplate(templatePath)
    MyMail.Display

    For Each att In MyMail.Attachments
        If att.DisplayName Like "*.xls*" Then
            att.SaveAsFile tempFileName

            'Now that you have saved the file, delete the attachment
            att.Delete

            'Open the file
            Set attWorkbook = Workbooks.Open(tempFileName)

            'Perform manipulation on the file
            attWorkbook.Sheets(1).Name = "Sheet ONE"

            'Save fhe file
            attWorkbook.Save

            'Close the file
            attWorkbook.Close

            MyMail.Attachments.Add tempFileName
        End If
    Next


'Send your mail (make sure you have added a recipient
MyMail.Send

Set attWorkbook = Nothing
Set MyMail = Nothing
Set MyOutlook = Nothing


End Sub

VBA open workbook attached to outlook template

1 Answers