+ Reply to Thread
Results 1 to 3 of 3

Putting a clickable link in the body of email sent from Excel

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    04-28-2004
    Location
    Norwich, England
    MS-Off Ver
    2010
    Posts
    119

    Putting a clickable link in the body of email sent from Excel

    Hi again

    I've managed to get the following macro working except for one small thing - the link to the folder where the spreadsheet (that will be attached to the email) resides (which I got to work fine in a macro to send out tasks) just ends up as plain text and is not clickable

    Can anyone help me to make this link clickable in the email so that when the recipient receives the email they can just click on the link and go straight to the relevant folder please?

    (I've highlighted the line with what I would like to work as a link in red)

    Sub Send_Schedule()
    
        Dim OutApp As Object
        Dim OutMail As Object
        Dim sh As Worksheet
        Dim cell As Range, FileCell As Range, rng As Range
     
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With
     
        Set sh = Sheets("Email Schedule")
     
        Set OutApp = CreateObject("Outlook.Application")
        OutApp.Session.Logon
     
        For Each cell In sh.Columns("B").Cells.SpecialCells(xlCellTypeConstants)
     
            'Enter the file names in the C:Z column in each row
            Set rng = sh.Cells(cell.Row, 1).Range("C1:C100")
     
            If cell.Value Like "?*@?*.?*" And _
               Application.WorksheetFunction.CountA(rng) > 0 Then
                Set OutMail = OutApp.CreateItem(0)
     
                With OutMail
                    .to = cell.Value
                    
                    .Subject = "Testfile"
                    
                   .Body = "Please find attached a copy of this month's reporting schedule (pictorial calendar format)." & _
        Chr(10) & "" & _
        Chr(10) & "The hyperlink to the actual folder is embedded below for your convenience:" & _
        Chr(10) & " " & _
        Chr(10) & " I:\Files\More Files\Meeting Data\00. Current Month Meeting Data"
    
        
      
                    
                    '& cell.Offset(0, -1).Value
                    
                    
                    For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
                        If Trim(FileCell) <> "" Then
                            If Dir(FileCell.Value) <> "" Then
                                .Attachments.Add FileCell.Value
                            End If
                        End If
                    Next FileCell
                    .Send  'Or use Display
                End With
     
                Set OutMail = Nothing
            End If
        Next cell
     
        Set OutApp = Nothing
    
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    End Sub
    Many thanks in advance

    Rae
    Last edited by raehippychick; 11-19-2008 at 07:33 AM.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259
    Hello Rae,

    I posted this yesterday, but I will repost it again for you.
    'Written: November 17, 2008
    'Author:  Leith Ross
    'Summary: Embeds a hyperlink in the message of an Outlook email.
    
    Sub EmailWithHyperlink()
    
      Dim refLink As String
      Dim MyApp As Boolean
      Dim olApp As Object
      Dim olEmail As Object
      Dim URL As String
      
      'Outlook constants aren't available using late binding
       Const olMailItem = 0
       
        'Open or Start a new instance of Outlook
         On Error Resume Next
           Set olApp = GetObject(, "Outlook.Application")
             If Err = 429 Then
               MyApp = True
               Set olApp = CreateObject("Outlook.Application")
             End If
         On Error GoTo 0
           
         olApp.Session.Logon
         
        'URL to Google
         URL = Chr$(34) & "http://www.google.com/" & Chr$(34)
         
        'Create HTML Hyperlink
         refLink = "<a href=" & URL & ">Link to Google</a>"
         
        'Create an Outlook Mail Item
         Set olEmail = olApp.CreateItem(olMailItem)
           With olEmail
             .To = "LeithRoss@gmail.Com"
             .Subject = "Hyperlink test"
             .HTMLBody = "This message hyperlink " & refLink & " will take you to Google."
             .Send
           End With
                         
       olApp.Session.logoff
       If MyApp Then olApp.Quit
     
    'Release Objects
     Set olApp = Nothing
     Set olEmail = Nothing
     
    End Sub
    Sincerely,
    Leith Ross

  3. #3
    Forum Contributor
    Join Date
    04-28-2004
    Location
    Norwich, England
    MS-Off Ver
    2010
    Posts
    119
    Thank you for that - it works really well, and clicks wonderfully to the correct folder Now I'm going to try and work out how to incorporate it with with my macro which sends files to people from a list in the spreadsheet so I can send files and links together

    Not sure why I missed it yesterday as I always do a search before I post a question, but my PC was being very silly and crashing a lot so it could be that

    Thanks again

    Rae

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1