Excel VBA Open Outlook Template as a template for editing

I don't know if this is possible, but I want the user to be able to open a template selected from a menu and

• The template remain as a template
• When the user clicks "Save" the template is either
• Saved to the same location it was called from, or at the user's discretion
• Saved as a new template under a new name.

Creating the menu system is not the problem here; it is having Outlook preserve the Template as a Template rather than converting it into a message to be sent. If there is a way to keep it as a template then I suspect the second part of the problem, directly saving it as a template, is probably solved.

The frustration presently is in opening the template using the following code (tbFileLocation" is the fully qualified file path and template file; i.e., "C:\Users\me\Documents\Peanuts.oft"):

Private Sub cbTemplateEdit_Click()
    Dim OutApp As Object
    Dim OutMail As Object
    
    ''''''''''''''''''''Activate Outlook if it isn't Open yet:
    On Error Resume Next
    Set OutApp = GetObject(, "Outlook.Application")
    If Err.Number = 429 Then
        Set OutApp = CreateObject("Outlook.application")
    End If
    On Error GoTo 0
    ''''''''''''''''''''End Activate Outlook

    Set OutMail = OutApp.CreateItemFromTemplate(frmOutlookTemplates.tbFileLocation)
    OutMail.display


End Sub

Is this possible? Possibly "CreateItemFromTemplate" is my mistake?

It is a huge inconvenience that every time one manually selects to save as a template Outlook insists on switching back to the default central location-

Thank-you very much, folks-

No problem Bruce!

I know this isn't exactly what you were looking for, but this will get the job done:

Take a look at this first.

All you need do is to establish the "MailItem" variable as a public object, let's call it "oPubOutMail", and for simplicity's sake, another public String variable which we will call "strPubOFTFileLocation".

Change your previous code as follows:

Private Sub cbTemplateEdit_Click()
    Dim OutApp As Object
 '   Dim OutMail As Object No longer used
    
    ''''''''''''''''''''Activate Outlook if it isn't Open yet:
    On Error Resume Next
    Set OutApp = GetObject(, "Outlook.Application")
    If Err.Number = 429 Then
        Set OutApp = CreateObject("Outlook.application")
    End If
    On Error GoTo 0
    ''''''''''''''''''''End Activate Outlook

    strPubOFTFileLocation = frmOutlookTemplate.tbFileLocation  'Here we assign the fully qualified path and filename to a Public string variable for later use after this code has run

    Set oPubOutMail = OutApp.CreateItemFromTemplate(strPubOFTFileLocation) 'Not really necessary to change the reference to the file path and name, but I did it for consistency
    oPubOutMail.display
    Set OutApp = Nothing

End Sub

Now create another command button labeled "Save Template" or the like that includes the following code:

    oPubOutMail.SaveAs strPubOFTFileLocation, 2
    Set oPubOutMail = Nothing

(We use the numeral "2" instead of the keyword "olTemplate" because you are using Late Binding, though you could have placed a public enumeration in your code as Cytop showed you how to do on another one of your inquiries as well)

Open the template as you would normally do and make your edits.

Once done, run the above code along with any other actions you need like setting visibility of various buttons and the like; it will save the file to the intended location.

Just a note: the file will be saved, but remains open. It can be hidden by other open programs so you will want to close that out too. I will ask you if you wish to save it, but you don't, as it is meaning if you want to save it as a regular mail item.

It's great to be schizophrenic. I would give my other self a reputation star for this if I could.

Posted in case anyone else in the future is looking for a similar solution.

By the way Bruce, if you want to make that email that opened up go away, then just modify that last bit of code to:

    oPubOutMail.SaveAs strPubOFTFileLocation, 2
    oPubOutMail.Close 1
    Set oPubOutMail = Nothing