VBA Finding file in directory with LARGEST file size.

[Lres81715]

Hiya all,

I've got everything working for this Macro except for one piece to this puzzle. I'm in the process of trying create an Email Attachment system that loads the "Signature" from Outlook. Our company uses multiple signatures for various types of emails (Default email Sig, Vendor sig, Overseas Sig, standard reply Sig). This macro will be used by multiple people so the Signature file naming convention is whatever the user decided to name their sig.
AKA, I can't just use Dir & "Default.htm" because it's not always named that.
The only consistency is the signature I want to use for this Macro happens to be the Largest file size.

I used Insert Outlook Signature in mail as a starting point where I grabbed these pieces of code.

[COLOR=#3366CC]    Dim [U]SigString [/U]As String
[/COLOR][COLOR=#3366CC]   Dim [U]Signature [/U]As String

[/COLOR]   [COLOR=black]'Change only Mysig.htm to the name of your signature[/COLOR]
    [U]SigString [/U]= Environ("appdata") & [FONT=Verdana]"\Microsoft\Signatures\[/FONT][COLOR=black][FONT=Verdana]Mysig[/FONT][/COLOR][FONT=Verdana].htm"
[/FONT]
    [U]Signature [/U]= GetBoiler([U]SigString[/U])    On Error Resume Next

    With OutMail
        .To = "ron@debruin.nl"
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .HTMLBody = strbody & "
" & [U][B]Signature[/B][/U]
        .Send    [COLOR=black]'or use .Display[/COLOR] [COLOR=#3366CC]   End With[/COLOR]

The line SigString = Environ("appdata") & "\Microsoft\Signatures\Mysig.htm" won't work for me as this name is different for each user's computer.

So, I need a process that will search through all the *.htm files in

SignaturePath = Environ("appdata") & "\Microsoft\Signatures\"
SignatureFile = Dir$(SignaturePath& "*.htm*", vbNormal)

This gets me the first *.htm* file found but it's not always the one I need. I want to cycle through the directory to find the largest *.htm* file size and return back to me that file to use in my Email.

Any help past this point would be appreciated.

 

[Jeffrey Mahoney]

How about this as a start?

Jeff

Sub FindLargestSignature()
  Dim SignaturePath As String
  Dim SignatureFile As String
  Dim fso As Object
  Dim fs As Object
  Dim S As Long
  Dim LargeFile As String
  Dim LargeSize As Long
  
  Set fso = CreateObject("Scripting.FileSystemObject")
  
  
  SignaturePath = Environ("appdata") & "\Microsoft\Signatures\"
  SignatureFile = Dir(SignaturePath & "*.htm*", vbNormal)
  If Len(SignatureFile) > 0 Then
    Set fs = fso.getfile(SignaturePath & SignatureFile)
    LargeSize = fs.Size
    LargeFile = SignatureFile
  End If
  
  Do
    SignatureFile = Dir()
    If Len(SignatureFile) > 0 Then
      Set fs = fso.getfile(SignaturePath & SignatureFile)
      S = fs.Size
      If S > LargeSize Then
        LargeSize = S
        LargeFile = SignatureFile
      End If
    Else
      Exit Do
    End If
  Loop
  
  MsgBox "The Largest file is named: " & LargeFile & " and is " & LargeSize & " bytes"
    
  
End Sub

 

[Jeffrey Mahoney]

With GETFILE you can retrieve more file information
.DateCreated
.Attributes
.DateLastAccessed
.DateLastModified

 

[Lres81715]

Jeff,

Thanks for steering me in the right direction.

two things.

  Do [B][U]While[/U][/B]    SignatureFile = Dir()
    If Len(SignatureFile) > 0 Then
      Set fs = fso.getfile(SignaturePath & SignatureFile)
      S = fs.Size
      If S > LargeSize Then
        LargeSize = S
        LargeFile = SignatureFile
      End If
    Else
      Exit Do
    End If
  Loop

Does that need to be Do or Do While?

Secondly, Dir() keeps exiting out of loop at this point . Apparently doesn't like Dir()

 

[Lres81715]

Scratch that Jeff,

Nevermind, figured out my problem. I had the Do and SignatureFile = Dir() on the same line.

It's working! Thanks a ton. Now I just need to test it on other users computers. Works fine on mine.

 

[Jeffrey Mahoney]

It is DO, because I'm exiting out of the loop if no file is found. DIR() searches for the next file based on the parameters you set in the previous DIR() command. It returns a blank if no more files are found.

 

[Lres81715]

Got it,

Thanks for the assistance btw. It's working perfectly. Dozen tests and the correct signature is appearing each time.

 

[Jeffrey Mahoney]

Nice, You're welcome.