Results 1 to 15 of 15
  1. #1
    jcc285 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2014
    Posts
    70

    Converting text data to Hyperlink

    I am using Office automation to create e-mail which opens in Outlook. The problem I have is that the e-mail field in my Access main table is stored as text in order to make edits simple. If it were stored as a hyperlink every time you click on it, to edit the data, it opens Outlook which is just annoying.

    I read through all my email addresses and create a 'Distinct' record set which is sent to a temporary table into a Hyperlink field. When Outlook subsequently opens, my vba having generated a sufficient number of e-mails using a limit of 90 bcc addresses in each e-mail, the address field is inserted as text. Outlook sorts this out but takes an inordinate amount of time to change each text address to a hyperlink, it indicates that this is done by underlining each address in the bcc list but it is like watching paint dry!!



    What I should like to do is carry out the conversion in vba when constructing the temporary table. So: - Read the text, turn this into a mailto: hyperlink and store in the temporary table. That should resolve the Outlook problem but I cannot find a way of doing this in vba, adding the mailto: as a string then stores 'mailto:zxy@abc.com' as text within the hyperlink field in the temporary table.

    Does anyone know how to change plain text to a mailto: hyperlink, please.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,824
    A hyperlink is composed of 3 parts separated by # character. An expression can construct hyperlink with concatenation. ="#" & [fieldname] & "#"

    Review http://allenbrowne.com/casu-09.html

    However, 'mailto:' is not one of the examples.

    This might not even speed up whatever Outlook is doing. Are you using SendObject for email?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    jcc285 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2014
    Posts
    70
    No, the address list is sent to an olObject, olRecipient as bcc within an Send e-mail function returning true if successful creation occurs.

    I have tried as many combinations of the concatenation you provided but that only creates a string not a mailto: hyperlink.

    I have searched for some time to try and resolve this but no luck, thanks for your help.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,824
    Okay, I did a test:

    CurrentDb.Execute "UPDATE table1 SET email = '#mailto:your address here#'"

    The result is a clickable link that opens Outlook email. It was definitely faster than opening Outlook with just the email address.

    Post your code for analysis.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    jcc285 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2014
    Posts
    70
    Quote Originally Posted by June7 View Post
    Okay, I did a test:

    CurrentDb.Execute "UPDATE table1 SET email = '#mailto:your address here#'"

    The result is a clickable link that opens Outlook email. It was definitely faster than opening Outlook with just the email address.

    Post your code for analysis.
    My temporary table looks like this,attached, all dummy data.Click image for larger version. 

Name:	(1)tbl_Bulk_EMail.JPG 
Views:	20 
Size:	29.4 KB 
ID:	38519 The E_Mail field is a hyperlink field but the data stored is just text even though it looks like a hyperlink. Right clicking and 'Open Hyperlink' produces nothing. The 'mailto:' bit is missing.

    OK, I've put the 'UPDATE' in a loop to change each text e-mail address in my temporary table tbl_Bulk_EMail into a mailto:hyperlink.

    ' Make sure the stored e-mail address in tbl_Bulk_Email is a mailto: hyperlink
    Dim rsa As DAO.Recordset
    strSQL = "SELECT E_Mail FROM tbl_Bulk_Email" 'E-Mail address field in temporary table stored as text
    'Open a recordset of EMail addresses
    Set rsa = dbCurrent.OpenRecordset(strSQL)
    If Not rsa.BOF And Not rsa.EOF Then
    rsa.MoveFirst
    'Loop through the records updating the addresses to mailto:hyperlinks
    Do While Not rsa.EOF
    dbCurrent.Execute "UPDATE tbl_Bulk_Email SET E_Mail ='#mailto: & rsa!E_Mail & #'"
    rsa.MoveNext
    Loop
    End If

    This produces the attached Click image for larger version. 

