Hyperlink email to database (1 Viewer)

chizzy42

Registered User.
Local time
Today, 19:46
Joined
Sep 28, 2014
Messages
115
Hi, i have some code I'm using to send an email to a user indicating that a database needs their input . What I've been trying to do as an extra is have a link on the sent email that when clicked opens the form that needs viewed. I have used the code below to open the database form with the correct data on the database
Code:
Private Sub Command3_Click()
Const cstrForm As String = "frmDevReq" ' <-- change this
    DoCmd.OpenForm cstrForm, WhereCondition:="[DeviationRequestNumber]=" & 3
End Sub

what i cant seem to figure out what i need to add to the following code below that would allow me to send a link to open the database on a location on the server...or even for example on the c drive path...c:\dev\devdatabase\formname.with condition

Code:
Dim appOutLook As Object
Dim MailOutLook As Object

strDevNo = Forms!frmdevreq!Text112
strDevDesc = Forms!frmdevreq![Deviation Description]
strDevOwn = Forms!frmdevreq!Text116
strDevapp1 = Forms!frmdevreq!Text114
strDevapp2 = Forms!frmdevreq!Text120
strDevapp3 = Forms!frmdevreq!Text124
strDevapp4 = Forms!frmdevreq!Text128
strDevapp5 = Forms!frmdevreq!Text132

    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.CreateItem(0)

     With MailOutLook

     .To = strDevapp1 & ";" & strDevapp2 & ";" & strDevapp3 & ";" & strDevapp4 & ";" & strDevapp1

     .Subject = "Deviation raised " & strDevNo

    .Body = "Hello " & _
    vbCrLf & _
    "A Deviation has been raised for your attention, number : " & strDevNo & _
    vbCrLf & _
    "Deviation was raised by " & strDevOwn & _
    vbCrLf & _
    " Deviation description:: " & strDevDesc

    .Send

    End With

    Set appOutLook = Nothing
    Set MailOutLook = Nothing
    notified = True

Any help in getting this started would be much appreciated, hope everyone is keeping well

ian
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:46
Joined
Oct 29, 2018
Messages
21,358
Hi. You might also consider tackling this form the Outlook side.
 

chizzy42

Registered User.
Local time
Today, 19:46
Joined
Sep 28, 2014
Messages
115
Hi. You might also consider tackling this form the outlook side.
Hi dbguy, could you explain what you mean by the outlook side please. What im trying to do is automatically open the database from the link on the email (which i can do with a button on a form) instead of the user opening the database and searching for the id number after receiving the email.

thanks

ian
 

chizzy42

Registered User.
Local time
Today, 19:46
Joined
Sep 28, 2014
Messages
115
Thanks for the info minty and gasman..kinda got it going now. I can open the database just need to figure out the code to open the specified id number
Code:
Dim appOutlook As New Outlook.Application
Dim objEmail As Outlook.MailItem
Set objEmail = appOutlook.CreateItem(olMailItem)
With objEmail

.To = "ian.xxxxxx@xxx.com"

.Subject = "stuff"

.BodyFormat = olFormatHTML

.HTMLBody = "Dear someone, <br><br>" & _
        "Please see something etc that requires your review.<br><br>" & _
            "file:\\c:\dev\deviationdatabase.accdb"

.Send

End With
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 12:46
Joined
Oct 29, 2018
Messages
21,358
Hi dbguy, could you explain what you mean by the outlook side please. What im trying to do is automatically open the database from the link on the email (which i can do with a button on a form) instead of the user opening the database and searching for the id number after receiving the email.

thanks

ian
Hi Ian. What I'm trying to say is you can write code in Access to make it do whatever. Now, you want Outlook to do something. So, all I'm suggesting is maybe you can also write code in Outlook to make it do something.

A hyperlink can only do so much. Rather than create a hyperlink to the database file, maybe you can make it a hyperlink to a batch or script file to open the db to a specified record. That's the idea anyway, I don't recall if anyone was able to make that work though.
 

chizzy42

Registered User.
Local time
Today, 19:46
Joined
Sep 28, 2014
Messages
115
Minty/gasman , those links has kinda got me there thanks if i drop the following line into windows explorer

Code:
"C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE C:\dev\deviationdatabase.accdb /cmd frmDevReq

with the below sub in the onload of the switchboard ...the resulting form and data appear that i wanted to work


Code:
Public Sub CheckCommandLine()
    ' Check the value returned by Command function and display
    ' the appropriate form.
    If Command = "frmDevReq" Then
        Const cstrForm As String = "frmDevReq" '
    DoCmd.OpenForm cstrForm, WhereCondition:="[DeviationRequestNumber]=" & 3
        Exit Sub
    End If
End Sub

my problem i have now is that i cant get the line

"C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE C:\dev\deviationdatabase.accdb /cmd frmDevReq

to open in windows explorer when i send the line as a hyperlink from outlook

Code:
Dim appOutlook As New Outlook.Application
Dim objEmail As Outlook.MailItem
Set objEmail = appOutlook.CreateItem(olMailItem)
With objEmail

.To = "ian.xxxx@xxxx.com"

.Subject = "stuff"

.BodyFormat = olFormatHTML

