• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Folder & Files DELETE ?

L

Logit

Active Member
Posted here as well : https://www.mrexcel.com/forum/excel-questions/1072427-delete-files-folder-not-working.html

I have a VBA email macro that first produce a PDF of a worksheet, creates a folder on the desktop named PDFs and saves the pdf of the worksheet there.

After sending the email, with the pdf attachment, the code successfully deletes the pdf file but refuses to delete the folder until the workbook is closed. Very inconvenient.

Here is the code I am using. What changes would you recommend to get the folder to delete without having to close the workbook first ?

Thank you.

Option Explicit

Sub pdf()
Dim wsA As Worksheet, wbA As Workbook, strTime As String
Dim strName As String, strPath As String
Dim strFile As String
Dim strPathFile As String


'On Error GoTo errHandler

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet


'replace spaces and periods in sheet name
strName = Replace(wsA.Name, " ", "")
strName = Replace(strName, ".", "_")

'create default name for savng file
'strPath = "G:\Finance\Corporate Accounting\SHIRLEY\A. Financial Planning Fee Payment Processing\"
strPath = "C:\Users\My\Desktop\PDFs\"
strFile = strName '"_" & strTime & "_" & Sheets("MDM Invoice").Range("B2").Value
strPathFile = strPath & strFile


Dim myFolder$
myFolder = Environ("UserProfile") & "\Desktop\PDFs"

If Dir(myFolder, vbDirectory) = "" Then
MkDir myFolder
End If

'export to PDF if a folder was selected
wsA.ExportAsFixedFormat 0, strPathFile

If Len(Dir$(myFolder)) > 0 Then
SetAttr myFolder, vbNormal
End If

'confirmation message with file info
MsgBox "PDF file has been created: " _
& vbCrLf _
& strPathFile


exitHandler:
Exit Sub
errHandler:
MsgBox "Could not create PDF file"
Resume exitHandler
End Sub


Sub Mail_workbook_Outlook()

Dim c As Range
Dim OutApp As Object
Dim OutMail As Object
Dim strPath As String
Dim FileName As String

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strPath = Environ("UserProfile") & "\Desktop\PDFs\"
FileName = Dir(strPath & "*.*")

'On Error Resume Next
For Each c In Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Cells
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = c.Value
.CC = ""
.BCC = ""
.Subject = c.Offset(0, 1).Value
.Body = "The parts have been placed on today's load sheet and will be processed by EOB today. The parts have also been transferred to the repository file."
FileName = Dir(strPath & "*.*")
.Attachments.Add strPath & FileName

'.Send '<-- .Send will auto send email without review
.Display '<-- .Display will show the email first for review
End With
'On Error GoTo 0
Next c


Set OutMail = Nothing
Set OutApp = Nothing

byby


End Sub

Sub byby()
Dim folder As Object
Dim path As String
path = Environ("UserProfile") & "\Desktop\PDFs"
Set folder = CreateObject("scripting.filesystemobject")

folder.deletefolder path, True

End Sub
Click to expand...
 

Attachments

Last edited:
As I wrote:
I won't test this...
because Your code has some features, which cannot work with my Excel!
... and ...
Why need to delete folder?
Will You run that macro only once?
 
General "cleanup" after running macro and before closing workbook. Perhaps it's just my OCD expressing itself ...
 
.. then it's Your way ...
How about this:
http://www.java2s.com/Code/VBA-Excel-Access-Word/File-Path/DeleteFolderbyusingFileSystemObject.htm
Is Your folder correct?

https://excel-macro.tutorialhorizon.com/filesystemobject-deletefolder-method/
has a little different than Yours ... compare Yourself:
Code: 
' << Your code
FnDeleteFolder (“c:\New Folder”)
' Your code >>

Function FnDeleteFolder(strCompleteFolderPath)
Set fso = CreateObject("Scripting.FileSystemObject")
Set objFolder = fso. DeleteFolder(strCompleteFolderPath)
End Function
 
I'm getting a "User Defined Type Not Defined" error on :

Dim myFileSystemObject As FileSystemObject

Tried defining as Object ... no go.

Suggestions ?
 
I have decided to place the temp folder on the C:\ path, rather than on the desktop. That eliminates seeing the folder until the workbook is closed.

I do thank you for your assistance ! Greatly appreciated.
 
You must log in or register to reply here.
Back
Top