Email Hyperlink from form VBA

[Sue Wilkes]

Hello All,
Access 2003
I have a form linked to table (HYInReg) on the form are 3 fields (controls)
called: Hyperlink1, Hyperlink2 and Hyperlink3. Using the form the user will
highlight the field then select the Hyperlink Icon (World&Chain) then enter a
hyperlink to a document stored on the data server. The database is a
document control system, the procedure is then to inform the necessary
personnel using email of the document (could be any format ie .doc, .xls,
..pdf etc.) by inserting the hyperlink(s) into the email. Is there any way in
which I can do this from the access form. I have created an email button
which will open outlook and enter the send to and subject details however I
am at a loss on how to insert the hyperlink either as an attachment or in the
main body. Any help would be gratefully appreciated. Below is the code I
am using for the email button
Private Sub EMAIL_Click()
On Error GoTo Err_EMAIL_Click

Dim stAppName As String
Dim SendTo As String, MySubject As String, MyMessage As String

stAppName = "C:\Program Files\Microsoft Office\OFFICE11\OUTLOOK.EXE"
Call Shell(stAppName, 1)

SendTo = "(e-mail address removed)"
MySubject = "HYPERLINK FROM HY01 DOCUMENT REGISTER"
DoCmd.SendObject acSendNoObject, , , SendTo, , , MySubject, MyMessage

Err_EMAIL_Click:
If Err <> 2501 Then ' Ignore the error
MsgBox "Error: " & Err & " " & Error

End Sub
(FYI - The Is Hyperlink property is set to No on the form control but in the
table the data property is set to Hyperlink)

 

[freakazeud]

Hi,
there is no need to use the Shell() and SendObject() method together. The
Sendobject method itself would do...however, it is very limited and some of
its limitations are that you can only send plain text formatted emails (so no
hyperlinks in the body) and only attach internal db objects as attachments
(so no external files).
Since you want to do one or the other you can use automation code to achieve
this.
If you want to use active hyperlinks in the body then use automation to
create an HTML email. You can then use HTML tags to format whatever values
you want as hyperlinks. A good sample of this can be found here:

http://www.utteraccess.com/forums/s...=64822&Zd=l&Zn=&Zt=3&Zs=b&Zy=#Post1211794&Zp=

If you want to attach the files as attachment then you can also use
automation code. A good sample could be found here:

http://support.microsoft.com/default.aspx/kb/209948/en-us

Both methods use early binding which requires a reference...you could easily
switch the code to late binding as well if you wanted.
HTH
Good luck

 

[BruceM]

A hyperlink can be inserted into a plain text message by means such as:

strLink = "<file://C:\Documents and Settings\Folder\File.txt>"

strLink can then be used in the body of the e-mail message using SendObject.
Assembling the string could be a bit of a trick, depending on the variables.
The brackets are needed if there are spaces in the address.

It can also be used with http instead of file when you are sending a link to
a web address. There are a few other choices, too, but they are probably
not of concern here.

An observation about html e-mail is that some people do not use it, so
whatever you send needs to take into account the recipient's e-mail format.

 

[Sue Wilkes]

Bruce, Thank you so much for the point in the right direction. I have
managed to create a button to send (too many records) to the file and the
hyperlink works! Great. However, as I am newbie their is a but. Could you
please give me the VBA code for sending only the selected record which I
currently get the user to select via a combo box (RegisterNumber is a primary
key - text). Then secondly the VBA code for opening outlook automatically
filling in the To, subject etc.. and the txt file as an attachment then
closing the outlook application. If I can get this last bit right I can
(after final testing) release the database for general use; although I'm
nowhere near your standards I will have felt proud to create our document
control register but this has only been possible with the use of this help
forum - long may it prosper. Kind regards Sue

 

[BruceM]

I would not have responded at all had I understood the question to be about
inserting a text file as an attachment. I responded in order to point out
that it is possible to include a link in the body of a plain text e-mail
(created with SendObject).

Here are a few things I can point out. In you sample code you defined
MyMessage as a string, but you didn't assign a value to MyMessage as far as
I can see. If you include:

Dim strLink as String

and assign strLink thus:

strLink = "<file://C:\Documents and Settings\Folder\File.txt>"

you can do something like this with the MyMessage string:

MyMessage = "Please take a look at this file: " & vbCrLf & strLink

vbCrLf forces a new line in the message (or anywhere else that you have a
text string in VBA code)

You would need to find a way to define strLink as the actual path to the
file. The details would depend on the specifics of your situation. I
cannot advise you on specifics other than to say you can open the Insert
Hyperlink dialog box from within Access, navigate to the file, and insert
the link in that way. If this is of interest I can provide more details,
although you may do better to start a new thread, as I am not sure my
limited experience at this would necessarily point you to the best approach.



I believe that SendObject opens the default e-mail program, so there is no
need to open Outlook separately if it is the default program. If it is not
the default program it gets more involved, and is something with which I
have little experience. I believe that it would involve automation code, as
has already been pointed out in the other response to your question. The
following links may provide some help in that direction.



http://www.peterssoftware.com/c_emfa.htm



http://www.granite.ab.ca/access/email.htm

 

[Sue Wilkes]

Many thanks to everyone for your help this has pointed me in the right
direction that has allowed me to solve the problem. Many thanks