Hi all,

Long time lurker, first time poster, so be gentle! I'm looking for a VBA code that will protect the entire workbook, then create an email in Outlook with the protected workbook attached (like with the Save & Send option) and pre-specified text in the email header and email body (depending on defined names within the workbook)... Let me have a go at explaining...

In my workbook I have various boxes and dropdown menus that need to be filled in before the email is allowed to be sent. The current names I have defined which need to be populated are:

_Recipient_Name
_Email
_ClientName
_Reference
_Product
_ProductNumber
_Email_Subject - this is a combination of the various other inputs above, plus others

So far I have this VBA code which is close but attaches the unsaved, unprotected workbook (i.e. with no changes since last opened). Any help would be massively appreciated!

Formula: copy to clipboard 
Sub Email_Lead()

 

    Dim RNG As Range

    Dim OutApp As Object

    Dim OutMail As Object

    Dim StrBody As String

    

    StrBody = "Dear " & Range("_Recipient_Name").Value & "," & "<br><br>" & "Please see attached the Form for " & Range("_ClientName").Value & " Client #" & Range("_Reference").Value & " " & Range("_Product").Value & " " & Range("_ProductNumber").Value & "." & "<br><br>" & _

    "Text in the email to be added here" & "<br><br>" & _

    "Many thanks"

    

    Set RNG = Nothing

    On Error Resume Next

    'Only the visible cells in the selection

    Set RNG = Sheets("General Specifications").Range("A1:O168").SpecialCells(xlCellTypeVisible)

    On Error GoTo 0



    With Application

        .EnableEvents = False

        .ScreenUpdating = False

    End With



    Set OutApp = CreateObject("Outlook.Application")

    Set OutMail = OutApp.CreateItem(0)

    

    On Error Resume Next

    With OutMail

        .to = Range("_email").Value

        .CC =         

        .BCC = ""

        .Subject = Range("_Email_Subject").Value

        '.HTMLBody = StrBody & RangetoHTML(rng)

        .Attachments.Add ActiveWorkbook.FullName

        .HTMLBody = "<p style='font-family:calibri;font-size:15'>" & StrBody & "</p>" & HTMLBody

        .Display   'or use .Send

    End With

    On Error GoTo 0



    With Application

        .EnableEvents = True

        .ScreenUpdating = True

    End With



    Set OutMail = Nothing

    Set OutApp = Nothing



End Sub


My VBA skills are pretty weak (Only been doing it for a few weeks and most info I've found has been online so I'm not 100% sure how it all works) so any help - as simple as possible - that can be given would be awesome!

Cheers,

Mogles