Return FolderPath to add attachment to email

Hi All,

I have code which creates a folder and saves a sheet within the folder as PDF. This works fine but when I try to generate an email with PDF attachment I cant seem to get the folder path to return in my code.

Any help appreciated.

Sub Macro1()
Dim PDF_Name As String
Dim Create_PDF As String
Dim FolderPath As String
Dim NewFolderName As String

Dim OutApp As Object
Dim OutMail As Object

NewFolderName = ActiveSheet.Range("A1").Value

Set objFSO = CreateObject("Scripting.FileSystemObject")

If ActiveSheet.Range("A2").Value = "A" Then

    FolderPath = "C:\" & NewFolderName

    MkDir (FolderPath)

End If

    PDF_Name = (FolderPath & "\" & ActiveSheet.Range("A3").Value)
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDF_Name, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
        
        If Dir(PDF_Name) <> "" Then
        
        Create_PDF = PDF_Name
        
    End If
         
     Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .To = (ActiveSheet.Range("A4").Value)
        .CC = ""
        .BCC = ""
        .Subject = (ActiveSheet.Range("A5").Value)
        .Body = "Text Here"
        .Attachments.Add Create_PDF 'This is where I have the problem
        .Display   'or use .Send
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub

Originally Posted by JosephP

I guess your A3 in reality does not include the ".pdf" extension which gets added when the export to pdf is done. hence your Dir fails and Create_PDF never gets a value. either specify the .pdf in the cell or add it in the code.

Doh!

Thanks JP