Add Command of cell selection range to VBA

[NVRensburg]

Hi there

Please could someone assist me with adding a range to this macro. I've been using this for years, but now I need to add a command which only emails a selected range of cells (namely I1:V14) so it doesn't send the whole sheet. I can't select a print area as I have 2 ranges of cells I want to email separately, and I've tried adding some commands, but without success. Thanks so much, I appreciate the valued assistance!

Sub AttachActiveSheetPDF()
Dim IsCreated As Boolean
Dim i As Long
Dim PdfFile As String, Title As String
Dim OutlApp As Object

' Not sure for what the Title is
Title = Range("$T$2")

' Define PDF filename
PdfFile = ActiveWorkbook.FullName
i = InStrRev(PdfFile, ".")
If i > 1 Then PdfFile = Left(PdfFile, i - 1)
PdfFile = PdfFile & "_" & ActiveSheet.Name & ".pdf"

' Export activesheet as PDF
With ActiveSheet
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With

' Use already open Outlook if possible
On Error Resume Next
Set OutlApp = GetObject(, "Outlook.Application")
If Err Then
Set OutlApp = CreateObject("Outlook.Application")
IsCreated = True
End If
OutlApp.Visible = True
On Error GoTo 0

' Prepare e-mail with PDF attachment
With OutlApp.CreateItem(0)

' Prepare e-mail
.Subject = Range("$T$2")
.To = Range("$L$14")
.CC = "testemail@gmail.com" ' <-- Put email of 'copy to' recipient here
.Body = "Hi there," & vbLf & vbLf _
& " " & vbLf _
& "Please find attached your roster in PDF format." & vbLf & vbLf _
& "Should you have any issues, or changes, or is something doesn't look quite right, please do not hesitate to contact me." & vbLf _
& " " & vbLf _
& "Kind Regards," & vbLf _
& "Test Text," & vbLf _
& "Test text," & vbLf _
& " " & vbLf & vbLf
.Attachments.Add PdfFile

' Try to send
On Error Resume Next
.Send
Application.Visible = True
If Err Then
MsgBox "E-mail was not sent", vbExclamation
Else
MsgBox "E-mail successfully sent, **HAVE A NICE DAY**", vbInformation
End If
On Error GoTo 0

End With

' Delete PDF file
Kill PdfFile

' Quit Outlook if it was created by this code
If IsCreated Then OutlApp.Quit

' Release the memory of object variable
Set OutlApp = Nothing

End Sub

 

[sykes]

' Export activesheet as PDF
With ActiveSheet.Range("I1:V14 ")
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With

 

[sykes]

.....personally, I prefer to audit any document I'm going to email - before I send it out.
This will open the document, and allow the user to view it, then give them a yes/no option. Yes = continue with the code, No = don't (don't send email).

' your previous code here...

With ActiveSheet.Range("I1:V14")
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End With

If MsgBox("Are you happy with the document?", 35, "PDF OK?") = vbNo Then Exit Sub

End Sub
' ...rest of your code here

 

[NVRensburg]

Thank you so much for your help! That works!!

 

[sykes]

Hurrah!
Pleasure, and thanks for the feedback.

 

[NVRensburg]

Hi everyone, how can I edit this code to open the email and attachment in Outlook so I can add some text/attachments and then send it myself?