Script to remove signature from received emails
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
-----
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
-----
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.
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.
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.
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.
ASKER
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?
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?
"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.
Absolutely. In fact that'd be a lot easier than trying to extract it at the received end.
ASKER
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
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
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?
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\Outloo
This is assuming OL2007 or below, incidentally. I haven't checked in OL2010 (though can if needed).
Oh, good point purplepomegranite! I'm always thinking that folks already have macros,
ASKER
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.
purplepomegranite - I'd bet my life savings none of my users have made any Outlook macros, that deployment method should work beautifully. Thanks.
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.
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
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
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.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you use: %appdata%\Microsoft\Outloo
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.
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.
Cool!
ASKER
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\Outloo
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.
"If you use: %appdata%\Microsoft\Outloo
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.
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).
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).
To add to purplepomegranite's confirmation, I wrote it on a 2007 box so it definitely works there.
ASKER
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?
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?
Sorry, I've no experience with certificates outside of a selfcert on the same computer.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
What line of code is the error occurring on?
ASKER
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)
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.
ASKER
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
ss1.bmp
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.
ASKER
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.
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.
ASKER