By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,506 Members | 2,252 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,506 IT Pros & Developers. It's quick & easy.

Email Item as Link from Access

TheSmileyCoder
Expert Mod 100+
P: 2,321
Assumptions:
I know how to create email from within Access, using outlook automation.
User has the access frontend installed
Office 2003

Is it possible to create a hyperlink in a email that will accomplish the following, when receiver clicks it:?

Open the access front-end on the users PC.
Open relevant form
Goto relevant Item


If its not possible in the way I have described it, I have several modifications that could be acceptable, for example, it would be ok to require that the user has the frontend open when clicking the link. It would also be ok if I had to install a add-on to the users outlook.
Jan 19 '12 #1
Share this Question
Share on Google+
54 Replies


ADezii
Expert 5K+
P: 8,599
I didn't have much time to look into this for you, but I have had limited success Inserting a Pseudo Hyperlink in an Outlook E-Mail pointing to an Access front End. It is at least a start.
Expand|Select|Wrap|Line Numbers
  1. 'Provides the Send Mail automation. Send an E-Mail
  2. 'and Attachment from Access via Outlook
  3. Dim oLook As Object
  4. Dim oMail As Object
  5. Dim olns As Outlook.NameSpace
  6. Dim strTO As String
  7. Dim strMessageBody As String
  8. Dim strPathToFrontEnd As String
  9. Dim strSubject As String
  10.  
  11. Set oLook = CreateObject("Outlook.Application")
  12. Set olns = oLook.GetNamespace("MAPI")
  13. Set oMail = oLook.CreateItem(0)
  14.  
  15. '******************************* USER DEFINED SECTION ********************************
  16. strTO = "BartSimpson@aol.com"                                                       '*
  17. strPathToFrontEnd = "<file://C:\Test\FE.mdb>"                                       '*
  18. strMessageBody = "Click on Hyperlink to Open Front End Database" & vbCrLf & _
  19.                   vbCrLf & strPathToFrontEnd                                        '*
  20. strSubject = "Test Project for Inserting Hyperlink in Outlook E-Mail Pointing " & _
  21.              "to Front Ebd Database"                                                '*
  22. '*************************************************************************************
  23.  
  24. With oMail
  25.  .To = strTO
  26.  .Body = strMessageBody
  27.  .Subject = strSubject
  28.    .Display
  29.      '.Send (Should you wish to immediately Send the E-Mail without displaying Outlook)
  30. End With
  31.  
  32. Set oMail = Nothing
  33. Set oLook = Nothing
Jan 19 '12 #2

100+
P: 332
I don't know about a hyperlink in an email, but in my case, say I have a new person that needs a database interface. I attach in the email a text file that all the user has to do is save the file on the desktop and change the txt extension to bat. That is because out implementation of Outlook does not allow sending bat files.

This is the content of the bat file:
Expand|Select|Wrap|Line Numbers
  1. @echo off
  2. set destDir="C:\"
  3. set srcDir="\\Your own source folder full path\"
  4. set srcFile="BFT_FE.mdb"
  5. set destFile="BFT_FE.mdb"
  6. echo %destDir%
  7.  
  8. if exist %destDir%%destFile% attrib -h %destDir%%destFile%
  9. copy /Y /B %srcDir%%srcFile% %destDir%%destFile% /V
  10.  
  11. attrib +h %destDir%%destFile%
  12.  
  13. start /B "C:\Program Files\microsoft office\OFFICE11\MSACCESS.EXE" %destDir%%destFile%
This is waht is used to start the Front End. When ever this is double clicked, a new front end is copied and opened. It makes release of new front ends a snap.
Jan 19 '12 #3

ADezii
Expert 5K+
P: 8,599
@Mariostg:
Nice approach, why the /B Command Line Argument on the Copy Operation (Line #9), since it is not a Binary File?
Jan 19 '12 #4

100+
P: 332
*ADezii*
Well, I am more of a linux guy when comes time to command line stuff :), but as far as I know, an mdb file is a binary file.

