VBA to add all files in a folder to an email

[snirben]

i have trying to send emails to a list of address and add the entire pdfs in a folder,

the code works the problem i have is that the files can be add only if i put a full path.

i want it to go to D and get all the pdfs or files from the folder and create email.

so again everything works except the attachments need a full path, but i just want to put the path of the folder and it will take all the files from it,
thank you.

Sub massemailsend()



    Dim objOutlook As Object
    Dim objMail As Object
    Dim ws As Worksheet
    


    Set objOutlook = CreateObject("Outlook.Application")
    Set ws = ActiveSheet


  For Each cell In ws.Range("A1:A70")


    Set objMail = objOutlook.CreateItem(0)


        With objMail
            .To = cell.Value
            .Subject = cell.Offset(0, 1).Value
            .Body = cell.Offset(0, 2).Value
            '\\Attacment from here
            .Attachments.Add cell.Offset(0, 3).Value
            .Display
        End With


        Set objMail = Nothing
    Next cell


    Set ws = Nothing
    Set objOutlook = Nothing


End Sub

 

[snirben]

i found this code but i dont know how to integrate it to my code. can someone help me?

F[COLOR=#101094][FONT=inherit]unction[/FONT][/COLOR][COLOR=#303336][FONT=inherit] GetFileList[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]FileSpec [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]String[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Variant[/FONT][/COLOR]<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">[COLOR=#858C93][FONT=inherit]'   Returns an array of filenames that match FileSpec[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#858C93][FONT=inherit]'   If no matching files are found, it returns False[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] FileArray[/FONT][/COLOR][COLOR=#303336][FONT=inherit]()[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Variant[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] FileCount [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Integer[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] FileName [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]String[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]On[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Error[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]GoTo[/FONT][/COLOR][COLOR=#303336][FONT=inherit] NoFilesFound

    FileCount [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]0[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    FileName [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Dir[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]FileSpec[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit] FileName [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]""[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Then[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]GoTo[/FONT][/COLOR][COLOR=#303336][FONT=inherit] NoFilesFound

    [/FONT][/COLOR][COLOR=#858C93][FONT=inherit]'Loop until no more matching files are found[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Do[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]While[/FONT][/COLOR][COLOR=#303336][FONT=inherit] FileName [/FONT][/COLOR][COLOR=#303336][FONT=inherit]<>[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]""[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
        FileCount [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] FileCount [/FONT][/COLOR][COLOR=#303336][FONT=inherit]+[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]1[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
        [/FONT][/COLOR][COLOR=#101094][FONT=inherit]ReDim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Preserve[/FONT][/COLOR][COLOR=#303336][FONT=inherit] FileArray[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]1[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]To[/FONT][/COLOR][COLOR=#303336][FONT=inherit] FileCount[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
        FileArray[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]FileCount[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] FileName
        FileName [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Dir[/FONT][/COLOR][COLOR=#303336][FONT=inherit]()[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Loop[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    GetFileList [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] FileArray
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Exit[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Function[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

    [/FONT][/COLOR][COLOR=#858C93][FONT=inherit]'   Error handler[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
NoFilesFound[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    GetFileList [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]False[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR]</code>[COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Function[/FONT][/COLOR]