.HTMLBody = "Dear someone, <br><br>" & _
        "Please see something etc that requires your review.<br><br>" & _
        "file:///C:\Program%20Files%20(x86)\Microsoft%20Office\root\Office16\MSACCESS.EXE%20C:\dev\deviationdatabase.accdb%20/cmd%20frmDevReq"
             
.Send

End With

ive tried using file\\ and file/// still doesnt open...but using it as a line of text into the windows explorer it works, has anyone any idea please how i format this line to open the link?

thanks

ian
 

chizzy42

Registered User.
Local time
Today, 19:46
Joined
Sep 28, 2014
Messages
115
Hi Gasman thanks for your input again , I've made a bit of progress by using the following i get two separate links, the first opens access and the second opens the database ....but issue is the /cmd is getting ignored as well as the form name. looks like the link doesn't like spaces or filling with %20 or embedding the string in <>.

Code:
.HTMLBody = "Dear someone, <br><br>" & _
        "Please see something etc that requires your review.<br><br>" & _
        "file:///C:\Program%20Files%20(x86)\Microsoft%20Office\root\Office16\MSACCESS.EXE" & _
       vbNewLine & _
        "file:///C:\dev\deviationdatabase.accdb /cmd frmDevReq"

best regards

Ian
 

Gasman

Enthusiastic Amateur
Local time
Today, 19:46
Joined
Sep 21, 2011
Messages
14,046
I do not believe HTML will recognise vbNewLine?
I'd make it all one link, no breaks, otherwise see that link, but that means you still need to be able to edit the registry, which again is a security risk.

Perhaps approach it another way.?
Send the email and then set some process so when that person logs on, it automatically takes them to wherever they need to be.?
Have a table with Required form and ID. When you send the email, create a record in that table. When the user logs in, check that table. If a record exists open the required form to the ID, and delete the record or mark it processed in that form.?

HTH
 

chizzy42

Registered User.
Local time
Today, 19:46
Joined
Sep 28, 2014
Messages
115
HI, you're right about the vbNewLine, it doesn't go on a new line , both links are on the same line
 

Extra_Cover

Registered User.
Local time
Today, 19:46
Joined
Oct 21, 2008
Messages
71
Try a href

Something like :

Code:
 Dim myfilelink As String
    
    myfilelink = "<a href='" & "C:\dev\deviationdatabase.accdb /cmd frmDevReq" & "'>Click Here To Open</a>."
    .HTMLBody = "Dear Someone <br>" & "Somthing that requires your review " & myfilelink

Be careful with single and double quotes.
 

chizzy42

Registered User.
Local time
Today, 19:46
Joined
Sep 28, 2014
Messages
115
Thanks for the reply extra cover...again this partially works if i remove the /cmd frmDevReq part it will open the required database, if i dont it gives the error cant find C:\dev\deviationdatabase.accdb /cmd frmDevReq


Code:
Dim myfilelink As String
    
    myfilelink = "<a href='" & "C:\dev\deviationdatabase.accdb" & "'>Click Here To Open</a>."

the issue seems to be having the spaces after the .accdb , i tried the %20 to fill the spaces but still no joy..but thanks again

ian
 

Gasman

Enthusiastic Amateur
Local time
Today, 19:46
Joined
Sep 21, 2011
Messages
14,046
Ok, think outside the box then.
Create a batch file that sets environment variables for frm2Open and RecID
Then when opening the DB, have a method of checking those variables?, if set open required form to required ID and clear the variables. If not set open as normal?

Even try the whole string in a batch/cmd file?
 

chizzy42

Registered User.
Local time
Today, 19:46
Joined
Sep 28, 2014
Messages
115
Hi Gasman thought that was another way to look at it so tried a batchfile with the whole string that i know if i drop in window explorer opens the form in the database and the result i thought strange. I made the batchfile below to open the form
Code:
start "" "C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE" "C:\dev\deviationdatabase.accdb /cmd frmDevReq"

and when ran gave the error, cant find the database C:\dev\deviationdatabase.accdb /cmd frmDevReq.mdb

This was an old database that was originally .mdb, but converted to .accdb , i cant see why it would look for a form adding .mdb after it?? when its just a form name and i tried adding .accdb after form name but still no coconuts....might have to rethink this
 

Gasman

Enthusiastic Amateur
Local time
Today, 19:46
Joined
Sep 21, 2011
Messages
14,046
OK, I have no clue as to what is going on here.
I have this code called by autoexec macro
Code:
Public Function CheckCommandLine()
    ' Check the value returned by Command function and display
    ' the appropriate form.
    MsgBox "Command is " & Command
    Stop
    If Command = "Links" Then
        DoCmd.OpenForm "frmLinks"
    ElseIf Command = "Crew" Then
        DoCmd.OpenForm "frmCrew"
    Else
        MsgBox "Autoexec did not work"
        Exit Function
    End If
End Function
which displays 'Command is Links' in the message box, but ignores the If statement and I get the 'not working ' message.?

calling it with
Code:
"c:\users\Paul\Documents\bibbys.mdb" /cmd "Links"
as a command file.?
 

Users who are viewing this thread

Top Bottom