Help with code very much needed. Please

[aleb]

I have a code:
+++++++++++++++++++++++++++++++++
Private Sub SendEmailToAssignedTo_Click()
Dim myOlApp As New Outlook.Application
Dim myItem As Outlook.TaskItem
Dim myDelegate As Outlook.Recipient
Dim strImp As String
On Local Error GoTo SendEmailToAssignedTo_Err
Me.Sendon = Now()
Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.CreateItem(olTaskItem)
myItem.Assign
Set myDelegate = myItem.Recipients.Add(Forms!issues.Form![Assigned To E-Mail])
myDelegate.Resolve
strImp = Forms!issues.Form.Priority
If myDelegate.Resolved Then
myItem.Subject = Forms!issues.Form.Title & " Item number - " & Me.Item_Number
myItem.DueDate = Forms!issues.Form.[Due Date]
myItem.StartDate = Forms!issues.Form.[Opened Date]
If strImp = "(1) high" Then
strImp = olImportanceHigh
End If
If strImp = "(2) normal" Then
strImp = olImportanceNormal
End If
If strImp = "(3) low" Then
strImp = olImportanceLow
End If
myItem.Importance = strImp
'myItem.BodyFormat = olFormatHTML
myItem.Body = Forms!issues.Form.Comment & vbCr & vbCr & "Please see attachements if present for detailed clarificaitons" & _
vbCr & Me.Attachment & _
vbCr & Me.Attachment4 & vbCr & vbCr & _
"Please, take appropriate actions, update mail-task assigned and Excel link below" & _
vbCr & Me.Attachment2
myItem.Display
SendEmailToAssignedTo_Err:
MsgBox ("Outlook is not running, Launch Outlook and try again")
Exit Sub
End If
End Sub
++++++++++++++++++++++++++++
and it works almost fine, except 2 things:
1. Everytime it gives the error message even if it sends out the task
2. when outputting the hyperlinks ( attachment2,attachment, attachment4 ) it shows the display name and at the beggining and at the end of the filepath it puts stupid pound signs. Now hyperlinks in task do not work as they are not recognized as hypelinks.
ex.
Please see attachements if present for detailed clarificaitons
My letter#\\TCOTENNTDFS1\SHARE\All TCO Groups\Agency Inspections\Action Tracker\LINKS\Citation\DES Citation of 3000608\Citation dated 30 Jun 08_R.pdf#
Inspection letter#\\TCOTENNTDFS1\SHARE\All TCO Groups\Agency Inspections\Action Tracker\LINKS\Citation\DES Citation of 3000608\Citation dated June 30 08.pdf#

 

[CyberLynx]

What is the Error you are getting? This would be helpfull and it may be the root of your second problem

.

 

[aleb]

Well the error message won't help a lot because it says "Outlook is not running try to launch outlook and send again" ( you can see this message in the code ). Obviously there is some error but the message itself is not quite explanatory. Now regarding the second problem. If I click on the form field and try to link the file it gives me .../links/nameoffile.ext due to the fact that dbase is located in the same folder. Unfortunately I can not send this filepath to other people because they have no clue where the linked documents reside. I do the following - I usually click on the file and select option: "Put filepath to clipboard" and only then paste it to the field. The display name and filepath become the same. As a result now when I send the message I have two looooooong file pathes links and non of them is working because of the # sign inserted by MS access.

 

[CyberLynx]

Ok...this would have been a lot easier to see if you code was formated within a code window using the [ code ] and [ /code ] tags, but in any case....

The reason why you are always getting the message Outlook is not running, Launch Outlook and try again is because the code is always allowed to do so. it is within the mainstream of the running procedure. It should be seperated like this:

Private Sub SendEmailToAssignedTo_Click()
   Dim myOlApp As New Outlook.Application
   Dim myItem As Outlook.TaskItem
   Dim myDelegate As Outlook.Recipient
   Dim strImp As String
   
   On Error GoTo SendEmailToAssignedTo_Err
   
   Me.Sendon = Now()
   
   Set myOlApp = CreateObject("Outlook.Application")
   Set myItem = myOlApp.CreateItem(olTaskItem)
   
   myItem.Assign
   
   Set myDelegate = myItem.Recipients.Add(Forms!issues.Form![Assigned To E-Mail])
   
   myDelegate.Resolve
   strImp = Forms!issues.Form.Priority
   
   If myDelegate.Resolved Then
      myItem.Subject = Forms!issues.Form.Title & " Item number - " & Me.Item_Number
      myItem.DueDate = Forms!issues.Form.[Due Date]
      myItem.StartDate = Forms!issues.Form.[Opened Date]
      If strImp = "(1) high" Then
         strImp = olImportanceHigh
      End If
      If strImp = "(2) normal" Then
         strImp = olImportanceNormal
      End If
      If strImp = "(3) low" Then
         strImp = olImportanceLow
      End If
      myItem.Importance = strImp
     'myItem.BodyFormat = olFormatHTML
      myItem.Body = Forms!issues.Form.Comment & vbCr & vbCr & "Please see attachements if present for detailed clarificaitons" & _
                    vbCr & Me.Attachment & _
                    vbCr & Me.Attachment4 & vbCr & vbCr & _
                    "Please, take appropriate actions, update mail-task assigned and Excel link below" & _
                    vbCr & Me.Attachment2
      myItem.Display
   End If

Exit_SendMailToAssignedTo:
   Exit Sub
   
SendEmailToAssignedTo_Err:
   MsgBox Err.Number * " -- " & Err.Description
   'MsgBox ("Outlook is not running, Launch Outlook and try again")
   Resume Exit_SendMailToAssignedTo