Name:	(2)test e-mail.JPG 
Views:	21 
Size:	92.9 KB 
ID:	38520when Outlook opens.

    I have been struggling with this for ages !! Many thanks for your help

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,824
    How can code work with 'dbCurrent' instead of 'CurrentDb'?

    Your UPDATE syntax is not correct. Need to concatenate. Missing quote marks.

    dbCurrent.Execute "UPDATE tbl_Bulk_Email SET E_Mail ='#mailto:" & rsa!E_Mail & "#'"

    Also, this code is incomplete. It will update all records with the same email address. Need a WHERE clause. Or:

    Code:
    Dim rsa As DAO.Recordset
       strSQL = "SELECT E_Mail FROM tbl_Bulk_Email" 'E-Mail address field in temporary table stored as text
      'Open a recordset of EMail addresses
        Set rsa = dbCurrent.OpenRecordset(strSQL)
        If Not rsa.BOF And Not rsa.EOF Then
         rsa.MoveFirst
         'Loop through the records updating the addresses to mailto:hyperlinks
         Do While Not rsa.EOF
          rsa.Edit
          rsa!E_Mail = "#mailto:" & rs!E_Mail & "#"
          rsa.Update
          rsa.MoveNext
         Loop
        End If
    However, don't need to loop recordset. Just update all records in one UPDATE action. All that code can be replaced with one line (note the use of embedded apostrophes and &:

    CurrentDb.Execute "UPDATE tbl_Bulk_Email SET E_Mail ='#mailto:' & [E_Mail] & '#'"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    jcc285 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2014
    Posts
    70
    Quote Originally Posted by June7 View Post
    How can code work with 'dbCurrent' instead of 'CurrentDb'?

    Your UPDATE syntax is not correct. Need to concatenate. Missing quote marks.

    dbCurrent.Execute "UPDATE tbl_Bulk_Email SET E_Mail ='#mailto:" & rsa!E_Mail & "#'"

    Also, this code is incomplete. It will update all records with the same email address. Need a WHERE clause. Or:

    Code:
    Dim rsa As DAO.Recordset
       strSQL = "SELECT E_Mail FROM tbl_Bulk_Email" 'E-Mail address field in temporary table stored as text
      'Open a recordset of EMail addresses
        Set rsa = dbCurrent.OpenRecordset(strSQL)
        If Not rsa.BOF And Not rsa.EOF Then
         rsa.MoveFirst
         'Loop through the records updating the addresses to mailto:hyperlinks
         Do While Not rsa.EOF
          rsa.Edit
          rsa!E_Mail = "#mailto:" & rs!E_Mail & "#"
          rsa.Update
          rsa.MoveNext
         Loop
        End If
    However, don't need to loop recordset. Just update all records in one UPDATE action. All that code can be replaced with one line (note the use of embedded apostrophes and &:

    CurrentDb.Execute "UPDATE tbl_Bulk_Email SET E_Mail ='#mailto:' & [E_Mail] & '#'"

    Many thanks for your help, I have 'lost the plot' with this as have been trying for so long to get it right without success.

    In my code I now have the single line:
    ' Convert the text stored e-mail address field in tbl_Bulk_Email to a mailto: hyperlink dbCurrent.Execute "UPDATE tbl_Bulk_Email SET E_Mail ='#mailto:' & [E_Mail] & '#'"

    Which you kindly gave me, however: -
    The data in tbl_Bulk_Email now looks like this: -Click image for larger version. 

Name:	(1)tbl_Bulk_EMail.JPG 
Views:	19 
Size:	26.5 KB 
ID:	38533

    and

    when Outlook opens the bcc address line looks like this: - Click image for larger version. 

Name:	(2)test e-mail.JPG 
Views:	19 
Size:	94.8 KB 
ID:	38534

    This is close, certainly closer than I was before your help, but it still isn't correct as Outlook errors, on every address, when a send is attempted, typically: -Click image for larger version. 

