Consulting

Results 1 to 6 of 6

Thread: Saving attachment with subject - help to change file type in the coding

  1. #1
    VBAX Regular
    Joined
    Feb 2018
    Posts
    70
    Location

    Saving attachment with subject - help to change file type in the coding

    Hi guys, hoping someone can help me with this. I've got this code which saves .xls attachments from my outlook folder and renames with part of the subject. I now want this to do exactly the same but for word documents. The documents are .DOC so I assumed I could just amend the code and replace .xls with .DOC but it nothing saves when I'm using .DOC

    I'd appreciate any help

    Thanks

    HTML Code:
    Sub saveAttachtoDisk(itm As Outlook.MailItem)
    Dim objAtt As Outlook.Attachment
    Dim saveFolder As String
    Dim strSubject As String
    Dim strName As String
        strSubject = "Our Ref: " & Right(itm.Subject, Len(itm.Subject) - InStrRev(itm.Subject, Chr(32)))
        saveFolder = "Y:\accounts\success fee bills\"
        For Each objAtt In itm.Attachments
            If Right(LCase(objAtt.fileName), 4) = ".xls" Then
                strName = CleanFileName(strSubject & ".xls", ".xls")
                objAtt.SaveAsFile saveFolder & strName
            End If
        Next objAtt
        Set objAtt = Nothing
    End Sub
    
    Public Function CleanFileName(strFilename As String, strExtension As String) As String
    'Graham Mayor
    'A function to ensure there are no illegal filename
    'characters in a string to be used as a filename
    'strFilename is the filename to check
    'strExtension is the extension of the file
    Dim arrInvalid() As String
    Dim vfName As Variant
    Dim lng_Name As Long
    Dim lng_Ext As Long
    Dim lngIndex As Long
        'Ensure there is no period included with the extension
        strExtension = Replace(strExtension, Chr(46), "")
        'Record the length of the extension
        lng_Ext = Len(strExtension)
        'Remove the path from the filename if present
        If InStr(1, strFilename, Chr(92)) > 0 Then
            vfName = Split(strFilename, Chr(92))
            CleanFileName = vfName(UBound(vfName))
        Else
            CleanFileName = strFilename
        End If
        'Remove the extension from the filename if present
        If Right(CleanFileName, lng_Ext + 1) = "." & strExtension Then
            CleanFileName = Left(CleanFileName, InStrRev(CleanFileName, Chr(46)) - 1)
        End If
    
        'Define illegal characters (by ASCII CharNum)
        arrInvalid = Split("9|10|11|13|34|42|47|58|60|62|63|92|124", "|")
        'Add the extension to the filename
        CleanFileName = CleanFileName & Chr(46) & strExtension
        'Remove any illegal filename characters
        For lngIndex = 0 To UBound(arrInvalid)
            CleanFileName = Replace(CleanFileName, Chr(arrInvalid(lngIndex)), Chr(95))
        Next lngIndex
    lbl_Exit:
        Exit Function
    End Function

  2. #2
    You could replace ".xls" with ".doc" or with "docx" if the current format (not ".DOC")
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3
    VBAX Regular
    Joined
    Feb 2018
    Posts
    70
    Location
    Thanks for the reply. I have tried this and its not working. It works fine when the file type is .xls but when I try it with a word (.doc) file and I've also changed to .docx and none of this works. These are a screen shot of the email attachment and its definitely a .doc file so can't figure out why it works for when its .xls but not .doc


  4. #4
    VBAX Regular
    Joined
    Feb 2018
    Posts
    70
    Location
    Quote Originally Posted by leemcder View Post
    Thanks for the reply. I have tried this and its not working. It works fine when the file type is .xls but when I try it with a word (.doc) file and I've also changed to .docx and none of this works. These are a screen shot of the email attachment and its definitely a .doc file so can't figure out why it works for when its .xls but not .doc

    attachment.jpg

  5. #5
    What is the extension of the attachment?
    If it is docx then as the macro looks for the last four characters, searching for ".docx" of ".doc" are not going to work. You need to search for "docx".
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  6. #6
    VBAX Regular
    Joined
    Feb 2018
    Posts
    70
    Location
    Thank you, this is now working. Thanks again for you help, its much appreciated

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •