Email Item as Link from Access

[sierra7]

NeoPa
Neither you précised version nor the Microsoft reference http://support.microsoft.com/kb/209207 indicate how you can pass a form-name and an item ID into Access, other than firing-up a predefined macro.

Please could you elucidate futher?
S7

[NeoPa]

I'll try to clarify - using the linked article as a reference. It doesn't strictly tell any old database which form to open, but if the database is designed to open a specific form which can interpret the value passed then it can, nevertheless, cause a particular form or piece of code to be given control.

From the section titled Starting Access into a Database:, you will see an item #4 which explains how you pass a value to the database being opened. This database must be designed to open a particular form automatically. That is a prerequisite. How the data passed is formulated is up to the design of the database.

The next section, titled Designing the Database to Handle the Parameter:, illustrates how the database can be designed to determine the value passed and run normally if none is passed, but in a specific way if the value "Auto" is passed. Clearly this logic is designable, so the code could be written to handle all sorts of values including, but not limited to, the name of a form to open.

If more than one value is required then a string containing both, but separated by a character (EG comma (,)), could be processed in the code to separate the items into different variables for use in the code.

[TheSmileyCoder]

Ive tried more variations with the syntax, but simply can't get it to flow, using a simple hyperlink. Based on the research I've done around the internet, I coming to the conclusion that its not possible.


While NeoPa's suggesting would work, it doesn't really fit my needs. A user could get 10 emails asking him to look at 10 different reviews, and as I understand the solution proposed by NeoPa it would then involve my program creating 10 different cmd files and placing them on the server. Not in itself a problem but what if I have 5 users for each review? They can have their frontend in different locations or even have renamed it. Now part of my program allready records in the backend where the user has the frontend (Because that allows me to catch when some moroon user has used the distribution copy found on the network, instead of using his own copy of the frontend client), so I can handle that situation, but still, that might take us to 50 different commandfiles, for just 10 reviews. And in time the system's intention is to handle roughly 5-10.000 reviews, and I dont like the idea of leaving all those commandfiles out there.

Angle 2
Another angle I have taken is to try to create a shortcut file, and then email the shortcut to the user. No matter which syntax I try, its as if it doesn't read the command parameter, or doesn't open the database at all.

Angle 3
Is there any way of doing this by creating and installing a outlook addon? I really have no outlook experience, so I was hoping maybe some of you had some pointers in that area.

[sierra7]

Hi Smiley,
I have not read your last post properly nor tried NeoPa's suggestions yet (being the weekend i have to say hello to wifey now and then!) but i've got this test rig working.

Unzip it into c:\test. There are two files an Access file and a spreadsheet. In the Spreadsheet you can nominate a FormName, a FieldName and the field value (I've assumed it's the ID)

In the Access .mdb file there are two Forms called frmTable1 & frmTabl2 (based on Table1 and Table2). The key fields are Table1_ID and Table2_ID. There are only 3 records in each table; ID = 1,2,or 3.

The Access app will open showing the form and record defined in the xls file.

Up to this point it is an acedemic exercise; I'm nor sure it meets your requirements, Smiley. I certainly want to try NeoPa's method again. It's some years since I tried this previously and I'm now starting to get the hang of this programming lark.

S7

[TheSmileyCoder]

I appreciate the input, but if I wanted to "simply" take a user to a specific point I could store the information in the database. I want to be able to send the user 1 email for each review he has to follow up on. Each email must have its own link/item/somethingy which the user can click to open to that specific review.

[NeoPa]

I believe my suggestion gives you the wherewithall to handle that Smiley. My answer was directed only at the question - particularly that part that wasn't covered by the article. If your requirements are somewhat different from the initial question that isn't a problem, but I believe the suggestions I gave allow you to tailor the solution to what you need. My solution doesn't rely on using CMD files. In many cases they won't even be necessary (I suspect yours is one such). Without a clearer understanding of what you want, and more specifically where you see a gap in the solution suggested, I can't direct my explanation to fit the bit you're not seeing.

[TheSmileyCoder]

@ Adezii post 11, sorry for not coming back to you on that one. When manually adding the link via outlook's "Insert Hyperlink" the / was always being converted (silently grr) to \.

@ NeoPa. I have reread your posts as well as the linked article. I still don't see how to include the ID of an item in the email.

I can open a database using a hyperlink. Or I can open a command file that will open the database. Im sure I could setup the command file to pass an argument. But I dont see how to combine this to include the argument in the email, without making hyperlinks to several different reviews.

[sierra7]

Hi
This is probably acedemic now but has been an interesting learning experience!

Yes, thanks to NepoPa, I can now pass arguments via a command line to access.

rem  "C:\Program Files (x86)\Microsoft Office\Office14\MsAccess.exe" "c:\Test\Open_A_Form.mdb"  ;frmTable2, Table2_ID,3

start %AccessPath% %TestDatabase%  ;frmTable2, Table2_ID,3