In any case, I just tried without the /B switch (Line 9) and everything works fine. i.e. the front ends gets copied over and opens fine.
Jan 19 '12 #5

NeoPa
Expert Mod 15k+
P: 31,186
According to the quick help :
Expand|Select|Wrap|Line Numbers
  1. /B   Start application without creating a new window.
  2.      The application has ^C handling ignored.
  3.      Unless the application enables ^C processing, ^Break is the only way to interrupt the application.
An alternative for line #13 might be :
Expand|Select|Wrap|Line Numbers
  1. Start /D%destDir% "%ProgramFiles%\Microsoft Office\OFFICE11\MSACCESS.EXE" %destFile%
Smiley:
Is it possible to create a hyperlink in an email that will accomplish the following, when receiver clicks it?
I believe it is, as long as the hyperlink can point to a shortcut or CMD file on the server. Invoking a Database From the Command Line gives examples of how a database can allow itself to be controlled from a command-line parameter. Obviously, such a command-line could simply be included in a shortcut if that were all that was involved.
Jan 19 '12 #6

100+
P: 332
@NeoPa
Yes, that /B swith is for the start function, not the copy.

In fact I have another one that simply goes like so, no start command:
Expand|Select|Wrap|Line Numbers
  1. copy /Y /B \\Img-lsl-v001860\dglepm.dv$\DLEPS\DLEPS_3\Shared_Working_Documents\SWEET_FE.mdb "C:\Documents and Settings\"%USERNAME%\Desktop\SWEET_FE.mdb /V
  2. "C:\Program Files\microsoft office\OFFICE11\MSACCESS.EXE" "C:\Documents and Settings\%USERNAME%\Desktop\Sweet_FE.mdb" /wrkgrp "\\Img-lsl-v001860\dglepm.dv$\DLEPS\DLEPS_3\Shared_Working_Documents\Sweet.mdw"
  3.  
The one above has a workgroup specified.
The first example I submitted was created after that one. And I think I had issues when not using start at the time.
In my environment, shortcuts, mdb files etc will not go through Outlook and I got tired of sending emails to advise people of new front end release then receive back a whack of out of office reply.

Anyway, you get the idea.
Jan 19 '12 #7

NeoPa
Expert Mod 15k+
P: 31,186
Without wishing to divert from Smiley's question Mario, did you try sending a link to an internal-network based shortcut instead of the the text of the command file itself? I would expect that could work as well for you, and avoid the manual part for the user.
Jan 19 '12 #8

100+
P: 332
NeoPa,
Because you are asking, I know you will not tell me to start a new thread :-)

Even an html link will not go through. Whether it is sent internally or not. The file get renamed as soon as it is inserted in the email. Not even sent yet... I tried to send it to me anyway, then the anti-virus pops up.

But from a user point of view, it is not too bad. All I do is send it as a text file (.txt) then they rename it bat (.bat). Then they acknowledge the change of extension, end of story. I do not send the commands as is in the body of the email if that is what you think. I think this is what you might think... That is a lot of thinking.
Jan 19 '12 #9

TheSmileyCoder
Expert Mod 100+
P: 2,321
Thanks for all your suggestions.

I have tried it, and I am able to create a hyperlink that will cause the database to open. However I can't seem to pass the argument that I would like. For now I have tried with a simple procedure called gotorid, which for now doesn't take any arguments to keep it simple. At some point if succesfull, I would like it to take the ID of the review to open.


I have tried:
Expand|Select|Wrap|Line Numbers
  1. file:///C:\Documents and Settings\aec.OSS.000\Desktop\Test\TestMail.mdb ;gotorid
  2.  
  3. file:///C:\Documents and Settings\aec.OSS.000\Desktop\Test\TestMail.mdb \x gotorid
  4.  
  5. file:///C:\Documents and Settings\aec.OSS.000\Desktop\Test\TestMail.mdb \x ; gotorid
  6.  
  7. "file:///C:\Documents and Settings\aec.OSS.000\Desktop\Test\TestMail.mdb" ;gotorid
But it keeps saying "Cannot open the specified file"
Jan 19 '12 #10

ADezii
Expert 5K+
P: 8,599
@TheSmileyCoder:
Your Syntax is incorrect,
Expand|Select|Wrap|Line Numbers
  1. /x MacroName
Jan 19 '12 #11

NeoPa
Expert Mod 15k+
P: 31,186
@Smiley.
Line #7 seems closest to what I'd expect - although three slashes after FILE: surprises me. I'd expect two. Anyway, try the quotes around the filename part only. It would need these I think, due to the spaces in Documents and Settings. Alternatively, try replacing those spaces with %32, as you sometimes see in links to items which have embedded spaces.

@Mario.
I'm running tests to see what happens when you try to include a link to the server file (in UNC notation) in the body of the email. I managed to send an email with the link from GMail, but how Outlook handles it is still unclear. I'll update later when I know more.
Jan 19 '12 #12

NeoPa
Expert Mod 15k+
P: 31,186
I was able to send an email from Outlook (manually mind) with a hyperlink value of (something equivalent to) :
Expand|Select|Wrap|Line Numbers
  1. file://MySvr02/Home/MyName/Copy%20of%20Test.CMD
NB. Only two slashes (/) after file: - and the space is %20 not %32. It seems it uses Hex rather than decimal values for the codes.

When it arrived I was able to follow the link (to "\\MySvr02\Home\MyName\Copy of Test.CMD") but I was prompted to confirm that I was happy to allow it to run. The CMD file ran fine.
Jan 20 '12 #13

Expert 100+
P: 446
Hi
Smiley's original requirement was to
Open the access front-end on the users PC.
Open relevant form
Goto relevant Item
I don't believe this can be done with either a Hyperlink or a command line argument. You can open the database and display the default form,ok if that's the form you want to display, but you still cannot go to a specific record.

One dodge I use is to to have my default form hidden (actually it has no features and matches the background 'cos it running a timer to fetch the prefered language from the user's login to then setup the correct Menu Bars /Ribbon)

Instead of doing this it could run an import routine to bring two fields (presumably a form-name and an item-number) from the email attachment into a local table, whereupon it could open up the said form and item.

A bit fiddly but not too difficult and a bit dependant on the email attachment. The simplest way would be to transfer the command line as you have been discussing but then a separate 'datapackage' in either a .xls or .mdb format. You could then use either DoCmd.TransferSpreadsheet or TransDatabase. However, if the whole thing was to be included in a BAT file then you would have to do a sequential read treating it as a text file, then sort out the wheat from the chaff.

Another fiddly bit would be to stop the application from displaying the form/item combination, when in normal use.

Is there any mileage in persuing this rout?
S7
Jan 20 '12 #14

NeoPa
Expert Mod 15k+
P: 31,186
S7:
I don't believe this can be done with either a Hyperlink or a command line argument. You can open the database and display the default form,ok if that's the form you want to display, but you still cannot go to a specific record.
If you check out the article linked in post #6 (Invoking a Database From the Command Line), you'll find that such control is, indeed, possible S7. Otherwise a CMD (or BAT for the dinosaurs) file could be used to do some preparation first, but frankly, I see no obvious need for that as an Access project could do anything a CMD could do.
Jan 20 '12 #15

Expert 100+
P: 446
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
Jan 20 '12 #16

NeoPa
Expert Mod 15k+
P: 31,186
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.
Jan 20 '12 #17

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Jan 22 '12 #18

Expert 100+
P: 446
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
Attached Files
File Type: zip Open_A_Form.zip (24.8 KB, 178 views)
Jan 22 '12 #19

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Jan 22 '12 #20

