Make a hyperlink in a VBA created email

HVACMAN24 · Feb 17, 2011

I have some code setup to send an email to people as a reminder to do do their monthly assessment, and in it I list the location of the database but I would like to make it a hyperlink instead. Below is an example of what I have, any easy ways to change it to a hyperlink?

link = "M:\Engineering\Cooling\ACE\5S Self-Scorecard.mdb"

DoCmd.SendObject , , , Email.Value, , , "5S Reminder", begin & Area.Value & finish & " You can find the database at " & link, True

Thanks in advance

hbrems · Feb 17, 2011

Try sending mails in the below way. You can get more flexible with the HTML code.

Code:

Dim to As String

' Create to list
to = ""
For i = 1 To last
     to = to & Email.Value & ";"
Next

' Create message
message = "<HTML><BODY><P>Type something here...</P></BODY></HTML>

' Send mail
Set OutApp = CreateObject("outlook.application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
With OutMail
    .to = to
    .cc = "whoeveryouwant@yourcompany.com"
    .subject = "yoursubject"
    .HTMLBody = message
    .Display
End With
Set OutMail = Nothing
Set OutApp = Nothing

HVACMAN24 · Feb 17, 2011

I changed to the HTML email like hbrems suggested but my hyperlink still isnt working. I used your code and set the message variable to:

message = "<HTML><BODY><P>irrelevant words<a href="M:\Engineering\Cooling Dev\ACE\Cooling Lab ACE\Cooling Lab New 5S\New 5S\5S Self-Scorecard.mdb"> Click Here to access the scorecard</a></P></BODY></HTML>"

Any idea on whats wrong or what I need to change?

HVACMAN24 · Feb 17, 2011

With the message variable set to what I have above I keep getting a "Compile Error: Expected: End of Statement" error and the cursor goes to the M which is the directory I'm using.

In case that means anything to you.

blah · Jul 31, 2012

HVACMAN24 said:
With the message variable set to what I have above I keep getting a "Compile Error: Expected: End of Statement" error and the cursor goes to the M which is the directory I'm using.

In case that means anything to you.

It's the quotation mark thing. Try this:

Code:

"<HTML><BODY><P>[FONT=Arial][SIZE=3][FONT=Verdana][SIZE=2]irrelevant[/SIZE][/FONT] [/SIZE][/FONT]words<a href=""" & "M:\Engineering\Cooling Dev\ACE\Cooling Lab ACE\Cooling Lab New 5S\New 5S\5S Self-Scorecard.mdb""" & "> Click Here to access the scorecard</a></P></BODY></HTML>"

To include quotation marks in a string, you have to jump through hoops like those shown above.

But I actually had to do this to have the email show up with a clickable link:

Code:

"<[URL="file:///\\M:\Engineering\Cooling"]file:\\M:\Engineering\Cooling[/URL] Dev\ACE\Cooling Lab ACE\Cooling Lab New 5S\New 5S\5S Self-Scorecard.mdb> "

And at that the html brackets aren't necessary if the string is continuous (no spaces).

But to REALLY make it happen, I had to do this:

Code:

HTMLbody_ = "<a href=""" & "C:\some folder\some subfolder\etc\and etc" & """>" & "C:\some folder\some subfolder\etc\and etc" & "</a > "

...and then, in the email code, do this:

Code:

.
.
.
.Subject = subject_
[B][COLOR=red].HTMLBody = HTMLbody  [/COLOR][COLOR=black]'''[SIZE=1]...(give credit [URL="http://www.mrexcel.com/forum/showthread.php?542275-Insert-link-into-email-body-using-VBA&p=2677553&viewfull=1#post2677553"]where[/URL] it's due!)[/SIZE][/COLOR][/B]
.To = recipient_
.Send
.
.
.

...now the email shows up with a proper, functioning link, ala:

C:\some folder\some subfolder\etc\and etc

hth.

Make a hyperlink in a VBA created email

HVACMAN24

Registered User.

hbrems

has no clue...

HVACMAN24

Registered User.

HVACMAN24

Registered User.

blah

Registered User.

Similar threads

Users who are viewing this thread