Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

VBA, Outlook, and ASCII


  • Please log in to reply
8 replies to this topic

#1 whoabuddy

whoabuddy

    Bleepin' Verbose


  • Malware Response Instructor
  • 2,053 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Cottonwood, AZ
  • Local time:01:40 AM

Posted 28 July 2012 - 11:21 AM

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! :smash:

Thank you for your time,
whoabuddy
Meditate. Elevate. Appreciate. | "Life is a journey, love is the destination, happiness is the path!"
If I am helping you and have not responded within 48 hours, please send me a PM.
Vi Veri Universum Vivus Vici (VVVVV)
Excellent Security Advice
Proud member of UNITE

BC AdBot (Login to Remove)

 


#2 dev00790

dev00790

    Bleeping Chocoholic


  • Members
  • 5,037 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:UK
  • Local time:09:40 AM

Posted 28 July 2012 - 05:39 PM

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 :lol:, 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 :)

Edited by dev00790, 28 July 2012 - 05:39 PM.

Regards, dev00790

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

Marge: "Homer, the plant called. They said if you don't show up tomorrow don't bother showing up on Monday." Homer: "Woo-hoo! Four-day weekend!"I do not reply to Private Messages (PMs) asking for assistance - please use the forums instead. If I have been helping you, and I have not replied to your latest post in 48 hours please send me a PM. My Blog


#3 whoabuddy

whoabuddy

    Bleepin' Verbose

  • Topic Starter

  • Malware Response Instructor
  • 2,053 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Cottonwood, AZ
  • Local time:01:40 AM

Posted 29 July 2012 - 05:07 PM

Hi Dev,

Thanks for the reply! :thumbup2: 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.

Posted Image

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

Thank you for your time,
whoabuddy
Meditate. Elevate. Appreciate. | "Life is a journey, love is the destination, happiness is the path!"
If I am helping you and have not responded within 48 hours, please send me a PM.
Vi Veri Universum Vivus Vici (VVVVV)
Excellent Security Advice
Proud member of UNITE

#4 myrti

myrti

    Sillyberry


  • Malware Study Hall Admin
  • 33,784 posts
  • OFFLINE
  •  
  • Gender:Female
  • Location:At home
  • Local time:11:40 AM

Posted 30 July 2012 - 04:38 AM

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

is that a bird?  a plane? nooo it's the flying blueberry!

If I have been helping you and haven't replied in 2 days, feel free to shoot me a PM! Please don't send help request via PM, unless I am already helping you. Use the forums!

animinionsmalltext.gif

Follow BleepingComputer on: Facebook | Twitter | Google+


#5 whoabuddy

whoabuddy

    Bleepin' Verbose

  • Topic Starter

  • Malware Response Instructor
  • 2,053 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Cottonwood, AZ
  • Local time:01:40 AM

Posted 30 July 2012 - 08:13 AM

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
Meditate. Elevate. Appreciate. | "Life is a journey, love is the destination, happiness is the path!"
If I am helping you and have not responded within 48 hours, please send me a PM.
Vi Veri Universum Vivus Vici (VVVVV)
Excellent Security Advice
Proud member of UNITE

#6 dev00790

dev00790

    Bleeping Chocoholic


  • Members
  • 5,037 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:UK
  • Local time:09:40 AM

Posted 30 July 2012 - 08:39 AM

Aha - sounds like you're onto something there :).

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

Regards, dev00790

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

Marge: "Homer, the plant called. They said if you don't show up tomorrow don't bother showing up on Monday." Homer: "Woo-hoo! Four-day weekend!"I do not reply to Private Messages (PMs) asking for assistance - please use the forums instead. If I have been helping you, and I have not replied to your latest post in 48 hours please send me a PM. My Blog


#7 whoabuddy

whoabuddy

    Bleepin' Verbose

  • Topic Starter

  • Malware Response Instructor
  • 2,053 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Cottonwood, AZ
  • Local time:01:40 AM

Posted 30 July 2012 - 09:12 AM

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
Meditate. Elevate. Appreciate. | "Life is a journey, love is the destination, happiness is the path!"
If I am helping you and have not responded within 48 hours, please send me a PM.
Vi Veri Universum Vivus Vici (VVVVV)
Excellent Security Advice
Proud member of UNITE

#8 myrti

myrti

    Sillyberry


  • Malware Study Hall Admin
  • 33,784 posts
  • OFFLINE
  •  
  • Gender:Female
  • Location:At home
  • Local time:11:40 AM

Posted 01 August 2012 - 07:23 AM

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

regards myrti

is that a bird?  a plane? nooo it's the flying blueberry!

If I have been helping you and haven't replied in 2 days, feel free to shoot me a PM! Please don't send help request via PM, unless I am already helping you. Use the forums!

animinionsmalltext.gif

Follow BleepingComputer on: Facebook | Twitter | Google+


#9 whoabuddy

whoabuddy

    Bleepin' Verbose

  • Topic Starter

  • Malware Response Instructor
  • 2,053 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Cottonwood, AZ
  • Local time:01:40 AM

Posted 01 August 2012 - 06:06 PM

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
Meditate. Elevate. Appreciate. | "Life is a journey, love is the destination, happiness is the path!"
If I am helping you and have not responded within 48 hours, please send me a PM.
Vi Veri Universum Vivus Vici (VVVVV)
Excellent Security Advice
Proud member of UNITE




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users