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

woah

New Member
Joined
May 10, 2023
Messages
24
Office Version
  1. 2016
Platform
  1. Windows
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.

Save and Email.png
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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.


VBA Code:
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
 
Last edited:
Upvote 0
I haven't tested the line break "<br>" as I don't have Outlook.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top