VBA code exporting to PDF but not attaching the file to the email.

Hi,

i am almost there with my code i am trying to PDF a range in excel and attach to an email. I am converting ok and opening up the email but i am missing the attachment and i am now stuck. I would really appreciate if someone could advise me how to adapt the code so it attaches just the range i have specified

Sub Mail_Genius()

    Dim rng As Range
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim OutApp As Object
    Dim OutMail As Object

  
    ActiveSheet.ExportAsFixedFormat xlTypePDF, fn, OpenAfterPublish:=True
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
    Set rng = ActiveSheet.Range("B8:J75")

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    fn = "H:\AA - XXX\XX\XX\XX" & Format(Now(), "DD-MM-YYYY") & ".pdf"
    On Error Resume Next
    With OutMail
        .To = ""
        .CC = ""
        .BCC = ""
        .Subject = Range("d6").Value & " , " & Range("C18").Value & " , " & Range("B2").Value
        .Attachments.Add ActiveSheet.Range("B8:j75")
       
        .Display
    End With
    On Error GoTo 0
  
    Set OutMail = Nothing
    Set OutApp = Nothing

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

Appreciate your help with this.

Hi Norie


That is it, works perfectly. i will digest your comments now so i undertsand.

Thankyou so very much for looking at this and heping.

Hi

Is it possible to add Range ("d6) to the fn?

fn = "H:\XXX\XX\XX " & Format(Now(), "DD-MM-YYYY") & ".pdf"

Sorry one more thing how can i specify the sheet name, just in case another file is open it cannot accidently email the wrong sheet?

Hi Norie,

Thanks for your reply and that makes sense,. I have just noticed it is not saving the file, it is correctly opening up the range as a PDF along. i have checked the file name which is fine, must be something else in the code?

Hi Norie, when the pDF opens up the name is that of the file which is not what i want actually i want it to be the same as the email subject title.

Hi i have worked out the filename :-)

& Range("D6").Text & Format(Now(), "DD-MM-YYYY") & ".pdf"

Thanks