Been trying for some time now but cannot get this to work..
Looking for the following:

Create an email containing:

1. To: emails stored in tab “info” range M2 until empty line
2. CC: hardcoded email address
3. Subject: Overview – ‘Dateoftoday’
4. Body:
   “Dear all,

Please find below overview of today.
Click this link to open the file” <- the word ‘Link’ should hyperlink to currentworkbook path

Screenshot of sheet “Status” range B2:W62

Signature setup in the user’s email

I don’t want the email to automatically send, i’d like the end users to be able to check everything first
Any push in the right direction would be highly appreciated!!

Current code i have will be listed below, however, i cannot get a hyperlink in this code no matter how hard i google it..

Sub Mail()
If ActiveSheet.Name <> “Status” Then
MsgBox “This macro can only be executed from the Status sheet!”
Exit Sub
End If

Dim Ol As Object 'Outlook.Application
Dim Olemail As Object 'Outlook.MailItem
Dim Olinsp As Object 'Outlook.Inspector
Dim Wd As Object 'Word.Document
Dim Maillist As String

Application.ScreenUpdating = False

Dim LastMember As Long
LastMember = Worksheets("Info").Cells(Rows.Count, 13).End(xlUp).Offset(0).Row

For Each cell In ActiveWorkbook.Sheets("Info").Range("M2:M" &LastMember).Cells.SpecialCells(xlCellTypeVisible)
    If cell.Value Like "*" Then
        Maillist = Maillist & ";" & cell.Value
    End If
Next

Set Ol = GetObject(, “Outlook.Application”) ‘/* if outlook is running, create otherwise */
Set Olemail = Ol.CreateItem(0) ‘olMailItem

With Olemail

.To = Maillist

‘ .CC = “HARCODED EMAIL”

Set Olinsp = .GetInspector
If Olinsp.EditorType = 4 Then 'olEditorWord
    Set Wd = Olinsp.WordEditor
End If

    If ActiveSheet.Name = "Status" Then
    Wd.Paragraphs(1).Range.InsertBefore "Dear all," & Chr(10) & Chr(10) & "Please find below overview of today." & Chr(10) & Chr(10)
    
    Sheets("Status").Range("B2:W62").SpecialCells(xlCellTypeVisible).Copy
    
    .Subject = "Overview - " & Date
    End If

Wd.Paragraphs.Add
‘ wd.Paragraphs(4).Range.Characters.Last.PasteAndFormat 13 & Chr(13) & Chr(13)
Wd.Paragraphs(4).Range.Characters.Last.PasteAndFormat 13

With Wd
    .InlineShapes(1).Height = 800
End With

 .Display

End With

Application.ScreenUpdating = True

End Sub