End Sub

Easier to read isn't it.

As for the second problem.....try this in the procedure:

Dim H1 as HyperLink, H2 As Hyperlink, H3 As Hyperlink

H1.Address = Me.Attachment
H2.Address = Me.Attachment2
H3.Address = Me.Attachment4
......................................
......................................
......................................
......................................
myItem.Body = Forms!issues.Form.Comment & vbCr & vbCr & "Please see attachements if present for detailed clarificaitons" & _
              vbCr & [B]H1[/B] & _
              vbCr & [B]H3[/B] & vbCr & vbCr & _
              "Please, take appropriate actions, update mail-task assigned and Excel link below" & _
              vbCr & [B]H2[/B]
......................................
......................................
......................................
......................................

.

 

[aleb]

Thank you for the solution of the first issue.
Unfortunately the solution for the second issue didn't work in any way. I have posted the dbase to see what is the problem. You migt be able to see what is going on.

 

[CyberLynx]

A thought for starters:

Although Microsoft Windows operating systems today allow for long paths and file names with spaces in them, Microsoft Outlook does not within Hyperlinks. Long paths and file names are Okay but for some reason, Outlook doesn't care for spaces within a Hyperlink string and therefore truncates the Hyperlink String at the first space it encounters. Obviously then, the Hyperlink fails.


In your code:

The Importance property for the Outlook Object is looking for an Integer value but you are passing it a String value. Since the priorities are listed in a table and presented through a ComboBox, providing the required Integer value can be easily done by passing Column 0 of a two Column ComboBox (0 column hidden). Column 0 would hold the Index (Autonumber) of the specific Priority string and Column 1 would hold the Priority string itself. This reduces code. You could however leave the code as it is except for where strImp is supplied to the Importance property:

myItem.Importance = strImp

and change it to:

myItem.Importance = CInt(strImp)

or you could just change the Declaration of strImp from a String Data Type to a Variant Data Type:

Dim strImp As Variant

I went by way of declaring strImp as Long Integer then changing the order of things in the Priorities Table and utilizing Column 0 of a two column ComboBox with Column(0) holding the Index of the Table rows, then subtracting the Index by one since the AutoNumber Index starts at 1. Less code and cleaner (in my opinion).


A Solution:

Because your Hyperlinked Paths contain spaces you need to pass it into Outlook message body as a tag by enclosing it with the Less Than (<) and Greater Than (>) tag symbols. Under normal circumstances this works but for some reason the hash or pound symbols (#) are also being added. Without completely dissecting everything, I'm not completely sure why this is happening even when HTML or RTF tags are used. There is however a work around to this. You can use the <File:yourHyperLinkString> tag. With this tag, spaces in the Hyperlink are accepted but you do see the tag within the hyperlink. This shouldn't really be an issue since the ultimate goal is to either view or save the attachment item behind the Hyperlink. To ensure the tags are not placed when a Form Control is Null, I used the IIf statement within the Message Body string construction as shown below.

myItem.Body = Forms!issues.Form.Comment & vbCr & vbCr & _
              "Please see attachments if present for detailed clarifications:" & _
              vbCr & IIf(IsNull(Me.Attachment) = False, "<File:" & Me.Attachment & _
              ">", "") & vbCr & IIf(IsNull(Me.Attachment4) = False, "File:" & _
              Me.Attachment4 & ">", "") & vbCr & vbCr & _
              "Please, take appropriate actions, update mail-task assigned and Excel link below:" & _
              vbCr & IIf(IsNull(Me.Attachment2) = False, "<File:" & Me.Attachment2 & ">", "")

I also noticed that the Hash (#) Marks were actually added into the record and were visible within the Form Control field. There were duplicate Paths within the table record field One without hash marks directly followed by the same path with Hash Marks. I'm not sure if this was deliberate or not.....so I removed the second path enclosed within the Hash Marks including the Hash Marks.

The application for some reason was adding this additional data to the field. Again, without dissecting everything, I'm not sure why this was doing this. I'm sure it has something to do with the Hyperlink Data Type....So I changed the Hyperlink Data Type for all the Attachment fields in both the Tables and Form Controls to a Text Data Type. So, in Tables, the fields Attachment, Attachment2, Attachment3, and Attachment4 now contain the Text Data Type and all the TextBox Controls within the Issues Form which hold the supposed Hyperlinks have their Hyperlink property set to No.

Attached to this post is your Sample DB with the modifications mentioned above. I hope this helps somewhat.

.

 

[aleb]

CyberLynx, unfortunately the option of changing the hyperlink field to a text field is not an option yet for me. People who use dbase want to be able, by clicking on the field, to open the file linked.
Now about the problem itself: I looked thoroughly before I posted the code here and found out that duplicated addresses are not a duplication at all it is just a "display name" and "file address". These are two properties of hyperlink address. Funny thing is that when I paste filepath in the hyperlink field the display name and filepath became the same. What happens next - when dbase creates the task THE DISPLAY NAME works as a hyperlink but the file path becomes unusable because of the hash signs. I tried to get only the display name of this field ( that would solve the problem completely ) but failed to get it in the code. I tried me.attachment.displayname me.attachment.displayname.value, tried to search help for something like"display name" - could not find anything.

 

[aleb]

Solution found

I knew that there should be some solution to my problem. Here it is:

Instead of putting the reference to the field like me.attachment I have inserted this:

HyperlinkPart(Forms!issues.Form.Attachment, acDisplayedValue)

And it worked !
Thank you very much.