Outlook VBA macro copy to file has trouble with colons

We have been copying client correspondence to a shared folder so that we have all of the messages to a client in the same place.  When we drag & drop, everything is fine regardless of the subject line.  I wrote a macro to do the same thing, but it doesn't work right if the message has a colon in the subject line; the "file name" stops after the colon and it is not associated with the MSG extension.

Here's the code:
Sub SaveEmails()
    Dim StrSavePath As String
    Dim Prompt As String
    Dim Title As String
    Dim strSubject As String
    Dim strFileName As String
    Dim strFile As String
    Dim strPath As String
    Dim mItem As MailItem
    Dim Messages As Selection
    Set Messages = ActiveExplorer.Selection
    If Messages.Count = 0 Then
        Exit Sub
    End If
    Prompt = "Please enter the path to save all the emails to"
    Title = "Folder Specification"
    StrSavePath = BrowseForFolder("C:\ShareFile\Folders\Email")
    If StrSavePath = "" Then
        GoTo ExitSub:
    End If
    If Not Right(StrSavePath, 1) = "\" Then
        StrSavePath = StrSavePath & "\"
    End If
    For Each mItem In Messages
        strSubject = mItem.Subject
        strPath = StrSavePath
        strFile = strSubject & ".msg"
        strFileName = strPath & strFile
        mItem.SaveAs strFileName
End Sub

Open in new window

I am looking for help in getting the subject line to be the literal string regardless of spaces or characters.  Any help would be greatly appreciated.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David Johnson, CD, MVPOwnerCommented:
enclose the file name in quotes
Alexei KuznetsovMicrosoft Outlook MVPCommented:
David, this will not work here =)

The real problem here is that colon is not allowed in filenames. There are more invalid filename characters such as "<", ">" and others. In order to write the correct script, you need to replace invalid filename characters with something like underscore or any other allowed character or even empty string.

For example:
Function ReplaceInvalidChars(str As String) As String
    For Each c In Array("?", "*", ":", "|", "<", ">", "[", "]", """", "/")
        str = Replace(str, c, "_")
    ReplaceInvalidChars = str
End Function

Open in new window

Add this function to your code file and use it in your script like this:
strSubject = ReplaceInvalidChars(mItem.Subject)

Open in new window

M_PattonAuthor Commented:

Removing the standard invalid characters worked (thank you), but what is happening differently during the drag & drop function (which allows colons)?  Some of our subject lines contain time stamps and removing the colons from the time make is look weird.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Helen FeddemaCommented:
Here is a function I use for stripping inappropriate characters from file names:

Public Function StripNonAlphaNumericChars(strText As String) As String
'Strips a variety of non-alphanumeric characters from a text string
'Created by Helen Feddema 10-15-97
'Modified by Ruud H.G. van Tol 6-18-99
'Modified by Brad Beacham 6-Feb-2005
'Last modified by Helen Feddema 7-Feb-2005

On Error GoTo ErrorHandler

   Dim strTestString As String
   Dim strBadChar As String
   Dim i As Integer
   Dim strStripChars As String

   strStripChars = " `~!@#$%^&*()-_=+[{]};:',<.>/?" & Chr$(34) & Chr$(13) & Chr$(10)
   strTestString = strText

   For i = 1 To Len(strStripChars)
      strBadChar = Mid(strStripChars, i, 1)
      strTestString = Replace(strTestString, strBadChar, vbNullString)

   StripNonAlphaNumericChars = strTestString
   Exit Function

   MsgBox "Error No: " & Err.Number _
      & " in StripNonAlphaNumericChars procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Function

Open in new window

It is also useful when saving a document from code.
For the time stamps, if possible, save the time in a format that doesn't include colons or slashes.
Alexei KuznetsovMicrosoft Outlook MVPCommented:
Helen_Feddema, " `~!@#$%^&*()-_=+[{]};:',<.>/?" contains way too many chars that are allowed in filenames (like spaces, ~, !, @, # etc...)

M_Patton, no. Event drag-drop will not allow colons because this symbol is reserved for constructing file system paths like "C:\My folder" (please not the colon). You need to replace colons with something anyway.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
M_PattonAuthor Commented:
Simple, direct, and thorough.  Followed up with explanation.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.