Change code from Sheet to Range

[ExcelRoy]

Good Evening,

Could I please ask for some help. I have used ZVI's code from the following thread

https://www.mrexcel.com/forum/excel...de-convert-excel-pdf-email-attachment-14.html

Which works perfectly, but I would like it to email a range B2:B200 instead of the complete sheet

I am not sure where it would go?

Sub Email_Range()
  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("AK5")
 
  ' 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 = Title
    .To = "nhol@al.com" ' <-- Put email of the recipient here
    .CC = "nhol@al.com" ' <-- Put email of 'copy to' recipient here
    .Body = "Please find attached your Parts Status Report" & vbLf & vbLf _
          & "This is an automated email" & vbLf & vbLf _
          & Application.UserName & 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", 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

Many thanks for any help

 

[CalcSux78]

https://www.rondebruin.nl/win/s1/outlook/amail4.htm

 

[ExcelRoy]

Thanks Calcsux78, but I don't understand all the code?

 

[ExcelRoy]

I was hoping it was easy to change the selection to a range rather than the complete sheet

 

[ExcelRoy]

Hi,
Am I looking in the correct place by assuming the following part of the code needs to be changed

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

I am thinking he activesheet needs to be changed to range maybe?

Thanks