HelpSpot Help Desk Software | HelpSpot Blog | HelpSpot Support

First steps for VBA macro to forward messages into HelpSpot


#1

Folks,

I am way out of my league when it comes to Windows automation. However, here’s a VBA macro that will auto-forward any mails put into a particular folder into HelpSpot. This is just to perhaps encourage someone who does a lot with VBA to take this further. In order to get this to run, you’ll need to follow the instructions here: http://tinyurl.com/yb6xtag as far as the location of the file, etc. Also, look through the code for the CHANGEME lines and make the appropriate changes.

Although the original author doesn’t mention it, you will need to disable or limit macro security in Outlook 2007 in order to get this to run. To do that, click on “Tools -> Trust Center -> Macro Security” and choose either ‘No security check for macros’ or ‘Warnings for all macros’. Neither is very satisfactory. Ideally, there would be an easy way to sign the code, etc. I’ll leave that for someone else to figure out. :slight_smile: Here’s a good place to start: http://office.microsoft.com/en-us/products/HA010872981033.aspx . It takes you through self-signed certs, commercial certs, etc.

Best regards,

Ben Chapman

---------------------8<-------------- Cut here ---------------- 8< -----------------------
’ Taken from http://tinyurl.com/yb6xtag
’ Copyright under GPL by Mark Grimes
’ Edited by Joshua McNary (with help from Vaish Pidathala) for use with Toodledo.com
’ Changed by Ben Chapman for use with HelpSpot 2/2010
’ This requires you to set a folder under your inbox in Exchange called “TO_HELPSPOT”
’ Change as required.
’ Change email address from HELPEMAIL@YOURDOMAIN.EDU to your helpspot email.

’ Most importantly, you’ll need to develop some solution to the Outlook macro security settings issue
’ in Office 2007. The settings are very restrictive.

Option Explicit

Private WithEvents objActionItems As Items

’ instantiate Items collections for folders we want to monitor
Private Sub Application_Startup()
Dim objNS As NameSpace
Set objNS = Application.GetNamespace(“MAPI”)
’ CHANGEME as required
Set objActionItems = objNS.GetDefaultFolder(olFolderInbox).Folders.Item(“TO_HELPSPOT”).Items
Set objNS = Nothing
End Sub

’ disassociate global objects declared WithEvents
Private Sub Application_Quit()
Set objActionItems = Nothing
End Sub

’ Forward msg when new msg added to folder
’ Prompt before sending
Private Sub objActionItems_ItemAdd(ByVal Item As Object)
Dim Response As Variant
Dim myForward As Variant

Response = MsgBox("Forward message (" + Item.Subject + ") to HelpSpot?", vbYesNo)
If Response = vbYes Then
    Set myForward = Item.Forward
    myForward.Subject = "" + Item.Subject + " ##forward:true##"
    ' CHANGEME as required
    myForward.Recipients.Add "HELPEMAIL@YOURDOMAIN.EDU"
    myForward.Send
End If

End Sub

------------ 8< ---------------------------- 8< ------------------------------------------------


#2

Thanks Ben! This got filtered off to spam, but I’ve gone ahead and pulled it out. Sorry for the delay!


#3

Not a problem! I hope it helps someone. Best, Ben