Name:	ActualError.JPG 
Views:	19 
Size:	39.5 KB 
ID:	38536
    Attached Thumbnails Attached Thumbnails Outlook error.JPG  

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,824
    I did not test building multi-address email, just the click open for one address which does work and confirmed the string is valid for that purpose.

    However, I am now remembering reading that these hyperlink strings will not work for multi-address constructed with concatenation. The final advice was that the address part had to be extracted, dropping the "mailto:", which puts you right back where you started.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    jcc285 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2014
    Posts
    70

    Unhappy

    Quote Originally Posted by June7 View Post
    I did not test building multi-address email, just the click open for one address which does work and confirmed the string is valid for that purpose.

    However, I am now remembering reading that these hyperlink strings will not work for multi-address constructed with concatenation. The final advice was that the address part had to be extracted, dropping the "mailto:", which puts you right back where you started.
    Yes, my original post, was trying to solve the concatenation problem. I was trying to do it one address at a time so your solution to change the data in the table for all addresses at once looked promising. As you say I am back where I started. I believed this should be possible as, if you use the FollowHyperlink construct, you can concatenate the mailto:anaddress. Outlook opens correctly displaying the address but only for one address at a time.
    Many thanks for your help

  10. #10
    jcc285 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2014
    Posts
    70
    Do you think there might be a way using an ALTER table to either change an existing column type or add a new column?

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,824
    Yes. https://www.w3schools.com/SQl/sql_alter.asp

    Why would you need this?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    jcc285 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2014
    Posts
    70

    Red face

    Quote Originally Posted by June7 View Post
    Yes. https://www.w3schools.com/SQl/sql_alter.asp

    Why would you need this?
    Try creating a new Access Db, then a table with a single 'short text' field, I called mine 'e-mail'. Enter some rows of test data which look like email addresses e.g. aa@anymail.com etc. Save the table. Now open the table in 'Design View' and change the data type of the field to 'Hyperlink', close and re-open in Datasheet view, lo and behold the original 'text' data has turned into a 'mailto:hyperlink'!! Cursor changes to a link type when you hover and right click, 'Edit Hyperlink' shows it to be real. Clicking on it opens Outlook.

    Your idea of updating the table led me to try this yesterday.
    Sequence given below:

    Click image for larger version. 

Name:	Design.JPG 
Views:	16 
Size:	19.4 KB 
ID:	38543


    Click image for larger version. 

Name:	Text.JPG 
Views:	16 
Size:	23.6 KB 
ID:	38542

    Click image for larger version. 

Name:	DesignChange.JPG 
Views:	16 
Size:	20.4 KB 
ID:	38544

    Click image for larger version. 

Name:	Hyperlinks.JPG 
Views:	16 
Size:	19.8 KB 
ID:	38545

  13. #13
    jcc285 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2014
    Posts
    70
    Quote Originally Posted by jcc285 View Post
    Try creating a new Access Db, then a table with a single 'short text' field, I called mine 'e-mail'. Enter some rows of test data which look like email addresses e.g. aa@anymail.com etc. Save the table. Now open the table in 'Design View' and change the data type of the field to 'Hyperlink', close and re-open in Datasheet view, lo and behold the original 'text' data has turned into a 'mailto:hyperlink'!! Cursor changes to a link type when you hover and right click, 'Edit Hyperlink' shows it to be real. Clicking on it opens Outlook.

    Your idea of updating the table led me to try this yesterday.
    Sequence given below:

    Click image for larger version. 

Name:	Design.JPG 
Views:	16 
Size:	19.4 KB 
ID:	38543


    Click image for larger version. 

Name:	Text.JPG 
Views:	16 
Size:	23.6 KB 
ID:	38542

    Click image for larger version. 

Name:	DesignChange.JPG 
Views:	16 
Size:	20.4 KB 
ID:	38544

    Click image for larger version. 

Name:	Hyperlinks.JPG 
Views:	16 
Size:	19.8 KB 
ID:	38545
    I have found the solution, see: - https://access-programmers.co.uk/for...d.php?t=283786

    That has been a long journey, many thanks for your help.

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,824
    Okay, but how does that help with building the multi-address string? Already determined using email hyperlink fails.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    jcc285 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2014
    Posts
    70
    Quote Originally Posted by June7 View Post
    Okay, but how does that help with building the multi-address string? Already determined using email hyperlink fails.
    Yes, you are right, of course.

    I have managed to transfer text data from one table to another appearing in the target table as a clickable hyperlink which was progress!!

    However when I then read the data back into Outlook as a multi address string back comes the #mailto:an address#.

    I have to accept, I think, that this isn't possible and just send text to Outlook and let it sort it out, which it does, but slowly!!

    Most grateful for all your help

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Converting Text to Single
    By sibe in forum Access
    Replies: 5
    Last Post: 08-24-2018, 03:17 AM
  2. Converting Hyperlink field back to short text
    By gksmith5 in forum Access
    Replies: 3
    Last Post: 06-02-2018, 01:19 AM
  3. Converting Text To Columns
    By gameemaster in forum Queries
    Replies: 1
    Last Post: 04-18-2017, 02:12 AM
  4. Converting a Code to Text
    By rmcafee in forum Programming
    Replies: 7
    Last Post: 09-13-2011, 08:52 PM
  5. Export to excel with hyperlink converting
    By Jamy in forum Import/Export Data
    Replies: 0
    Last Post: 03-22-2010, 08:36 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums