Solved

Script to remove signature from received emails

Posted on 2010-09-14
30
1,198 Views
Last Modified: 2012-05-10
I have a single Exchange mailbox that when it recceives any email, a rule/script should run that removes all images/text from the body of the email starting with the first signature in the body going from the top down. So, it should remove the first signature (if there are more than one) and everything after it. The signatures will always begin with 2 hyphens, for example '--' without the quotes. On the rare occasion that a double hypen is used in the portion of the body of the message that I don't want removed, its not a big deal and can easily be fixed. I know I need a VBA script in Outlook if I want to do this, but I have no skills for VBA coding.

This is so that users that have an IT issue can send an email to the mailbox, and then we have a program that takes any emails in the mailbox and turns them into an IT service ticket. We only have about 50 users total but it would be nice if I didn't have to manually delete the signature, disclaimer, etc from each ticket.

Example signature;

--
My Name
My Company
(123) 456-7890
me @company.com
[image - logo1]
[image - logo2]
[image - logo3]
-----
LENGTHY disclaimer
-----
0
Comment
Question by:TheGorby
  • 14
  • 10
  • 6
30 Comments
 
LVL 11

Author Comment

by:TheGorby
Comment Utility
Also, will this even work if Outlook isn't open? I.E. will this run on the server?
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Hi, TheGorby.

It is not possible to do this at the server via scripting.  It'd have to be at the client.  I'm pretty sure I can manage writing a script that does this, but that brings up another problem.  Outlook's built-in security is going to pop up a warning each time the script runs.  The warning will say that a program is accessing your mailbox and ask for your permission to allow it to continue.  There's no way to turn that off, but there are ways to work around it.  They are

