Question Link to Record in Email

[matthewnsarah07]

I currently have a staff holiday database. When a member of staff request a holiday their manager receives an email where the holiday request number (an autonumber in the request table) is the subject. They type this in to their front end and retrieve the request.

Is there any way to provide a link in the email to the manager which would take them straight to the actual request number in question?

Any help is greatly appreciated

 

[orna]

I took code from
http://msdn.microsoft.com/en-us/library/aa163981(office.10).aspx#odc_offtips_topic2
and add to it a shell call with environment variable.

Private Sub Application_NewMail()
Dim olApp As Outlook.Application
Dim olNS As Outlook.NameSpace
Dim olFld As Outlook.MAPIFolder
Dim oMail As Outlook.MailItem
Dim objShell As Object
Dim objEnv
Dim stAppName As String

Set olApp = Outlook.Application
Set olNS = olApp.GetNamespace("MAPI")
Set olFld = olNS.GetDefaultFolder(olFolderInbox)
Set oMail = olFld.Items.GetFirst
If IsNumeric(oMail.Subject) Then
Set objShell = CreateObject("WScript.Shell")
Set objEnv = objShell.Environment("User")
objEnv("var1") = oMail.Subject
stAppName = "MSAccessPath YourApplicationPath"
Call Shell(stAppName, 1)
objEnv.Remove "var1"
Set objShell = Nothing
Set objEnv = Nothing
End If

Set oMail = Nothing
Set olFld = Nothing
Set olNS = Nothing
Set olApp = Nothing

End Sub


on the Access application in the start form add this code .

Private Sub Form_Open(Cancel As Integer)
Dim objShell As Object
Dim objEnv

Set objShell = CreateObject("WScript.Shell")
Set objEnv = objShell.Environment("User")
If objShell.ExpandEnvironmentStrings(objEnv("var1")) <> "" Then
DoCmd.OpenForm "NameOfForm", acNormal, , "[AutonumberField]=" & CLng(objShell.ExpandEnvironmentStrings(objEnv("var1")))
Else
DoCmd.OpenForm "NameOfForm"
End If
Set objShell = Nothing
Set objEnv = Nothing
End Sub