NeoPa
Expert Mod 15k+
P: 31,186
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.
Jan 23 '12 #21

TheSmileyCoder
Expert Mod 100+
P: 2,321
@ 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.
Jan 23 '12 #22

Expert 100+
P: 446
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.
Expand|Select|Wrap|Line Numbers
  1. rem  "C:\Program Files (x86)\Microsoft Office\Office14\MsAccess.exe" "c:\Test\Open_A_Form.mdb"  ;frmTable2, Table2_ID,3
  2.  
  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;-
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2.  
  3. Dim stFormName As String    'name of Form to be opened
  4. Dim stFieldName As String   'name of key field in form
  5. Dim iItemID As Long         'Item ID to be displayed
  6. Dim iPos As Integer
  7. Dim strParameters As String
  8.  
  9. If Command = "" Then
  10.     'no parameters so open default form
  11.     DoCmd.OpenForm "frmDefault"
  12. Else
  13.     'save Command as string
  14.     strParameters = Command
  15.  
  16.     'find position of first comma - hence stFormName
  17.     iPos = InStr(1, strParameters, ",")
  18.     stFormName = Left(strParameters, iPos - 1)
  19.  
  20.     'truncate string
  21.     strParameters = Right(strParameters, Len(strParameters) - iPos)
  22.     'find position of second comma - hence stFieldName
  23.     iPos = InStr(1, strParameters, ",")
  24.     stFieldName = Left(strParameters, iPos - 1)
  25.  
  26.     'truncate string for iItemID
  27.     strParameters = Right(strParameters, Len(strParameters) - iPos)
  28.     iItemID = Val(strParameters)
  29.  
  30.     'open the form at the required item
  31.     DoCmd.OpenForm stFormName, , , "" & stFieldName & "=" & iItemID & ""
  32. End If
  33.  
  34. 'close the start-up form
  35. DoCmd.Close acForm, "frmStart"
  36. End Sub
  37.  
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
Attached Files
File Type: zip Open_A_Form2.zip (22.8 KB, 120 views)
Jan 23 '12 #23

NeoPa
Expert Mod 15k+
P: 31,186
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.
Jan 23 '12 #24

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Jan 23 '12 #25

NeoPa
Expert Mod 15k+
P: 31,186
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.
Jan 23 '12 #26

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Jan 24 '12 #27

ADezii
Expert 5K+
P: 8,599
@TheSmileyCoder:
You may also wish to look into the possibility of replacing Code Line #3 in the following Code
Expand|Select|Wrap|Line Numbers
  1. With oMail 
  2.  .To = strTO 
  3.  .Body = strMessageBody 
  4.  .Subject = strSubject 
  5.    .Display 
  6.      '.Send (Should you wish to immediately Send the E-Mail without displaying Outlook) 
  7. End With 
  8.  
with
Expand|Select|Wrap|Line Numbers
  1. .HTMLBody = strMessageBody
I haven't experimented with it much, but I'm pretty sure that you can use Embeded HTML Tags within this context.
Jan 24 '12 #28

100+
P: 332
@ADezii
Yes, that is possible, I have one that goes like so"
Expand|Select|Wrap|Line Numbers
  1.     strBody = "<table style='font-family:courier, monospace;font-size:12px;border-collapse:collapse;'>"
  2.     strBody = strBody & "<tr><td>Spent         </td><td align='right'>$" & FormatNumber(Spent, 2) & "</tr>"
  3.     strBody = strBody & "<tr><td>Balance       </td><td align='right'>$" & FormatNumber(Bal, 2) & "</tr>"
  4.     strBody = strBody & "<tr><td>Working Plan  </td><td align='right'>$" & FormatNumber(frm.[Total Cur Yr], 2) & "</tr>"
  5.     strBody = strBody & "<tr><td>Forecast      </td><td align='right'>$" & FormatNumber(frm.New_Forecast, 2) & "</tr></table><br>"
  6.     strBody = strBody & "<div style='font-family:courier, monospace;font-size:12px;'>"
  7.     strBody = strBody & frm.Description & "<br><br>"
  8.     strBody = strBody & frm.Comments & "<br></div>"
  9.  