1.  Sign the code.  Here's a link to instructions on doing that: http://msdn.microsoft.com/en-us/library/aa155754(office.10).aspx
2.  Use ClickYes (http://www.contextmagic.com/express-clickyes/), a small utility that'll click the Yes button for you.  It creates a security hole though, since a virus could start sending messages and ClickYes would click the Yes button for it too.  
3.  Use Redemption (http://www.dimastr.com), a COM library that enables code to safely bypass Outlook security.
0
 
LVL 24

Expert Comment

by:purplepomegranite
Comment Utility
If the code is written as an Outlook macro (i.e. using the Macro editor in Outlook) then there won't be any security prompts.  If this code is to run on a single computer this is quite straightforward - if more than one, then the code will need to be copied to each Outlook client.

It is possible to run this on the server, but it isn't a simple exercise.  With Exchange 2007 and above you'd need an Exchange Transport Agent (these can be written in .Net) to intercept incoming messages to the particular mailbox.  Prior to Exchange 2003 it is even more difficult - mainly because I assume the messages would be internal (i.e. from other users in the organisation).  An SMTP event sink can be used on Exchange 2003, but it doesn't work for internal messages as these do not get routed using SMTP.
0
 
LVL 11

Author Comment

by:TheGorby
Comment Utility
Great comments, I really appreciate it. At this point, BlueDevilFan I'd say that option #1 sounds the best for my situation.
I will if I have to, but I'd rather not leave Outlook open and running on a server. Is it possible to code something for the sender's client instead, to remove the unwanted text/etc before it even reaches the receiving mailbox?
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
"Is it possible to code something for the sender's client instead, to remove the unwanted text/etc before it even reaches the receiving mailbox?"
Absolutely.  In fact that'd be a lot easier than trying to extract it at the received end.
0
 
LVL 11

Author Comment

by:TheGorby
Comment Utility
Great! The script should trigger if the user sends an email to one specific email address, let's call it helpbox@domain.com. If the message is sent to any other recipients it's still OK to let the script do its thing.
I guess one last thing, the more centrally-deployable this solution is the better, it's never fun to VNC into 40 machines one after the other :) But at this point I'll do what it takes, I just implemented our 'email-to-ticket' settings and I'm already sick of removing extra text and attachments from the tickets. Thanks again
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
There is no means I know of for centrally deploying an Outlook macro.  The macro would have to be installed on each computer.  Rather than removing the signature from a message as it's sent, my thought is to give the end user a means of creating a help desk message that doesn't contain a signature to begin with.  For example, a toolbar button in Outlook that when clicked generates a pre-addressed help desk message that doesn't have a signature.  Would that work?
0
 
LVL 24

Expert Comment

by:purplepomegranite
Comment Utility
If you can assume that no users already have macros, there is a way of distributing macros via group policy.  All macros are held in the VbaProject.OTM file in the user profile (%appdata\Microsoft\Outlook).  You can write a script to copy this to each user's profile and then they should have the macro next time they start Outlook.
0
 
LVL 24

Expert Comment

by:purplepomegranite
Comment Utility
This is assuming OL2007 or below, incidentally.  I haven't checked in OL2010 (though can if needed).
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Oh, good point purplepomegranite!  I'm always thinking that folks already have macros,  
0
 
LVL 11

Author Comment

by:TheGorby
Comment Utility
You guys (gals?) rock!! BlueDevilFan, that toolbar to bring up a preformatted template is a better solution than I could have ever imagined. The button should be named 'HelpBox' (without quotes), and if an image can be put in front of the text that would be great, I have an image in mind in any format needed (ICO, JPG, BMP, PNG). If it's possible to prevent text from being entered in the Subject that would be a bonus, but nothing to worry about if not. I think that covers it, let me know if you need any more info.
purplepomegranite - I'd bet my life savings none of my users have made any Outlook macros, that deployment method should work beautifully. Thanks.
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Cool.  The button can include an image.  I think it'll work with an .ico, but I'll have to double-check.  I'll see what I can do with the subject.
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
The code for this solution comes in two parts.  This is part #1.  Follow these instructions to add the code to Outlook.

1.  Start Outlook
2.  Click Tools > Macro > Visual Basic Editor
3.  If not already expanded, expand Microsoft Office Outlook Objects
4.  Right-click on Class Modules, select Insert > Class Module
5.  In the Properties panel click on Name and enter HelpBox
6.  Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook's VB Editor window
7.  Edit the code as needed.  I included comments wherever something needs to or can change
8.  Click the diskette icon on the toolbar to save the changes
9.  Close the VB Editor


'On the next line change the button caption'
Const BUTTON_CAPTION = "HelpBox"
'On the next line change the tooltip text'
Const BUTTON_TOOLTIP = "Create a new help request"
'On the next line change the path and name of the .bmp file containing the picture you want to appear on the button'
Const BUTTON_PICTURE = "c:\Users\David\Pictures\vlc.bmp"
'On teh next line change the address of the help mailbox'
Const HELPBOX_ADDR = "HelpBox"

Private ofcBar As Office.CommandBar
Private WithEvents ofcButton As Office.CommandBarButton
Private WithEvents olkMsg As Outlook.MailItem

Private Sub Class_Initialize()
    Dim oPic As stdole.IPictureDisp
    Set oPic = LoadPicture(BUTTON_PICTURE)
    On Error Resume Next
    Set ofcBar = Outlook.Application.ActiveExplorer.CommandBars("HelpBox")
    If TypeName(ofcBar) = "Nothing" Then
        'Create the toolbar'
        Set ofcBar = Application.ActiveWindow.CommandBars.Add("HelpBox", msoBarTop, False, True)
        Set ofcButton = ofcBar.Controls.Add(msoControlButton)
        With ofcButton
            .Picture = oPic
            .Caption = BUTTON_CAPTION
            .Style = msoButtonIconAndCaption
            .TooltipText = BUTTON_TOOLTIP
        End With
    End If
    On Error GoTo 0
    ofcBar.Visible = True
End Sub

Private Sub Class_Terminate()
    Set ofcButton = Nothing
    ofcBar.Delete
    Set ofcBar = Nothing
End Sub

Private Sub ofcButton_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
    Set olkMsg = Outlook.Application.CreateItem(olMailItem)
    With olkMsg
        .To = HELPBOX_ADDR
        .BodyFormat = olFormatPlain
        .Body = ""
        .Display
    End With
End Sub

Private Sub olkMsg_Close(Cancel As Boolean)
    Set olkMsg = Nothing
End Sub

Private Sub olkMsg_Send(Cancel As Boolean)
    olkMsg.Subject = ""
    olkMsg.Save
    Set olkMsg = Nothing
End Sub

Open in new window

0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
This is part #2.  Follow these instructions to add this code to Outlook.

1.  Start Outlook
2.  Click Tools > Macro > Visual Basic Editor
3.  If not already expanded, expand Microsoft Office Outlook Objects and click on ThisOutlookSession
4.  Copy the code from the Code Snippet box and paste it into the right-hand pane of
5.  Outlook's VB Editor window
6.  Edit the code as needed.  I included comment lines wherever something needs to or can change
7.  Click the diskette icon on the toolbar to save the changes
8.  Close the VB Editor
9.  Click Tools > Macro > Security
10. Set the Security Level to Medium
11. Close Outlook
12. Start Outlook
13. Outlook will display a dialog-box warning that ThisOutlookSession contains macros and asking if you want to allow them to run.  Say yes.

Dim objHelpBox As HelpBox

Private Sub Application_Startup()
    Set objHelpBox = New HelpBox
End Sub

Private Sub Application_Quit()
    Set objHelpBox = Nothing
End Sub

Open in new window

0
 
LVL 76

Accepted Solution

by:
David Lee earned 325 total points
Comment Utility
The code together creates a new toolbar with a single button.  Clicking that buttons creates a pre-addressed plain-text email with no signature.  The code also wipes the subject line as the message is sent.  The one real flaw is that the user can manually add a signature via Outlook's GUI.  

Once you've added the code and tested it to make sure it meets your needs, then you can deploy it to the other computers by following purplepomegranite's suggestion.  Here's how.

1.  Close Outlook
2.  Go to the folder C:\Documents and Settings\<username>\Application Data\Outlook  (I think that's the right path, but I'm doing this from memory since I don't have access to a Windows XP computer at the moment.)
3.  Copy the file VbaProject.OTM
4.  Deploy this file to the other computers via a logon script

The image that goes on the button has to be a 16x16 file in .bmp format.  Don't forget that it needs to be deployed to the computers too, or placed on a file share that they can all get to.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 24

Expert Comment

by:purplepomegranite
Comment Utility
If you use: %appdata%\Microsoft\Outlook

That will work on XP, Vista and Windows 7 when finding (and deploying) the macro file.  It'll also work if the folder has been redirected for some reason.
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Cool!
0
 
LVL 11

Author Comment

by:TheGorby
Comment Utility
Code works great, I tested it at home last night but I'm about to start testing deployment at work now.
"If you use: %appdata%\Microsoft\Outlook  That will work on XP, Vista and Windows 7..."
Do either of you know if this works for Office 2007 as well? We have 4 machines with '07 and none that I can test on, really.
0
 
LVL 24

Expert Comment

by:purplepomegranite
Comment Utility
Yes, I can confirm this works for Office 2003 and 2007.

The only thing you need to check when deploying is that the auto-open macros may not fire properly until you manually run a macro from that machine (these seem to rely on a setting outside the deployed file that will be corrected when you either go into the editor, or run a macro).
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
To add to purplepomegranite's confirmation, I wrote it on a 2007 box so it definitely works there.
0
 
LVL 11

Author Comment

by:TheGorby
Comment Utility
Sorry to keep dragging this question on, but - I'm having a bit of trouble bypassing the security warnings when users open Outlook. I really need to leave the macro security set to high, so I'm playing around with publisher certificates. I created one using selfcert.exe on the same machine I copied my VbaProject.otm file from, and the digital signature does carry over with the OTM file. However, once deployed to other machines users still have to install the certificate and check the box to always trust the publisher. I know it's not hard to do that but I would like this to be as seamless as possible, my users are very afraid of change and strange prompts.
I found one website that suggested copying the outlook.srs and outlook.xml files along with the OTM file, but that seems to do absolutely nothing. I'm also using SpyMe Tools to track registry changes, but it's not working so well either.
I'm currently researching creating some sort of domain-wide certificate to install using GPO, so that the publisher is automatically trusted by domain computers. Anyone have some suggestions to help me along?
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Sorry, I've no experience with certificates outside of a selfcert on the same computer.
0
 
LVL 24

Assisted Solution

by:purplepomegranite
purplepomegranite earned 175 total points
Comment Utility
You should be able to deploy the certificate using group policy.  I have found a guide here: http://www.unixwiz.net/techtips/deploy-webcert-gp.html

It is referring to an SSL (web) certificate, but the same theory should apply to any certificate.
0
 
LVL 11

Author Comment

by:TheGorby
Comment Utility
We expect to re-image this server anyway, but does anyone know what problem would cause this script to have an error 5 'invalid procedure call or argument'? Running on one TS server we get this error, but on another very similar TS server the toolbar works fine. Like I said, no big deal on this one since we're probably moving everyone to the working server soon anyway so the old one can be re-imaged.
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
What line of code is the error occurring on?
0
 
LVL 11

Author Comment

by:TheGorby
Comment Utility
Sorry, meant to include that the first time around... it's on line 18 from your post above, my only changes besides the first 8 lines were to change .BodyFormat = olFormatHTML on 44 and to remove line 55 (I was able to use the subject after all)
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Unless line 17 (i.e. On Error Resume Next) has been removed or commented out, then it should be impossible for the code to generate an error.  Line 17 specifically tells the VB interpreter to ignore errors.
0
 
LVL 11

Author Comment

by:TheGorby
Comment Utility
I thought that was the way it was supposed to be, but sure enough as you can see in the screen shot it's line 18. However I know the real problem isn't the script, it's the much-abused, multiple versions of Office installed on the TS server. Portions of Office 2002, XP, 2003 and 2007 are installed on this Win2k3 server, and needless to say we're amazed we have as few problems as we do.
ss1.bmp
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
I can't explain that.  The whole point of "On Error Resume Next" is to prevent the interpreter from stopping on an error.  I can't fathom how it's possible to have essentially turned error processing off and still have it trigger when an error occurs.  It shouldn't be possible, although clearly it's happening.  Must be something to do with Terminal Server.  Just another reason to despise thin-clients.
0
 
LVL 11

Author Comment

by:TheGorby
Comment Utility
As some other forums I came across suggested, I changed the Error Trapping option in VB editor to 'Break on Unhandled Errors' instead of the default 'Break on All Errors'. The error doesn't appear now, but the button doesn't appear either so I'm at a loss.
But like I said since it works fine on our other TS server we'll just move everyone over to that machine soon, I was just hoping to put this code to use as soon as possible.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Use these top 10 tips to master the art of email signature design. Create an email signature design that will easily wow recipients, promote your brand and highlight your professionalism.
If you don't know how to downgrade, my instructions below should be helpful.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now