The first line (now rem'd) is the basic code required, but is dependednt on the user's version of Access and where he has placed the front end and whether or not he has renamed it. The second line uses system environment variables to overcome this. It will require a visit to each user's computer to set-up but at least the code can be consistant thereafter.

The code to interpret the parameters is in the startup form frmStart. It's a developmentr of Neopa's code; if there are no parameters then frmDefault is displayed;-

Private Sub Form_Open(Cancel As Integer)

Dim stFormName As String    'name of Form to be opened
Dim stFieldName As String   'name of key field in form
Dim iItemID As Long         'Item ID to be displayed
Dim iPos As Integer
Dim strParameters As String

If Command = "" Then
    'no parameters so open default form
    DoCmd.OpenForm "frmDefault"
Else
    'save Command as string
    strParameters = Command
    
    'find position of first comma - hence stFormName
    iPos = InStr(1, strParameters, ",")
    stFormName = Left(strParameters, iPos - 1)
        
    'truncate string
    strParameters = Right(strParameters, Len(strParameters) - iPos)
    'find position of second comma - hence stFieldName
    iPos = InStr(1, strParameters, ",")
    stFieldName = Left(strParameters, iPos - 1)
    
    'truncate string for iItemID
    strParameters = Right(strParameters, Len(strParameters) - iPos)
    iItemID = Val(strParameters)
    
    'open the form at the required item
    DoCmd.OpenForm stFormName, , , "" & stFieldName & "=" & iItemID & ""
End If

'close the start-up form
DoCmd.Close acForm, "frmStart"
End Sub

This is what I could not get working because I did not appreciate the 'Command' variable or function or whatever, should be treated like OpenArgs. Although not elegant, the above code does the job.

I'll see if I can attach the test database again.
S7

[NeoPa]

Smiley:
I can open a database using a hyperlink. Or I can open a command file that will open the database. Im sure I could setup the command file to pass an argument. But I dont see how to combine this to include the argument in the email, without making hyperlinks to several different reviews.

S7's post (First chunk of code, line #3) illustrates the format of a command that will pass this information into Access. This looks like it's been designed for a CMD file, but could be used as-is in a hyperlink, or even without the Start part (as that is generally only helpful/necessary when running from a CMD file). S7's code seems to handle processing this information within the code of the automatically opened form of the database. I'd just add here that multiple values for ID can also easily be passed and processed in the same way as in S7's code (although using the Split() function makes more sense, especially as the numbers increase). The only question left it seems, is how difficult it is to formulate the hyperlink in the original email. This isn't something I've done much of yet, but if you're still in need of direction (I appreciate S7's post came in after your question so you may well have no questions left - but JIC you do) then I'll look into it further and see if I can demonstrate a way around that.

[TheSmileyCoder]

I can get the a .cmd file to work, and I can get a shortcut to work, but I have tried probably 20 or 30 variations of writing it as a hyperlink, with and without quotes, with %20 instead of spaces and so on. Maybe its possible, but I am just not able to hit the correct combination and its driving me insane!

EDIT:I can send a shortcut or .cmd in an email, but outlook requires the user to save it to his disk first, before opening it, which is just annoying.

[NeoPa]

If you give me (post) the details of what your current code is, and where all the files are relative to your network, and which values you want passed into the database, I'll have a look at what I can sort out for you. I assume you're using Outlook for both sending and receiving the emails?

I'm on Skype most afternoons too, and would happily spend some time working through it with you. I have two Skype accounts for you so I'm sure one of them at least is still active.

[TheSmileyCoder]

I am using the code provided by Adezii in post #2.

Further Details are:
Access location:
C:\Program Files\Microsoft Office\Office11 \MSAccess.exe

Test database location (Placed in root without spaces in path to simplify testing):
C:\TestMail.mdb

For testing the database has a startup form, which contains a OnOpen event with 1 line of code:
Msgbox "Testing Command [" & Command & "]"

When testing with a shortcut file, or a command file it works fine. But doing it by hyperlink does not work.

[ADezii]

@TheSmileyCoder :
You may also wish to look into the possibility of replacing Code Line #3 in the following Code

With oMail 
 .To = strTO 
 .Body = strMessageBody 
 .Subject = strSubject 
   .Display 
     '.Send (Should you wish to immediately Send the E-Mail without displaying Outlook) 
End With

with

.HTMLBody = strMessageBody

I haven't experimented with it much, but I'm pretty sure that you can use Embeded HTML Tags within this context.

[Mariostg]

@ADezii
Yes, that is possible, I have one that goes like so"

    strBody = "<table style='font-family:courier, monospace;font-size:12px;border-collapse:collapse;'>"
    strBody = strBody & "<tr><td>Spent         </td><td align='right'>$" & FormatNumber(Spent, 2) & "</tr>"
    strBody = strBody & "<tr><td>Balance       </td><td align='right'>$" & FormatNumber(Bal, 2) & "</tr>"
    strBody = strBody & "<tr><td>Working Plan  </td><td align='right'>$" & FormatNumber(frm.[Total Cur Yr], 2) & "</tr>"
    strBody = strBody & "<tr><td>Forecast      </td><td align='right'>$" & FormatNumber(frm.New_Forecast, 2) & "</tr></table><br>"
    strBody = strBody & "<div style='font-family:courier, monospace;font-size:12px;'>"
    strBody = strBody & frm.Description & "<br><br>"
    strBody = strBody & frm.Comments & "<br></div>"

[NeoPa]

Smiley,

I have knocked up a testing rig now to handle this (included below code for form which has one Command Button on it). What I'm struggling with is what I thought you people already had working - IE. the format of the hyperlink. I know what a single command should look like for you, but I'm struggling to get that into a hyperlink (because I've never tried to do this before). I thought you already had that bit understood.

Anyway, the code below works fundamentally, but doesn't allow passing of any parameters within the link as yet. When I've sussed that out I'll post again with code tailored to your specific situation.

Option Compare Database
Option Explicit

Private Sub cmdSend_Click()
    Dim strHyperlink As String

    strHyperlink = "<FILE://ServerName/ShareName/" & _
                   "User.Name/Access/Hyperlink Test.Mdb ;Try this!>"
    With CreateObject(Class:="Outlook.Application")
        With .CreateItem(olMailItem)
            Call .Display
            .Subject = "Test Project for Inserting Hyperlink in Outlook " & _
                       "E-Mail Pointing to Front End Database"
            .To = "BartSimpson@aol.com"
            .Body = "Click on Hyperlink to Open Front End Database." & _
                    vbCrLf & vbCrLf & strHyperlink
            Call .Send
        End With
    End With
End Sub