file save as problem (VBA ish) 4 spaces causes a problem (1 Viewer)

[GaryPanic]

ok this a bit of ponder

I have a file save as for Outlook messages (works pretty well)

1 user has had a gremlin and I thinkj I have tracked it down
the file save as takes the subject of the email and makes it part of the "save as name"

if I do Dave as the subject - no problem

if I do dave (space space space space) outlook does weird stuff and records a square after dave so when I do a vba file save as I get Dave[] (its a box and that was the best I could find to represent it ) and save as will fall over illegal character stuff

any thoughts

 

[Isaac]

can you explain more what is generating the (space space space space)? where is that being read or derived from? please post entire code/procedure

maybe there really is a non printable or otherwise objectionable character there which really isn't a space, but looked like one

and then finally ends up ▮

 

[GaryPanic]

ok I have

Public Sub SaveMessageAsMsg()
Me.Refresh
Dim oMail As Outlook.MailItem
Dim objitem As Object
Dim sPath As String
Dim dtDate As Date
Dim sName As String
Dim mystring As String
Dim trimstring As String




Dim enviro As String
Dim stFolder As String


enviro = CStr(Environ("USERPROFILE"))
For Each objitem In ActiveExplorer.Selection
If objitem.MessageClass = "IPM.Note" Then
Set oMail = objitem - **** here it has Dave with the square but only if 4 spaces (I presume 4 or more )





sName = oMail.Subject




ReplaceCharsForFileName sName, "-"


dtDate = oMail.ReceivedTime
sName = Format(dtDate, "yyyy mm dd", vbUseSystemDayOfWeek, _
vbUseSystem) & Format(dtDate, "-hhnnss", _
vbUseSystemDayOfWeek, vbUseSystem) & "-" & sName & ".msg"


' need to change folder location to
' Me.FolderLocation
stFolder = Me.FolderLocation

' should be changed to stfolder ="S:\xxxxx\xxxxxx\Clients\"
stFolder = "S:\xxxxxx\xxxx\Clients\" & Me.QuoteNo & "\"


sPath = stFolder
' sPath = enviro & "\Documents\"

Debug.Print sPath & sName
oMail.SaveAs sPath & (sName), olMSG

End If
Next

End Sub

 

[GaryPanic]

and I get in the itermediate window

S:\xxxxxx\xxxxx\Clients\12245\2021 05 19-223600-FW-Dave .msg
S:\xxxxxx\xxxxx\Clients\12245\2021 05 19-223600-FW-Dave(space) space space space .msg

so I can see the 4 spaces - if I resend and delete a space to 3 it works ( not that their should be any spaces - but it might happen )

 

[arnelgp]

can you Debug your code?
what is this function for?

ReplaceCharsForFileName sName, "-"

 

[The_Doc_Man]

As a fairly simple test, since you know how to get to the intermediate window, try this on your sName:

Debug.Print ASC( Mid( sName, 4 + Instr(1, sName, "Dave" ) ), 1 )

Then do the same for 5+ the Instr, 6 + the Instr, etc. Pick apart the characters that look like spaces and determine what they really are. If they are real spaces, they should print as 32. If they are some other non-printing character, we might need to know what they are.

 

[Isaac]

You must be running this code directly in outlook, not access, right?

 

[GaryPanic]

Will try ... and come back
(Isaac - no Access - but it gets the file name from Outlook (Subject line) )

 

[GaryPanic]

OK results ( and I did it from 2 onwards

so
2 = 48
3 = 50

4 =49
5 = 32
6 = 48
7= 53
8= 32
9 = 49
10= 57

(I stopped)

 

[arnelgp]

OK results ( and I did it from 2 onwards

these are results of what formula?

 

[Gasman]

these are results of what formula?

Post #6 ?

 

[GaryPanic]

sorry yes Post 6

Debug.Print ASC( Mid( sName, 4 + Instr(1, sName, "Dave" ) ), 1 )

 

[Isaac]

Will try ... and come back
(Isaac - no Access - but it gets the file name from Outlook (Subject line) )

then why is ActiveExplorer not qualified?

 

[The_Doc_Man]

I think you must have left something out there...

2 = 48 = "0"
3 = 50 = "2"

4 =49 "1"
5 = 32 = space
6 = 48 = "0"
7= 53 = "5"
8= 32 = space
9 = 49 = "1"
10= 57 = "9"

From your earlier string, what it appears is that you left out something.

2021 05 19-223600-FW-Dave(space) space space space .msg

It would seem that the InStr wasn't used correctly or else the "Dave" wasn't part of the string. What I was trying to do was to find the stuff in green but instead found the stuff in red. You need to repeat the experiment BUT you need to also do a Debug.Print of sName to verify that "Dave" is, in fact, part of the string. My error for the incorrect assumption. You should be able to copy/paste that earlier command I gave you if "Dave" really IS a part of the string. If so, you would see a 32. (Unless the parenthesis really IS part of the sequence.)

IF "Dave" is in sName when you reach the breakpoint and enter the immediate window, you should be able to copy/paste these lines in turn.


Debug.Print ASC( Mid( sName, 4 + Instr(1, sName, "Dave" ) ), 1 )
Debug.Print ASC( Mid( sName, 5 + Instr(1, sName, "Dave" ) ), 1 )
Debug.Print ASC( Mid( sName, 6 + Instr(1, sName, "Dave" ) ), 1 )
Debug.Print ASC( Mid( sName, 7 + Instr(1, sName, "Dave" ) ), 1 )

If those are really spaces, then each of these lines should return a 32. If they are not, it would be instructive to see what they are.

 

[Cronk]

That should be
Debug.Print Asc(Mid(sName, 5 + InStr(1, sName, "Dave"), 1))

 

[The_Doc_Man]

Oh, did I get the parentheses wrong? I stand corrected. Thank you , Cronk.

@GaryPanic - as Cronk pointed out, I got a parenthesis error. See the way his correction reads. the idea of separately probing using the offsets of 4, 5, 6, 7 etc. is still correct - but my original parenthesis was not. Sorry and mea culpa.

 

[GaryPanic]

I used Dave as an example - but I will test it shortly its actually the name of a client that is in the email Subject line

 

[The_Doc_Man]

In that case, the "4+" part would be replaced by the length of the name you were seeking. Like "Alfred" would start at 6+, but Bob would start at 3+ and as needed for other names.

 

[GaryPanic]

yeah - got it it will be xxx (space) xxxxx 7 plus a space (8)
one for tomorrow (your input much appricated)