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
Bookmarks