Jan 24 '12 #29

NeoPa
Expert Mod 15k+
P: 31,186
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.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub cmdSend_Click()
  5.     Dim strHyperlink As String
  6.  
  7.     strHyperlink = "<FILE://ServerName/ShareName/" & _
  8.                    "User.Name/Access/Hyperlink Test.Mdb ;Try this!>"
  9.     With CreateObject(Class:="Outlook.Application")
  10.         With .CreateItem(olMailItem)
  11.             Call .Display
  12.             .Subject = "Test Project for Inserting Hyperlink in Outlook " & _
  13.                        "E-Mail Pointing to Front End Database"
  14.             .To = "BartSimpson@aol.com"
  15.             .Body = "Click on Hyperlink to Open Front End Database." & _
  16.                     vbCrLf & vbCrLf & strHyperlink
  17.             Call .Send
  18.         End With
  19.     End With
  20. End Sub
Jan 24 '12 #30

ADezii
Expert 5K+
P: 8,599
@Mariostg:
Thanks for the clarification. I actually tried to work Command Line Arguments into the following Syntax, utilizing HTMLBody, but as of yet, to no avail.
Expand|Select|Wrap|Line Numbers
  1. .HTMLBody =  "<a href=file://C:\Test\FE.mdb>Click to Open Database</a>"
Jan 24 '12 #31

NeoPa
Expert Mod 15k+
P: 31,186
I've tried to avoid any reliance on using HTML type emails. If you could let us know if that can be assumed then it might make it all a lot easier (as HTML tags will then become feasible).
Jan 24 '12 #32

TheSmileyCoder
Expert Mod 100+
P: 2,321
The message will likely be read in outlook 2003. What that means in terms of html or not I honestly dont know.
Jan 24 '12 #33

ADezii
Expert 5K+
P: 8,599
I have developed some Code that will:
  1. Send an E-Mail to any User consisting of a Semi-Colon Delimited List of Values placed in the Subject of the E-Mail (Employees;FL;33484).
  2. Minimal Code (Outlook VBA) on the End Users PC examines the Incoming Item, then:
    1. If it is in fact an E-Mail, and nothing else, and
    2. If it is from a specific Sender (TheSmileyCoder),
  3. Opens Access and sends the Subject String (Employees;FL;33484) as a Command Line Argument where Access can now parse it into its individual Elements, and make the appropriate action.
  4. The problem is that, right now, there is absolutely no User Intervention. You would send the E-Mail, and Access would Open on the End User's PC with the appropriate Command Line Arguments.
  5. This approach is rather 'Rogue', even by my standards, but if you are interested just let me know and I'll post the insanity (LOL).
Jan 24 '12 #34

TheSmileyCoder
Expert Mod 100+
P: 2,321
Hi Adezii.

It sounds good. I don't like the rogue part either, but I figure it would be possible to convert whatever outlook code you have to something that could be placed in a custom outlook button? And thus only execute when the user presses said button.

I would like very much to see what you have.
Jan 24 '12 #35

