Delete document after attaching to email

Question

Newbie here. I've created a Macro-enabled word document with a Submit button. The document is read-only, so what I'm trying to do is have it save a temp file, attach the temp file, then delete it. Simple enough, right? Everything works except it won't delete. Attached is the code for the Submit button. Please help! Thanks.

Public Sub SubmitButton_Click()
Dim OL              As Object
Dim EmailItem       As Object
Dim Doc             As Document
Dim sTempFilePath   As String

Application.ScreenUpdating = False
sTempFilePath = ("C:\temp\test.doc")

Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)
Set Doc = ActiveDocument
ActiveDocument.SaveAs FileName:="C:\temp\test.doc"
With EmailItem
    .Subject = "Application For Leave Form"
    .To = "email@email.com"
    .Attachments.Add sTempFilePath
    .Send
End With

Application.ScreenUpdating = True
Set OL = Nothing
Set EmailItem = Nothing
Set Doc = Nothing
ActiveDocument.Close
Kill sTempFilePath

End Sub

For simplicity I took out a lot of the code, and it's still not deleting the file. What's wrong with this?

Public Sub SubmitButton_Click()
Dim Doc             As Document
Dim sTempFilePath   As String

sTempFilePath = ("C:\temp\test.doc")

Set Doc = ActiveDocument

ActiveDocument.SaveAs FileName:="C:\temp\test.doc"

ActiveDocument.Close

Kill sTempFilePath

End Sub

Hello. Check [this answer](http://stackoverflow.com/questions/67835/deleting-a-file-in-vba) and see if it helps — Victor Moraes, Nov 16 '16 at 20:23
I am not familiar with VBA for Word, but by debugging the code I noticed that in the line `ActiveDocument.Close` you are actually closing the document which is running the macro, therefore the `Kill sTempFilePath` is never executed — Victor Moraes, Nov 16 '16 at 21:02
That absolutely makes sense! Any thoughts on how I should go about it then? If I move the Kill above it I get an access denied error (in use). — Mike, Nov 16 '16 at 21:06
See my answer, although user3598756 has also provided an alternative solution — Victor Moraes, Nov 16 '16 at 21:36

score 1 · Answer 1 · answered Nov 16 '16 at 21:32

use FileSytemObject() object to make a copy of the wanted file and finally delete it

here's an example with a late binding

Public Sub SubmitButton_Click()
    Dim sTempFilePath   As String

    sTempFilePath = ("C:\temp\test.doc")

    With CreateObject("Scripting.FileSystemObject") '<--| instantiate a running instance of 'FileSystemObject' object
        .CopyFile ActiveDocument.FullName, sTempFilePath '<--| copy active document into a new file
    End With

    '....code to process sTempFilePath

    Kill sTempFilePath '<--| and finally... kill it!

End Sub

score 1 · Accepted Answer · answered Nov 16 '16 at 21:34

You can add a new document as a copy to the current document and save that copy to the desired location. Then, once you close the file, you are closing the newly created file and can successfully delete it. This worked for me and hopefully will work for you.

See the code for your snippet:

Dim Doc             As Document
Dim sTempFilePath   As String

sTempFilePath = ("C:\temp\test.doc")

'the next line copies the active document
Application.Documents.Add ActiveDocument.FullName

ActiveDocument.SaveAs FileName:="C:\temp\test.doc"

ActiveDocument.Close

Kill sTempFilePath

Applying this to your working code, you would have this:

Public Sub SubmitButton_Click()
Dim OL              As Object
Dim EmailItem       As Object
'Dim Doc             As Document   'not needed
Dim sTempFilePath   As String

Application.ScreenUpdating = False
sTempFilePath = ("C:\temp\test.doc")

Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)
'Set Doc = ActiveDocument ' not needed
Application.Documents.Add ActiveDocument.FullName
ActiveDocument.SaveAs FileName:="C:\temp\test.doc"
With EmailItem
    .Subject = "Application For Leave Form"
    .To = "email@email.com"
    .Attachments.Add sTempFilePath
    .Send
End With

Application.ScreenUpdating = True
Set OL = Nothing
Set EmailItem = Nothing
'Set Doc = Nothing
ActiveDocument.Close
Kill sTempFilePath

End Sub

Reference: http://www.vbaexpress.com/kb/getarticle.php?kb_id=961

Thanks guys! I'm going to dig into it again tomorrow and I'll be sure to post the results. — Mike, Nov 16 '16 at 21:41
Victor, THANK YOU. That worked perfect. Even better is that it doesn't close the original so a user can fill it out a couple times without reopening. Awesome. I was wondering though, how hard would it be to rename the attachment and subject line to "Application For Leave Form - First Last" (as in, their first and last name as filled out in the Word form)? The first and last name is put in a "Rich Text Content Control" in Word. — Mike, Nov 17 '16 at 15:41
I'm glad the solution worked out for you. As I mentioned before, I don't know much about VBA for Word, but I think you can have a start on what you need in [here](https://msdn.microsoft.com/en-us/library/office/ff836891.aspx), in [here](http://stackoverflow.com/questions/29524537/extract-data-from-content-controls-in-word-to-excel) and in [here](http://stackoverflow.com/questions/10823874/with-a-word-contentcontrol-datepicker-how-do-i-get-the-value). If you have issues after that, I'd post another question with the new issue. Happy coding :) — Victor Moraes, Nov 17 '16 at 19:45

Delete document after attaching to email

2 Answers2