Use VBA to save as a New Workbook and Email it as attachment

[woah]

Sorry guys, I might ask similar question, but I have more requirement this time. I need a VBA to do the following tasks.

1. Only save part of the worksheet (List) with a new workbook. Only the area highlighted in yellow need to save as a new workbook, start from the Cell A13. However, the range sometimes contain more or less data, and sometimes with blank space. So, I would like to only extract up to the last row of Column D (i.e., Column Name).
2. And save the new workbook in a specific folder with a specific name (=Cell B3) and password to open.
3. Email the new workbook. However, the subject and body of the email will change according to some cells (i.e., cell B4, cell B5, and cell B6 (please refer to the picture).

Please kindly advise. Thank you very much.

 

[PureBluff]

Stolen from Ron de Bruin - Mail Range or Selection

Please note, you referenced cell B5/6, I used B4/5 respectively as B6 is blank in your example.

Sub Mail_Range()

'Working in Excel 2000-2016
'For Tips see: Excel Automation - Ron de Bruin
    Dim Source As Range
    Dim Dest As Workbook
    Dim wb As Workbook
    Dim FilePath As String
    Dim FileName As String
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim OutApp As Object
    Dim OutMail As Object
    Dim pwd As String
    Dim LastRow as Long

   pwd = "YourPassWordHere" 'set the password to protect the workbook later
 
   With ActiveSheet
        LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row 'find the last used row in Col.D
    End With

    Set Source = Nothing
    On Error Resume Next

    'Set Source = Range("A1:K50").SpecialCells(xlCellTypeVisible) ' Ron's code
    Set Source = Range("A13:F" & LastRow)      ' set range from A13 to F<last row in D with data>

    On Error GoTo 0



    If Source Is Nothing Then
        MsgBox "The source is not a range or the sheet is protected, please correct and try again.", vbOKOnly
        Exit Sub
    End If

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Set wb = ActiveWorkbook
    Set Dest = Workbooks.Add(xlWBATWorksheet)

    Source.Copy

    With Dest.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial Paste:=xlPasteValues
        .Cells(1).PasteSpecial Paste:=xlPasteFormats
        .Cells(1).Select
        Application.CutCopyMode = False
    End With

    FilePath = "C:\Users\PureBluff\Desktop\Folder\" 'Set your desired folder here
    FileName = Range("B3").Value 'Take the contents of cell B3 as the filename

    If Val(Application.Version) < 12 Then
        'You use Excel 97-2003
        FileExtStr = ".xls": FileFormatNum = -4143
    Else
        'You use Excel 2007-2016
        FileExtStr = ".xlsx": FileFormatNum = 51
    End If



    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    With Dest
       .SaveAs FilePath & FileName & FileExtStr, FileFormat:=FileFormatNum, Password:=pwd
        On Error Resume Next

        With OutMail
            .to = "ron@debruin.nl" 'specify recipients
            .CC = ""
            .BCC = ""
            .Subject = "List for " & Range("B4").Value & "place " & Range("B5").Value & " " & Range("B4").Value
            .HTMLBody = "Please be informed that " & Range("B4").Value & "will start in " & Range("B5").Value & "from " & Range("B6").Value & "<br>" & "Thank you"
            .Attachments.Add Dest.FullName
            'You can add other files also like this
            '.Attachments.Add ("C:\test.txt")
            .Send   'or use .Display
        End With

        On Error GoTo 0
        .Close savechanges:=False
    End With

  '  Kill TempFilePath & TempFileName & FileExtStr

    Set OutMail = Nothing
    Set OutApp = Nothing

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

End Sub

 

[PureBluff]

I haven't tested the line break "<br>" as I don't have Outlook.