Hello, I have a code someone helped me with which will save a copy of a template of information as a new file name in a specific folder and then attach that file to an email. I'm looking to make a slight change: Instead of attaching the newly named file to the email, I'd really like to just copy a set range of cells from the file and have that go into the body of the email. The code I currently have is as follows:
So the range that I would want in the body of the email is always the same: D10:G26
How might I integrate this into the code and replace the attachment with that?
VBA Code:
Sub SaveEmailSheet()
Dim Name As String
Dim VacFile As Workbook 'This will be VACATION CHANGES File
Dim NamedVacFile As Workbook 'This will be the file Vacation Changes data gets pasted to
With Application
.EnableEvents = False
.ScreenUpdating = False
.DisplayAlerts = False
End With
ActiveWorkbook.Save
Set VacFile = Workbooks("VACATION CHANGES.xlsm") 'Defining the Dim VacFile Variable
VacFile.Worksheets("VACATION").Range("A1:AC26").Copy 'Copying data from Vacation changes file
Set NamedVacFile = Workbooks.Add 'Creating a new workbook
NamedVacFile.ActiveSheet.Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste 'Pasting data from Vacation Changes to this file
ActiveWindow.DisplayGridlines = False
Range("A1").Select
Name = "\\specific folder locations\" & _
Range("E12").Value & " " & Format(Range("A2").Value, "mm-dd-yy") & " To " & Format(Range("A3").Value, "mm-dd-yy") & ".xlsx"
'Defnining the Name as String variable and telling it where we want it to save a file and what name we want to give it
NamedVacFile.SaveAs Filename:=Name, FileFormat:=51 'Saving our new workbook as the specified name and in the specified folder
EmailForm 'Calling our EmailForm subprocedure to email a copy of the newly named file
ActiveWorkbook.Close
VacFile.Activate 'Close the newly named file and return to the Vacation Changes template
With Application
.EnableEvents = True
.ScreenUpdating = True
.DisplayAlerts = True
End With
End Sub
Sub EmailForm()
ActiveWorkbook.Save
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.Display
.HTMLBody = "<BODY style=font-size:11pt;font-family:Calibri>" & ".<br>" & "</BODY>" & .HTMLBody '<-- enter message body here"
.Attachments.Add Application.ActiveWorkbook.FullName
.To = "emails entered here"
.CC = "CC emails entered here"
.BCC = ""
.Subject = "Vacation Change " & Range("E12").Value '<-- enter subject here
.Body = "Please see the attached vacation change for " & Range("E12").Value '<-- enter message body here
'.Display 'or use .Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
So the range that I would want in the body of the email is always the same: D10:G26
How might I integrate this into the code and replace the attachment with that?