ADezii
Expert 5K+
P: 8,599
  1. An E-Mail would be sent to the End User with the Subject consisting of a Delimited List of Values, such as: Employees;FL;33484.
  2. In Outlook, on the End User's PC, Macros must be enabled in some manner.
  3. In the ThisOutlookSession Code Module of Outlook:
    Expand|Select|Wrap|Line Numbers
    1. Public WithEvents myOItems As Outlook.Items
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Application_Startup()
    2.   Set myOItems = Outlook.Session.GetDefaultFolder(olFolderInbox).Items
    3. End Sub
    4.  
    Expand|Select|Wrap|Line Numbers
    1. Private Sub myOItems_ItemAdd(ByVal Item As Object)
    2. Dim varRetVal As Variant
    3.  
    4. 'Is this an E-Mail Item?
    5. If TypeName(Item) = "MailItem" Then
    6.   With Item
    7.     If .Sender = "Dezii, Armund" Then       'Execute if only from this Sender
    8.       'Open the Front End Database, passing to it the Delimited String
    9.       varRetVal = Shell("C:\Program Files\Office 2003\OFFICE11\MSACCESS.EXE C:\Test\FE.mdb /cmd " & _
    10.                          .Subject, vbMaximizedFocus)
    11.     End If
    12.   End With
    13. End If
    14. End Sub
    15.  
  4. At some Entry Point in the Front End Database, parse the Command String, and take some action accordingly:
    Expand|Select|Wrap|Line Numbers
    1. Dim varSplit As Variant
    2.  
    3. varSplit = Split(Command, ";")
    4.  
    5. Debug.Print "Form Name : " & varSplit(0)
    6. Debug.Print "State Name: " & varSplit(1)
    7. Debug.Print "Zip Code  : " & varSplit(2)
  5. Code has been tested with Outlook/Access 2003, and is functional. Should be a simple matter to channge it to a Manual Operation as opposed to Automatic.
Jan 24 '12 #36

ADezii
Expert 5K+
P: 8,599
@TheSmileyCoder:
The more that I think about this, the more I feel as though Outlook needs to 'Capture' your E-Mail as soon as it arrives, then Prompt the User for a Response. The following, slightly revised, Code appears to accomplish this quite well. It displays a Modal Dialog Box (Message Box) to the User while at the same time allowing E-Mails to filter in behind it. I really was not sure how this Logic would play out, but it apparently does.
Expand|Select|Wrap|Line Numbers
  1. Private Sub myOItems_ItemAdd(ByVal Item As Object)
  2. Dim varRetVal As Variant
  3. Dim intResponse As Integer
  4.  
  5. 'Is this an E-Mail Item?
  6. If TypeName(Item) = "MailItem" Then
  7.   With Item
  8.     If .Sender = "Dezii, Armund" Then       'Execute if only from this Sender
  9.       intResponse = MsgBox("Open Access Database with Parameters specified in the Subject Field?", _
  10.                     vbDefaultButton1 + vbQuestion + vbYesNo, "Open Access Prompt")
  11.       If intResponse = vbYes Then
  12.         'Open the Front End Database, passing to it the Delimited String
  13.         varRetVal = Shell("C:\Program Files\Office 2003\OFFICE11\MSACCESS.EXE C:\Test\FE.mdb /cmd " & _
  14.                            .Subject, vbMaximizedFocus)
  15.       End If
  16.     End If
  17.   End With
  18. End If
  19. End Sub
  20.  
Jan 25 '12 #37

100+
P: 332
@ADezii
I am amazed. Pretty clever to tell outlook to parse the subject line.
I have a routine that sends emails with subject pretty much broken down like you suggested. But it does not go beyond that. I will definitely have to look at extending this with the proposed codes.
Jan 25 '12 #38

ADezii
Expert 5K+
P: 8,599
@Mariostg:
Outlook is basically passing the Subject Line to Access in tact as a Command Line Parameter, Access is actually doing the parsing and further processing.
Jan 25 '12 #39

ADezii
Expert 5K+
P: 8,599
One obvious flaw in my Logic is if TheSmileyCoder wanted to send a Standard E-Mail to a Recipient, one that did not require the Opening on an Access Database. My simple workaround was to also check for the exclusion of a Semi-Colon in the Subject Line (Code Line #8), in addition to being a Mail Item and from TheSmileyCoder.
Expand|Select|Wrap|Line Numbers
  1. Private Sub myOItems_ItemAdd(ByVal Item As Object)
  2. Dim varRetVal As Variant
  3. Dim intResponse As Integer
  4.  
  5. 'Is this an E-Mail Item?
  6. If TypeName(Item) = "MailItem" Then
  7.   With Item
  8.     If .Sender = "TheSmileyCoder" And InStr(.Subject, ";") <> 0 Then
  9.       intResponse = MsgBox("Open Access Database with Parameters specified in the Subject Field?", _
  10.                     vbDefaultButton1 + vbQuestion + vbYesNo, "Open Access Prompt")
  11.       If intResponse = vbYes Then
  12.         'Open the Front End Database, passing to it the Delimited String
  13.         varRetVal = Shell("C:\Program Files\Office 2003\OFFICE11\MSACCESS.EXE C:\Test\FE.mdb /cmd " & _
  14.                            .Subject, vbMaximizedFocus)
  15.       End If
  16.     End If
  17.   End With
  18. End If
  19. End Sub
Jan 25 '12 #40

100+
P: 332
@ADezii
Yes, that is what I meant to say.
I guess one issue that bugs me now is that whatever codes that has to go in MS Outlook would have to be installed individually??
Jan 25 '12 #41

100+
P: 332
maybe use this, or a variation of, at line #8
Expand|Select|Wrap|Line Numbers
  1. If .SenderName = "TheSmileyCoder" And Left(.Subject, 10) = "For Review" Then
That would help a bit.

By the way, for me .Sender is not working. I have to use .SenderName.
Jan 25 '12 #42

ADezii
Expert 5K+
P: 8,599
I guess one issue that bugs me now is that whatever codes that has to go in MS Outlook would have to be installed individually??
Unfortunately, yes. You would also have to Close, then Open Outlook so that the myOItems Object Variable gets Initialized - therein lies the problemo. Only the OP (TheSmileyCoder) can decide if this is worth the effort or not, if he even considers this approach.

P.S. - Odd that .Sender does not work - what Version of Outlook are you using?
Expand|Select|Wrap|Line Numbers
  1. If .SenderName = "TheSmileyCoder" And Left(.Subject, 10) = "For Review" Then
is probably a better Option than mine.
Jan 25 '12 #43

NeoPa
Expert Mod 15k+
P: 31,186
It seems that the overarching difficulty involved with this is that a hyperlink seems unable to pass parameters. Most frustrating.

In the absence of this facility I've struggled to come up with a way of passing control to the recipient of one of these emails which doesn't involve the user having to follow any instructions more complicated than : Click This, and preferably doesn't involve specialist software developed for an application, Outlook, that isn't directly related to the facility.

I think I have a viable solution. It involves sending an Excel spreadsheet with embedded code (NB. The code must be runnable by the recipient of course.) that runs code to execute the command found in cell A1. Once the code has executed it closes itself down (and can even close Excel if it discovers that it's the only open workbook). Let me know if this sounds like a viable approach for you.
Jan 25 '12 #44

NeoPa
Expert Mod 15k+
P: 31,186
I developed a workbook to handle my suggestion, as well as a database to test that the workbook is working as intended. I've changed the data in the workbook to obscure my identity from the public (this time), but the only data is the command line in cell A1 which you can easily configure for yourself. In fact, the design of this is such that you are expected to update the spreadsheet from code anyway.

There are numerous possible options with this. The database is deliberately set up with a space in the name to illustrate how such names should be handled. It simply echoes the command parameter passed and is purely there to illustrate proof of concept.

Have fun :-)

PS. In case it's not clear, the idea is that the instigator (Smiley) would first prepare the data in the RunCmd.Xls spreadsheet to reflect what they wanted the user to run at the far end. This would then be sent to the user, who would simply open the workbook from there by double-clicking on it. As it opens it will execute the command from the Excel code (using Shell()).

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Private Sub Workbook_Open()
  4.     Dim strMsg As String, strCmd As String
  5.  
  6.     strCmd = Range("A1")
  7.     strCmd = Replace(strCmd, "%AccessPath%", Environ("AccessPath"))
  8.     strCmd = Replace(strCmd, "%ProgramFiles%", Environ("ProgramFiles"))
  9.     strMsg = "You are about to run the command :%L%L%C"
  10.     strMsg = Replace(strMsg, "%L", vbNewLine)
  11.     strMsg = Replace(strMsg, "%C", strCmd)
  12.     If MsgBox(Prompt:=strMsg, _
  13.               Buttons:=vbOKCancel Or vbQuestion, _
  14.               Title:="RunCommand") = vbOK Then _
  15.         Call Shell(PathName:=strCmd, WindowStyle:=vbNormalFocus)
  16.     If Application.Workbooks.Count > 1 Then
  17.         Call Me.Close
  18.     Else
  19.         Call Application.Quit
  20.     End If
  21. End Sub
PS. Attachment removed, as a later version may be found at post #52.
Jan 25 '12 #45

TheSmileyCoder
Expert Mod 100+
P: 2,321
@ NeoPa: Your excel idea is nice thinking, however I am pursuing the outlook way. I think using excel as a hyperlink is a bit confusing to the users :P

I think I have something decent cooked up so far, though its quite different from coding in Access at least in the way I am used to.

So far, I have written code that enables/disables the toolbar based on a few parameters tied to the SelectionChange event for the ActiveExplorer. Item must be in the inbox folder, and must be a mail item (not a task/appointment...so on). Now too this, I will need to add something that checks for certain charecters/codes in the email body, which I have not yet decided on. Im still mostly in the feasibility study of this project. However its fairly easy done, standard string manipulation.

The toolbar once activated will read/parse the email, and look for whatever code I decided to use, and based on that open the database, probably by automation, or by shell command with command line parameters.


My main concern now lies in distribution. I don't so much mind going around to the 20-50 users and installing some extra code in their outlook, is the maintenance of said code that bothers me more. I will have to look into both how to distribute the code, as well as update it. That however is a probably a matter for another question.
Jan 25 '12 #46

TheSmileyCoder
Expert Mod 100+
P: 2,321
I do see that your excel suggestion does have the benefit of not having to go around to each outlook installation and managing software there. I will give it some more thought...
Jan 25 '12 #47

TheSmileyCoder
Expert Mod 100+
P: 2,321
I just want to tack this onto the thread before I forget it. You can get the MSAccess.exe path from the excel file if you have set a reference to the Access object model, and then use:
Expand|Select|Wrap|Line Numbers
  1. SysCmd(acSysCmdAccessDir)
Users must also allow macros, in order for the excel code to run. I think a potential problem with the Excel solution is that we will soon be upgrading to Office 2010, and I fear that I will have to look into alot of macro security issues at that time.
Jan 26 '12 #48

100+
P: 332
What if another approach is considered? No Outlook coding, no Excel. From the original posting:
Expand|Select|Wrap|Line Numbers
  1. 1.  Open the access front-end on the users PC.
  2. 2.  Open relevant form
  3. 3.  Goto relevant Item
1. This can be done, no problem

Now in the Access application, suppose you have an ActionItems table containing fields such as username, actionID, isVerified. This table would be maintained by The Smiley.

When the recipient gets the email, the hyperlink is clicked, Access starts and goes to the specific form (Stage 2) containing the ActionItems records filtered for the user. The user clicks the actionID which in turns opens the form containing the items to review (Stage 3).

So one click to start access, one click to go to each item to review, maybe on click to mark the item reviewed...
Jan 26 '12 #49

ADezii
Expert 5K+
P: 8,599
@TheSmileyCoder:
Two last questions:
  1. Do you have access to the User's PCs on a Network?
  2. Do you have Full Rights to the Folder where the User's Database resides?
Jan 26 '12 #50

54 Replies

Post your reply

Sign in to post your reply or Sign up for a free account.