Url/ Email Table Filed

[ckennerdale]

Hi I hope this is the right forum.

I'm using Access 97

I would like a field in my table which contains email addresses.

Its datatype is set as 'hyperlink'. However when I click it it opens up a browser and looks for

http://mail@mail.com

If I type in 'mailto:' at tye beginning of the email address then all is fine.

Ideally I do not want the users of the database to have to do this, is there a way of appending 'mailto:' to an email address invisibly in Access or is there another way round my problem?

Thanks

Caspar Kennerdale

 

[SeeThru]

Set it to a normal field, and use OnClick code to generate an email using the contents of the field as the 'To'

You can create links to Outlook that will give you much more control over preset messages, repyto, to, copy, subject, etc.

You could also use the same OnClick to log the fact that you had sent the email.

 

[ckennerdale]

do you have an example of the onclick code, I'm new to VB

Thanks

Caspar Kennerdale

 

[SeeThru]

You need to link to Outlook
In any module Tools>Reference and find the latest version of outlook.

In a module, add:

Function SendOutlookMessage(ByVal Recipients As String, ByVal Subject As String, ByVal Body As String, ByVal DisplayMsg As Boolean, Optional ByVal CopyRecipients As String, Optional ByVal BlindCopyRecipients As String, Optional ByVal Importance As Integer = 2, Optional ByVal AttatchmentPath)
'Function to create and send an outlook message with more control than sendobject
'separate multiple recipients or CC, or BCC with comma
'importance - 1=low, 2=normal, 3=high

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.recipient
Dim objOutlookAttach As Outlook.Attachment
Dim txtRecipient, txtTempAttatchment1, txtTempAttatchment2 As String
Dim x As Integer


' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application&quot

' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg
' Add the To recipient(s) to the message.
Do While InStr(1, Recipients, &quot;,&quot;, vbTextCompare) <> 0 'checks for multiple recipients and adds each
txtRecipient = Left(Recipients, InStr(1, Recipients, &quot;,&quot;, vbTextCompare) - 1)
Recipients = Trim(Mid(Recipients, Len(txtRecipient) + 2, Len(Recipients)))
Set objOutlookRecip = .Recipients.Add(txtRecipient)
objOutlookRecip.Type = olTo
Loop

Set objOutlookRecip = .Recipients.Add(Trim(Recipients))
objOutlookRecip.Type = olTo

' Add the CC recipient(s) to the message if existing
If CopyRecipients <> &quot;&quot; Then
Set objOutlookRecip = .Recipients.Add(CopyRecipients)
objOutlookRecip.Type = olCC
End If

' Add the BCC recipient(s) to the message.
If BlindCopyRecipients <> &quot;&quot; Then
Set objOutlookRecip = .Recipients.Add(BlindCopyRecipients)
objOutlookRecip.Type = olBCC
End If

' Set the Subject, Body, and Importance of the message.
.Subject = Subject
.Body = Body & vbCrLf & vbCrLf
Select Case Importance
Case 1
.Importance = olImportanceLow
Case 2
.Importance = olImportanceNormal
Case 3
.Importance = olImportanceHigh
Case Else
.Importance = olImportanceNormal
End Select

' Add attachments to the message.
If Not IsMissing(AttatchmentPath) Then
txtTempAttatchment1 = AttatchmentPath
If CharCount((txtTempAttatchment1), &quot;;&quot = 0 Then
Set objOutlookAttach = .Attachments.Add(txtTempAttatchment1)
Else 'if there are multiple attatchments
txtTempAttatchment1 = txtTempAttatchment1 & &quot;;&quot; 'adds a closing ; to ensure loop always works
For x = 1 To CharCount((txtTempAttatchment1), &quot;;&quot
txtTempAttatchment2 = Left(txtTempAttatchment1, InStr(1, txtTempAttatchment1, &quot;;&quot - 1)
txtTempAttatchment1 = Mid(txtTempAttatchment1, InStr(1, txtTempAttatchment1, &quot;;&quot + 1, Len(txtTempAttatchment1))
Set objOutlookAttach = .Attachments.Add(txtTempAttatchment2)
Next x
End If
End If

' Resolve each Recipient's name. Temporarily disabled
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
Next

' Should we display the message before sending?
If DisplayMsg Then
.Display
Else
.Save
.Send
End If
End With
Set objOutlook = Nothing

End Function


This could be enhanced a bit for sending to multiple recipients, etc, but will work.


Then, in your button add:

SendOutlookMessage Recipients, Subject, Body, DisplayMsg, CopyRecipients, BlindCopyRecipients, Importance, AttatchmentPath

Obviously, most of these are strings, so you have to either pass string variables as arguments, or input them as strings directly into the code.

HTH

 

[ckennerdale]

thanks

Caspar Kennerdale