Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Add Email Hyperlink with VBA

  1. #1
    Board Regular
    Join Date
    Oct 2007
    Location
    Chester
    Posts
    480
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Add Email Hyperlink with VBA

    Hi All,

    I have a sheet ("Sheet2") with email addresses in column K down to about 300 or so. I want to write a bit of code to add a email hyperlink to each cell. I want the email address as the cell value and I want the subject to be "Report for " & CompanyName

    So far I have the following, I'm just missing the important bit!
    Code:
    Sub AddHyperlinks()
    
        Dim CompanyName As String
        
        With Application
            .ScreenUpdating = False
            .DisplayAlerts = False
        End With
    
        Lastrow = Range("D" & Rows.Count).End(xlUp).Row
        
        Range("K2:K" & Latrow).Select
        For Each cell In Selection
            cell.Activate
            CompanyName = cell.Offset(0, -7).Value
            
            '...Insert code to add hyperlink
            
        Next cell
                   
        With Application
            .ScreenUpdating = True
            .DisplayAlerts = True
        End With
    
    End Sub
    Many thanks,

    Jay
    Last edited by TFCJamieFay; Mar 24th, 2009 at 02:01 PM. Reason: Typo!
    Jay says, "When in Rome, eat lions!!"

  2. #2
    Board Regular
    Join Date
    Jan 2009
    Location
    Spain
    Posts
    983
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add Email Hyperlink with VBA

    Hi TFCJamieFay,

    You could try something like this

    Code:

    Set OutApp = CreateObject("Outlook.Application")
    On Error Resume Next
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
    .to = Activecell.text
    .CC = ""
    .BCC = ""
    .Subject = "Report for " & CompanyName .body = body
    .display
    End With
    On Error GoTo 0

    ColinKJ

  3. #3
    Board Regular
    Join Date
    Oct 2007
    Location
    Chester
    Posts
    480
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add Email Hyperlink with VBA

    Thanks for your reply ColinKJ, but I have been playing around with the macro recorder and have gone with the following. It seems to work OK. Maybe I should have tried that first. Thank you for your help though!

    Here's my code to help anyone else out there (if it's not full of holes that is but it worked for me!)
    Code:
    Sub AddHyperlinks()
    
        Dim CompanyName As String
        
        With Application
            .ScreenUpdating = False
            .DisplayAlerts = False
        End With
    
        Lastrow = Range("D" & Rows.Count).End(xlUp).Row
        
        Range("K2:K" & Lastrow).Select
        For Each cell In Selection
            cell.Select
            
            If cell.Value <> "" Then
                CompanyName = cell.Offset(0, -7).Value
                
                ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell, Address:= _
                    "mailto:" & cell.Value & "?subject=Report%20for%20" & CompanyName, _
                    TextToDisplay:=cell.Value
            End If
            
        Next cell
                   
        With Application
            .ScreenUpdating = True
            .DisplayAlerts = True
        End With
    
    End Sub
    Jay says, "When in Rome, eat lions!!"

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •