473,836 Members | 2,180 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Email Item as Link from Access

TheSmileyCoder
2,322 Recognized Expert Moderator Top Contributor
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
54 14411
NeoPa
32,584 Recognized Expert Moderator MVP
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
2,322 Recognized Expert Moderator Top Contributor
@ 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
sierra7
446 Recognized Expert Contributor
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, 174 views)
Jan 23 '12 #23
NeoPa
32,584 Recognized Expert Moderator MVP
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
2,322 Recognized Expert Moderator Top Contributor
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
32,584 Recognized Expert Moderator MVP
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
2,322 Recognized Expert Moderator Top Contributor
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
8,834 Recognized Expert Expert
@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
Mariostg
332 Contributor
@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
32,584 Recognized Expert Moderator MVP
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

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

Similar topics

2
3360
by: James Fraley | last post by:
I've created a MS Access form and get the exact same results as below? Now, I believe it has something to do with storing EMails, not OLE. I will appreciate any help! --------------------------------------------------------------- I've created a form with an OLE obect linked to a MS Access OLE Object. I need to store an email from Outlook in the field. Everything works fine if I drop the email on my desktop and then pick
5
5412
by: Alan | last post by:
Hello, I trying to export my email to an access database. Outlook already this function, but not all the fields are available once the export is complet. The time & date stamps are not available. Also, the attachment file names field is not present. Are there other means of obtaining these information? Regards,
0
1387
by: gj | last post by:
If anyone has worked with this I would be interested to find out. I have successfully put send/receive emails into an application using SMTP/POP server but would like to do something more professional with IMAP. I basically want a full email client in Access that can connect to an IMAP server. I have played with a few ActiveX controls but would like others experiences. TIA
7
5417
by: Daven Thrice | last post by:
I know how to send email out of Access, and that's no problem. What I need to figure out is how to receive email into access. Say that I give access an email address, such as access@daven.com. I want all email that comes to that address to be received directly by the database so that I can parse out the files. I'm thinking that, worst case, I can write my own email client using the winsock control. But is that my only alternative?
8
9639
by: tymperance | last post by:
I have a report that I currently email directly from Access as an attachment using a macro. I need to make it be the body of the email instead. Can that be accomplished without extensive VBA programming? Thanks!
2
8996
by: diwadube2003 | last post by:
I get a Warning message while automatically sending email through MS Access, using Outlook. Can anybody help me out with this.
6
6924
by: ttamilvanan81 | last post by:
Hai. I needto Receive Email using Ms-Access and Outlook. On the receiving end, the user receives the email at the Outlook. When they opens the Email, the message box should have a button called “Save to Access”. When this button is pressed, a dialog box should pop up where the user can choose a contact name and a job name and click on SAVE Here contact name and job name are in the access table fields. We go to select any one contact...
2
1806
by: PPPPPPPPP | last post by:
I Am Not Able To Link Access Database In Visual Basic .please Help Me To Do So . Thanking You In Anticipation.
0
2069
by: wassimdaccache | last post by:
Hello Experts; I have a wireless router TP-link I made security phrase for it. What i am trying to do is to configure a d-link access point as a repeater for the TP-link and always not working :(!!! while i am scanning in my access point (d-link) I see the tp-link and I am able to connect but when I scan using my wifi on my laptop I'm not seeing the D-link . Why ?
4
1921
by: davekearley | last post by:
Hi all, first post, looks like a great site. I have a small problem with Access. I need to link to an ODBC table (SQL7) but when i go to the link dialogue, the ODBC entry is missing. I have other linked tables in this DB and they all re-link ok so the system DSN is working fine. Any ideas? I have not linked a table for some time and cant remember how to get the ODBC option back.
0
9656
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10821
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10575
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
7774
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6975
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5642
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5812
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4443
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3102
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.