none
VBA attach pdf to email draft according to pdf file name RRS feed

  • Question

  • Hello guys, 

    Has anyone written a macro that attaches PDF files according to the name of the subject line in the email draft? 

    I have an amazing macro that @Hans Vogelaar helped me with that attaches all the PDF copies from a folder path and I wanted to take this to another level. Right now, the macro attaches all PDF copies saved in a folder and emails it to different recipients based on the spreadsheet. So basically, if the name of the PDF file matches the REF1 column in the spreadsheet then attach it to the email. In screenshot below, there are 3 references. In a separate folder, there will be PDF filenames such as 14344, 25344 and 98755. 

    Thank you so much for your recommendations! 

    Sub LWDSO_EMAIL()
    
    
    'VAR
        Dim strFolder As String
        Dim fso As Object
        Dim fsFolder As Object
        Dim fsFile As Object
        Dim objOL As Object
        Dim objMail As Object
        Dim irow As Long
        Dim endofSheet As Long
    
    '------------------
    'START OF MAIN CODE
    '------------------
    
        ' Get folder path           -ACTIVATE IF USER WOULD LIKE TO SELECT FOLDER
        '------------------
    '    With Application.FileDialog(4) ' msoFileDialogFolderPicker
    '        .Title = "Select the folder that the workbooks are in."
    '        If .Show Then
    '            strFolder = .SelectedItems(1)
    '        Else
    '            MsgBox "Error picking a folder.", vbExclamation
    '            Exit Sub
    '        End If
    '    End With
    
    
    MsgBox ("Enter email address in Column E if you have not")
    
    
        'Hardcode file path
        '------------------
        
         strFolder = "G:\folder\path"
        
    
        '// Create various needed objects.
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set fsFolder = fso.GetFolder(strFolder)
        
        
        Range("E1") = "EMAILED TO"
        
        ' Fill column F
        Range("F1") = "SUBJECT"
        endofSheet = Range("A" & Rows.Count).End(xlUp).Row
        With Range("F2:F" & endofSheet)
            .Formula = "=CONCATENATE(B2,"" "",C2) "
            .Value = .Value
        End With
    
        ' Start Outlook
        Set objOL = CreateObject("Outlook.Application")
        ' Loop through the rows
        For irow = 2 To endofSheet
            ' Create email message
            Set objMail = objOL.CreateItem(0) ' olMailItem
            ' Set properties and display the message
            With objMail
                .To = Range("E" & irow).Value
                .subject = Range("F" & irow).Value
                .Body = "This is a test "
                .NoAging = True
                For Each fsFile In fsFolder.Files
                    If fsFile.Name Like "*.pdf" Then
                        .Attachments.Add strFolder & "\" & fsFile.Name
                    End If
                Next fsFile
                .Display
            End With
        Next irow
    
        ' Clean up
        Set fso = Nothing
        Set fsFolder = Nothing
        Set objOL = Nothing
        Set objMail = Nothing
        
        
    End Sub
    
    

    Friday, January 24, 2020 8:40 PM

Answers

  • Insert the following line just above .Attachments.Add …:

        Debug.Print strFolder & "\" & Range("A" & iRow).Value & ".pdf"

    After the error occurs, look in the Immediate window in the Visual Basic Editor. You should see the path and filename that the code tried to attach.

    What do you see there? Do you notice something unexpected?


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by IamJackie Tuesday, January 28, 2020 4:43 PM
    Saturday, January 25, 2020 8:44 AM

All replies

  • Change the lines

                For Each fsFile In fsFolder.Files
                    If fsFile.Name Like "*.pdf" Then
                        .Attachments.Add strFolder & "\" & fsFile.Name
                    End If
                Next fsFile

    to

                   .Attachments.Add strFolder & "\" & Range("A" & iRow).Value & ".pdf"


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, January 24, 2020 9:19 PM
  • Hello Hans! 

    Thanks for responding to my question again!  

    So I removed this part of the macro: 

    For Each fsFile In fsFolder.Files
                    If fsFile.Name Like "*.pdf" Then
                        .Attachments.Add strFolder & "\" & fsFile.Name
                    End If
                Next fsFile

    Then changed it only to this: 

       .Attachments.Add strFolder & "\" & Range("A" & iRow).Value & ".pdf"

    However I am getting an error: Cannot find this file. Verify the file path and name are correct. 

    The file path and folder are correct though..I even added a .pdf extension on the file name but still getting same error.

    Friday, January 24, 2020 11:19 PM
  • Insert the following line just above .Attachments.Add …:

        Debug.Print strFolder & "\" & Range("A" & iRow).Value & ".pdf"

    After the error occurs, look in the Immediate window in the Visual Basic Editor. You should see the path and filename that the code tried to attach.

    What do you see there? Do you notice something unexpected?


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by IamJackie Tuesday, January 28, 2020 4:43 PM
    Saturday, January 25, 2020 8:44 AM
  • Hello Hans, 

    In the Visual Basic Editor, it just keeps on highlighting the code below. Keeps on saying, "Run-time rror -'2147024894(800700002). Cannot find this file. Verify the path and file name are correct.

    Below screeenshot is what it highlights. My folder path works when I use the previous code. But when i use the .Attachments... code it errors on the below.

    Monday, January 27, 2020 6:07 PM
  • What is displayed in the Immediate window?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, January 27, 2020 8:27 PM
  • On the immediate window, it displays the entire file path which i have been always using and worked in the original code plus the twice the .pdf extension 

    C:\Users\jackie\Desktop\PROJECT_MASTER\OCEAN EXPORT\TEST\5030267269.pdf.pdf

    My strfolder shows exactly like this: 

     strFolder = "C:\Users\jackie\Desktop\PROJECT_MASTER\OCEAN EXPORT\TEST"

    Tuesday, January 28, 2020 4:21 PM
  • Oh I figured what was wrong. My input spreadsheet contains the .PDF ext on Col A. When I removed it, now its working! :D Hans, thanks for your patience! Please consider this question resolved! :D 
    Tuesday, January 28, 2020 4:42 PM
  • Good to hear that! Thanks for the feedback.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, January 28, 2020 5:24 PM