Error when saving mail based on subject that has more than one consecutive space

Question

When the code constructs my email subject line, adding the date and time, it also adds a big space before the .msg file extension.

If I don't delete the space when the message box comes up for me to check the saving location, the code jumps to the error handler.

If I delete the space (which looks like 4 or 5 spaces), the file saves correctly.

Dim oMail As Outlook.MailItem
Dim objItem As Object
Dim atch As Attachment
Dim sPath, strFolderPath As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim answer As String
Dim dtDate As Date
Dim sName As String
Dim enviro As String
Dim jobNumber As String
On Error GoTo errHandler
enviro = CStr(Environ("USERPROFILE"))
jobNumber = InputBox("Please enter job number", "Saving to Job Folder", "Enter Job Number Here")

If jobNumber = "" Then Exit Sub
'create default name for saving file
If IsNumeric(jobNumber) Then
    strFile = Int(jobNumber / 100) & "00-" & Int(jobNumber / 100) & "99\" & jobNumber & "\"
Else
    strFile = ""
End If
strPathFile = "\\vacdc\VCI JOBS\" & strFile

If Dir(strPathFile, vbDirectory) = vbNullString Then
    strFile = Int(jobNumber / 100) & "00-" & Int(jobNumber / 100) & "99\"
    strPathFile = "\\vacdc\VCI JOBS\" & strFile
End If

answer = MsgBox(strPathFile, vbYesNoCancel + vbQuestion, "Save emails here?")
If answer = vbYes Then
    sPath = strPathFile
Else
    If answer = vbCancel Then GoTo exitHandler
    strFolderPath = BrowseForFolder("\\vacdc\VCI JOBS\")
    sPath = strFolderPath & "\"
End If

For Each objItem In ActiveExplorer.Selection
    If objItem.MessageClass = "IPM.Note" Then
        Set oMail = objItem
        sName = oMail.Subject
        ReplaceCharsForFileName sName, "_"
        dtDate = oMail.ReceivedTime
        sName = Format(dtDate, " mm-dd-yyyy", vbUseSystemDayOfWeek, _
          vbUseSystem) & " " & sName & ".msg"
        sName = InputBox("", "", sName)
        Debug.Print sPath & sName
        oMail.SaveAs sPath & sName, olMSG

I was asked to add this subroutine that my code from earlier post calls:

Private Sub ReplaceCharsForFileName(sName As String, sChr As String)
    sName = Replace(sName, "'", sChr)
    sName = Replace(sName, "*", sChr)
    sName = Replace(sName, "/", sChr)
    sName = Replace(sName, "\", sChr)
    sName = Replace(sName, ":", sChr)
    sName = Replace(sName, "?", sChr)
    sName = Replace(sName, Chr(34), sChr)
    sName = Replace(sName, "<", sChr)
    sName = Replace(sName, ">", sChr)
    sName = Replace(sName, "|", sChr)
End Sub

Comment out `On Error GoTo errHandler` - where does it fail and with what error message? Likely there is a character in the subject which is not valid in a file name. — Tim Williams, Jul 21 '21 at 19:18
I didn't include all the code. It fails at the end of the code that I posted. (When stepping through the code the next item after the posted code is errHandler.) When it goes to the errHandler, there's code that produces a text box with the message that the operation was canceled. There is a colon in the subject line when someone replies (after RE) but there's large spaces created by the code it seems. If I delete the spaces the message saves. Thanks. — user3523983, Jul 22 '21 at 13:31
When I get to the Debug.Print line a box comes up showing me the new subject line. This is where the spaces show up before .msg. If I delete the spaces the message will save instead of going to the error handler. — user3523983, Jul 22 '21 at 13:58

score 1 · Answer 1 · answered Jul 21 '21 at 19:30

The code where the Subject property is used:

  sName = oMail.Subject
                ReplaceCharsForFileName sName, "_"
                dtDate = oMail.ReceivedTime
                sName = Format(dtDate, " mm-dd-yyyy", vbUseSystemDayOfWeek, _
                  vbUseSystem) & " " & sName & ".msg"
                  sName = InputBox("", "", sName)
                  Debug.Print sPath & sName
                oMail.SaveAs sPath & sName, olMSG

I'd suggest grabbing the subject string and try to save any file with such name. Are you able to get the file saved?

If the pure subject line works correctly as a file name, than I'd suggest checking the parameter string passed to the SaveAs method. Most probably it contains forbidden symbols. The forbidden printable ASCII characters on Windows are:

 < (less than)
  > (greater than)
  : (colon - sometimes works, but is actually NTFS Alternate Data Streams)
  " (double quote)
  / (forward slash)
  \ (backslash)
  | (vertical bar or pipe)
  ? (question mark)
  * (asterisk)

See What characters are forbidden in Windows and Linux directory names? for more information.

Finally, I'd suggest using string functions to replace forbidden symbols with allowed ones. For example, you may find the VBA Replace function helpful.

Error when saving mail based on subject that has more than one consecutive space

1 Answers1