Email HTML hyperlinks via VBA (href)

[mjphillips88]

Hi all,

I am trying to use VBA to send emails in HTML format including hyperlinks.

I am using the syntax HREF = ""....
<a href="" .....
It works when I specify a link inside the function, such as www.google.com.<a href="" www.google.com"",="" etc.

However, I am trying to reference a link stored in a particular cell in the workbook. So, for example, instead of putting www.google.com in the HREF I would like to reference cell A1 which would have www.google.com in it.

Is there a way to do this?

Thanks,

Michael

 

[dermie_72]

could you post your code, so we can review.
From what you've said though, it sounds like you need to set a variable up for the link, then capture the cell reference, and use the variable in the HREF= Varable & ...
if that fails, please post your code, as well as which cell reference/s you're capturing the link from.

 

[mjphillips88]

Here is what I am trying to do as part of an HTML email in VBA.

strbody = "Hi " & .Range("O5") & ",

" & vbNewLine & vbNewLine & _
"Today you have " & .Range("B1") & " matches.

" & vbNewLine & vbNewLine & _
.Range("A5") & ". " & .Range("Q5") & " visited " & "<A HREF = ""[cell reference1]"" >[cell reference2] </A>"

Is there a way I can pull the cell references from the excel workbook?

Thanks,

Michael

 

[dermie_72]

have you tried doing it the same way as your other references? You're calling O5 for the persons name, B1 for the number of matches, ect.

If the reference is in a different workbook, then you would need to define that workbook name.

 

[JMonty]

Shouldn't be too hard. If your e-mail is HTML format already (ie. using something like Ron de Bruin's e-mail sending VBA), something like follows is a nice and simple solution.

strlink = ActiveSheet.Range("O5").Text

StrBody = "Visit [URL="http://www.mrexcel.com/forum/& strlink &"]Google[/URL]."

I just tested it and it works on my end. Please let us know if I understood this correctly.

We look forward to your reply.

EDIT: For some reason, the message board is transforming the code to HTML by itself so the code is not properly shown. Try using the above referencing first (.Text) and let us know how it works.