VBA, Outlook, and ASCII

Hello,

I am writing a macro for Microsoft Outlook, and I am having an issue when the code is stripping out illegal characters from the subject of a message. The code below works as expected no matter what type of string I input, but the issue arises with one of the e-mails; it has a character in the subject line that shows up as a small box. I am pretty sure this means the character is not recognized in ASCII, but what is more interesting is when it is passed into the VBA code it shows up as a question mark when I hover over the variable. So far I cannot find a way to remove this character, and the resulting string is passed on to create a directory, crashing the macro.

Sub FigureThisOut()

'same as: !, ", #, *, ., /, :, <, >, ?, \, |
invalidChars = Array(Chr(33), Chr(34), Chr(35), Chr(42), Chr(46), Chr(47), Chr(58), Chr(60), Chr(62), Chr(63), Chr(92), Chr(124))
msgSubject = "Testing! The\" & "Subject# With* All. Of/ The: Restricted< Characters> Here? Goes\ Nothing|"

MsgBox (msgSubject)

For j = LBound(invalidChars) To UBound(invalidChars)
  temp = Replace(msgSubject, invalidChars(j), " ")
  msgSubject = temp
Next j
  msgSubject = Replace(msgSubject, "RE ", "")
msgSubject = Replace(msgSubject, "FW ", "")
msgSubject = Trim(msgSubject)

MsgBox (msgSubject)

End Sub

I also cannot find a way to recreate the issue without running the whole macro and extracting the specific subject of the e-mail, which in this case I cannot share publicly (go figure!). Hopefully I am just missing something simple, but any advice would be appreciated!

Thank you for your time,
whoabuddy

Hi whoabuddy,

I hope you're well.

I've done a bit of VBA before. I'm not an expert, but 2 ideas:

1)
You could comment out half of the "invalidChars" line for troubleshooting.

thus that line for example would become:

invalidChars = Array(Chr(33), Chr(34), Chr(35), Chr(42), Chr(46), Chr(47)) ', Chr(58), Chr(60), Chr(62), Chr(63), Chr(92), Chr(124))

Note the extra bracket & apostophe in the above.

Now save it & run the macro again.

• If it produces the empty box still & no new empty boxes , then you know that it's not any of Chr(58), Chr(60), Chr(62), Chr(63), Chr(92) or Chr(124) & you can then concentrate on the others & repeat until the empty box is no longer appearing.

• If it no longer produces the empty box, then you know its one of: Chr(58), Chr(60), Chr(62), Chr(63), Chr(92) or Chr(124), & you can test those --> until you get to one or more culprits

One possibility I can think of that this method may fall down on is if the subject of email contains a character which is not any of the Chr() you have specified (you have probably specified all of these though).

--------------------

2) Another is that instead of starting with something like

msgSubject = "Testing! The\" & "Subject# With* All. Of/ The: Restricted< Characters> Here? Goes\ Nothing|"

to test, you could start with

msgSubject = "Testing! The\"

firstly.

If this is fine, then do:

"Testing! The\" & "Subject"

next & so on.

----------

I hope that helps

Hi Dev,

Thanks for the reply! Although your logic is perfect for addressing the code, that's where the issue gets weird, the code actually works perfectly as far as I can tell. It will remove everything in the starting string, let me elaborate a little more about what is going on in the macro:

This portion of code is used to strip invalid characters from the subject of an e-mail message in Outlook, so I select the e-mails in the Inbox and run the macro, and I cannot figure out where this mystery character is coming from. I've tried Googling for it up and down but I do not see a way to check for it / remove it - and it makes the macro crash. If you look at the picture below you will see that it looks like a normal e-mail at first glance, but if you hover over the subject it has the box in it. When that gets put into the code, it's turned into a question mark, but removing the question mark via chr(63) does not affect it - it is unrecognized by VB entirely from what I can tell.



I want to find a way to strip this box out of the string, but what is it???

Thank you for your time,
whoabuddy

Hi,

I would suspect this to be an unprintable character, something like a page-break or a line-break. I have never worked with Outlook, but I've had situations in excel, where someone had inserted page-breaks into his CVS files. This would break my macro. (I ended up parsing them out with sed before even opening them in excel, so the solution will not work for you)
Personally I would start by identifying the character. Maybe write a quick loop that prints out the char-numbers for each of the letters in the email topic, that way you can then go ahead and identify the number and add it to your list of exceptions.

regards myrti

Hi myrti,

Thank you for replying! That sounds like a great idea to narrow it down, I will have to start playing with it, and just using the keywords "non-printable characters" got me several more Google results than "box-thingy" - I needed that technical term!

Looking at the ASCII chart here and these two scripts, it looks like I could strip out ASCII 1-31 and 127-255 as a secondary precaution. The funny part is Excel has a "clean" function built-in but it seems to be specific to Excel. If that doesn't work I can go through and try to identify the character, this was only present on two e-mails out of thousands so I suspect it will be an uncommon issue.

Best Regards,
whoabuddy

Aha - sounds like you're onto something there .

I'ld be interested in seeing the final VBA script when you get it working.

Hi Dev,

This is one of those side projects that I never thought would turn into anything that great, but after some long hours of re-coding it the way I like, I am really happy with how it looks. The macro will remove the attachments from an e-mail, save them to the hard drive, then place text links in the e-mail to the original attachments. So far I am averaging a 80-90% reduction in PST size with this script - I will let you know when it goes live on my site!

Best Regards,
whoabuddy

Let us know what character this turned out to be in the end! /curious

regards myrti

Hi myrti,

Trust me I will! I do not have access to the user's computer but the good news they said the macro is working fine otherwise! I will see if I can have her forward me a copy so I can test some more on my own, or I will take a look next time I am in their office.

Best Regards,
whoabuddy