Results 1 to 3 of 3

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

Threaded 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.

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