PHPJunkyard support forum

Posted: **Thu Sep 27, 2012 6:37 am**

EDIT: the double spacing appears to be an outlook issue, when I convert HTML emails to plain text everything is double spaced.....

hesk 2.4.1

plain text emails copy right into the message field of /admin/new_ticket.php with no head aches... HTML formatted emails are double spaced.. I haven't tested with tables and pictures embedded.... kinda scared now.

This is the vba macro I'm using in outlook. What it does is take the selected message, log you into hesk with the name subject and message field.. would be really easy to add the email field too, although we don't use that.. we use it more like a worklog (stupid i know)

warning: code is not tested, use at own risk.

Code: Select all

Sub HelpdeskNewTicket()
Dim helpdeskaddress As String
Dim objMail As Outlook.MailItem
Dim strbody As String
Dim oldmsg As String
Dim senderaddress As String
Dim addresstype As Integer
Dim ie         As Object
Dim sResult    As String
Dim dtTimer    As Date
Dim lAddTime   As Long


Set objItem = GetCurrentItem()


' Sender E=mail Address
senderaddress = objItem.SenderEmailAddress

'Searches for @ in the email address to determine if it is an exchange user
addresstype = InStr(senderaddress, "@")

' If the address is an Exchange DN use the Senders Name
If addresstype = 0 Then
senderaddress = objItem.SenderName
End If


   Const sOVIDURL As String = "http://helpdesk.com/admin"
   Const lREADYSTATE_COMPLETE As Long = 4
   
      Set ie = CreateObject("InternetExplorer.Application")
      ie.Visible = True
      ie.navigate sOVIDURL
   
      dtTimer = Now
      lAddTime = TimeValue("00:00:20")
      
      Do Until ie.readystate = lREADYSTATE_COMPLETE And Not ie.busy
      DoEvents
      If dtTimer + lAddTime > Now Then Exit Do
      Loop
      
      ie.document.getElementById("user").Value = "yourusername"
      ie.document.getElementById("password").Value = "yourpassword"
      ie.document.forms(0).submit
      
      Do Until ie.readystate = lREADYSTATE_COMPLETE And Not ie.busy
      DoEvents
      If dtTimer + lAddTime > Now Then Exit Do
      Loop
      
      ie.navigate "http://helpdesk.com/admin/new_ticket.php"
      
      Do Until ie.readystate = lREADYSTATE_COMPLETE And Not ie.busy
      DoEvents
      If dtTimer + lAddTime > Now Then Exit Do
      Loop
      
               While ie.busy
            DoEvents
         Wend
      
      ie.document.getElementById("name").Value = objItem.SenderName
      ie.document.getElementById("subject").Value = objItem.Subject
      ie.document.getElementById("message").Value = objItem.Body
      dtTimer = Now
      lAddTime = TimeValue("00:00:20")
   Set ie = Nothing ' If you want to close it.


'Dim PageNumber As Object


Set objItem = Nothing
Set objMail = Nothing
End Sub

Function GetCurrentItem() As Object
Dim objApp As Outlook.Application
Set objApp = Application
On Error Resume Next
Select Case TypeName(objApp.ActiveWindow)
Case "Explorer"
Set GetCurrentItem = _
objApp.ActiveExplorer.Selection.Item(1)
Case "Inspector"
Set GetCurrentItem = _
objApp.ActiveInspector.currentItem
Case Else
End Select
End Function

wondering if it's some kind of CSS or field attribute thing I can do to fix this???

This code could potentially save someone hundreds of clicks in a day.. atleast it will me.. everyone is against email piping, support queue gets a lot of chain mails coming in, pasting screenshots in the body etc.....

Posted: **Thu Sep 27, 2012 8:36 am**

Outlook is notorious for generating crappy and non-standard HTML code.

What you can try is run the HTML first through this script before sending it to HESK:
http://htmlpurifier.org/

Posted: **Thu Sep 27, 2012 5:57 pm**

hm, that might work...

I think I need some code to pull only the text out of objItem.Body . I think when people hit enter in outlook HTML emails it puts a <p> tag and when that pastes into the message text area in hesk it reads that as a return so most things look double spaced (unless it was auto returned)... it also adds this to hyper linked text.. HYPERLINK="text"...

I'm sure i'll figure it out here soon... i don't want to convert the email itself to plain text.. and for some reason VBA keeps the returns... if i change ti plain text in outlook itself it doesn't... i hate outlook even more now.

Posted: **Fri Sep 28, 2012 12:17 pm**

I never coded in VBA myself, but have you tried using regular expressions to replace multiple spaces/lines "/s{2,}" with just one space/line?

Posted: **Fri Sep 28, 2012 4:33 pm**

ya i don't know if I care anymore... there a few lines of code to pull the body out from outlook... objItem.body pulls the body in clear text I've discovered.. but when VBA reads the clear text it throws returns in there on the HTML emails... also a little line like HYPERLINK="xxxx" which the xxx is still a hyperlink.. wtf???

When VBA converts the email to plain text it also throws the extra returns..... only in outlook itself changing it to plain text works without throwing in extra returns... also in the VBA if i convert the message to plain text it actually converts the email to plain text and i'd lose whatever photos were in there...

I give up, this is actually pretty good, at least the data is there and saves us about 3 alt tabs and tons of clicks...

here is the issue i'm trying to explain...

http://nerd-alert.info/images/hesk/001.png
http://nerd-alert.info/images/hesk/002.png

Posted: **Fri Sep 28, 2012 5:14 pm**

oh yes, thank you for taking a look and making suggestions, I really appreciate it.. Maybe this VBA code could benefit other hesk users..

Posted: **Fri Sep 28, 2012 7:06 pm**

I know what you mean. I'm not sure if this would work in VBA (I did C# and C++, not VBA), but you could try something like

Code: Select all

Replace chr(13),""

on the objItem.Body to remove carriage returns before sending to HESK (probably objItem.Body.Replace chr(13),"")?

Or use VBA to strip HTML?
https://www.google.com/search?hl=en&q=v ... plain+text

Posted: **Fri Sep 28, 2012 8:07 pm**

if I send an outlook HTML formatted message to pop3 ticket piping, it doesn't have those extra lines, what magic is going on there??

I HATE OUTLOOK

Posted: **Tue Oct 02, 2012 6:19 pm**

okay, I have it working now.. if then statements for RTF / plain text / and html formateed emails... The HTML fomratted emails are now displaying properly using

Code: Select all

   ie.document.getElementById("message").Value = Replace(objItem.Body, vbCrLf & vbCrLf, vbCrLf)

Code: Select all

Declare Function apiShowWindow Lib "user32" Alias "ShowWindow" _
            (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long

Global Const SW_MAXIMIZE = 3
Global Const SW_SHOWMINIMIZED = 2
Global Const SW_SHOWNORMAL = 1

Sub HelpdeskNewTicket()
Dim helpdeskaddress As String
Dim objMail As Outlook.MailItem
Dim strbody As String
Dim oldmsg As String
Dim senderaddress As String
Dim addresstype As Integer
Dim ie         As Object
Dim sResult    As String
Dim dtTimer    As Date
Dim lAddTime   As Long






Set objItem = GetCurrentItem()


' Sender E=mail Address
senderaddress = objItem.SenderEmailAddress

'Searches for @ in the email address to determine if it is an exchange user
addresstype = InStr(senderaddress, "@")

' If the address is an Exchange DN use the Senders Name
If addresstype = 0 Then
senderaddress = objItem.SenderName
End If


   Const sOVIDURL As String = "http://hesk/admin"
   Const lREADYSTATE_COMPLETE As Long = 4
   
      Set ie = CreateObject("InternetExplorer.Application")
      ie.navigate sOVIDURL
   
      dtTimer = Now
      lAddTime = TimeValue("00:00:20")
      
      Do Until ie.readystate = lREADYSTATE_COMPLETE And Not ie.busy
      DoEvents
      If dtTimer + lAddTime > Now Then Exit Do
      Loop
      
      ie.document.getElementById("user").Value = "username"
      ie.document.getElementById("pass").Value = "password"
      ie.document.forms(0).submit
      
      Do Until ie.readystate = lREADYSTATE_COMPLETE And Not ie.busy
      DoEvents
      If dtTimer + lAddTime > Now Then Exit Do
      Loop
      
      ie.navigate "http://hesk/admin/new_ticket.php"
      
      Do Until ie.readystate = lREADYSTATE_COMPLETE And Not ie.busy
      DoEvents
      If dtTimer + lAddTime > Now Then Exit Do
      Loop
      
               While ie.busy
            DoEvents
         Wend
      
      ie.document.getElementById("name").Value = objItem.SenderName
      ie.document.getElementById("subject").Value = objItem.Subject
      If objItem.BodyFormat = olFormatHTML Then
      ie.document.getElementById("message").Value = Replace(objItem.Body, vbCrLf & vbCrLf, vbCrLf)
      End If
      If objItem.BodyFormat = olFormatPlain Then
      ie.document.getElementById("message").Value = objItem.Body
      End If
      If objItem.BodyFormat = olFormatRichText Then
      ie.document.getElementById("message").Value = objItem.Body
      End If
      ie.Visible = True
      apiShowWindow ie.hwnd, SW_MAXIMIZE


      dtTimer = Now
      lAddTime = TimeValue("00:00:20")
   Set ie = Nothing ' If you want to close it.


'Dim PageNumber As Object


Set objItem = Nothing
Set objMail = Nothing
End Sub

Function GetCurrentItem() As Object
Dim objApp As Outlook.Application
Set objApp = Application
On Error Resume Next
Select Case TypeName(objApp.ActiveWindow)
Case "Explorer"
Set GetCurrentItem = _
objApp.ActiveExplorer.Selection.Item(1)
Case "Inspector"
Set GetCurrentItem = _
objApp.ActiveInspector.CurrentItem
Case Else
End Select
End Function

Posted: **Tue Oct 02, 2012 6:22 pm**

the only problem I have now is body's clear text turns hyperlinks in images and text into this:

HYPERLINK "http://www.url.com/"URL

wonder how I can get rid of that in VBA... or maybe some code in hesk's view ticket section to display that correctly?

PHPJunkyard support forum

outlook macro HTML emails double spaced in message field

outlook macro HTML emails double spaced in message field

Re: outlook macro HTML emails double spaced in message field

Re: outlook macro HTML emails double spaced in message field

Re: outlook macro HTML emails double spaced in message field

Re: outlook macro HTML emails double spaced in message field

Re: outlook macro HTML emails double spaced in message field

Re: outlook macro HTML emails double spaced in message field

Re: outlook macro HTML emails double spaced in message field

Re: outlook macro HTML emails double spaced in message field

Re: outlook macro HTML emails double spaced in message field