I am working on generating OFT files that will be e-mailed to customers who will then fill the To: and Subject: in and send them as e-mails to their clients.
My data comes from an Excel Workbook with one sheet containing static data (Books) and another information pasted in by the user (Pins). I've got a basic template that has placeholder text which gets replaced by the data in the aforementioned Excel sheets.
One important part of this is that I need the changed template to get saved to it's own file, so it can be stored for reference later. Originally I had the code below setup to open the template and call .SaveAs myFilename, olTemplate but that just made a broken 3KB file. You will notice I am copying the template to the actual destination file and operating on that instead.
My problem is that if I have the template item call .Display, everything is perfect. I see my image in the right place and all of the text is properly replaced. If I call .Save it saves out a copy of the original OFT template with no changes present.
Can anyone tell me what I'm doing wrong here? I've been searching here and google for hours trying to find some indication of what I'm missing. I'm trying to automate this thing as much as possible. Resaving the new OFT with Outlook's UI is a real time sink for a coworker and I'd like to eliminate that if possible. They're going to be generating dozens of these OFTs every day, so the work seems worth it in my opinion.
UPDATE I have managed to get this to work but the solution feels like a half-answer. The code below has been updated with changes that properly save the OFT.
Here is my sub:
Sub OutlookTemplate(ByVal pins As Range, ByVal book As Range, ByVal ImageLocation As String) Dim myolapp As Object Dim myItem As Object Set myolapp = CreateObject("Outlook.Application") 'myolapp.Session.Logon For Each p In pins.Cells If Not IsEmpty(p.Value) Then Dim myFilename As String myFilename = "c:\temp\" & Worksheets("PINS").Range("A2") & "-" & p.Value & ".oft" FileCopy "c:\template.oft", myFilename Set myItem = myolapp.CreateItemFromTemplate(myFilename) myItem.Save <- Added immediate save after creation of myItem myItem.Attachments.Add ImageLocation, olByValue, 0 myItem.HTMLBody = Replace(myItem.HTMLBody, "THEIMAGE", "<img src='cid:" & book.Cells(2).Value & "'" & "width='154'>") myItem.HTMLBody = Replace(myItem.HTMLBody, "PINHERE", p.Value) myItem.HTMLBody = Replace(myItem.HTMLBody, "THETITLE", book.Cells(1).Value) myItem.HTMLBody = Replace(myItem.HTMLBody, "THESUBTITLE", book.Cells(3).Value) myItem.HTMLBody = Replace(myItem.HTMLBody, "THEAUTHORS", book.Cells(4).Value) myItem.HTMLBody = Replace(myItem.HTMLBody, "THEDESCRIPTION", book.Cells(5).Value) ' Leaving the next line off results in a broken image ' when .SaveAs is called myItem.Display ' This saves all of the changes out to the file properly ' in combination with .Display ' Note: if I call myItem.SaveAs myFilename, olTemplate ' I get the 3KB broken OFT. Omitting ,olTemplate works myItem.SaveAs myFilename End If Next End Sub