I have an Access database that exports data to a spreadsheet. One column contains email addresses which I would like to be clickable links to the email address from a database table. Currently the lines of code that do this look like this:
sCell1 = "B" & iRow
xlSheet.Cells(iRow, 2) = ![Email]
sEmail = "mailto:" & ![Email]
xlSheet.Hyperlinks.Add Range(sCell1), Address:=sEmail
![Email] is the email field from the relevant table.
This successfully adds the email address into the cell as a hyperlink, however, mailto: disappears and just the contents of the ![Email] field are placed in the cell. Clicking the link in Excel produces 'The address of this site is not valid'. I've tried various modifications to the address, adding single quotes, enclosing it in HTML <a /a>, etc. nothing seems to give the cell the necessary mailto: to launch an email package.
I can't test this from Access, but please try
sCell1 = "B" & iRow
xlSheet.Hyperlinks.Add Anchor:=xlSheet.Range(sCell1), Address:="mailto:" & Email, TextToDisplay:=Email
If you are using late-binding you might need to use
sCell1 = "B" & iRow
xlSheet.Hyperlinks.Add xlSheet.Range(sCell1), "mailto:" & Email, , , Email