Trying to automatically add attachments to emails

[MasterBash]

Hello,

Cross-posted here : https://www.excelforum.com/excel-pro...o-clients.html

I am attaching my workbook as an example of what I am trying to do :
testemail_2.xlsm

I am trying to automatically add attachments to my e-mails, depending on the client.

Column D are the e-mail address of the clients, E is the path to the folder, F is a sub-folder that contains the month and it is dynamic, so in august, the folder name will change to august. G is the date and it is dynamic too, so tomorrow it will be 28 and H is the file name.
I could've used only one cell per column for column E, F and G, as they all share the same path for now... I may do that in the future, but for now my priority is to get the script to work.

It works, as long as I use the entire file name, which is saved under ClientnameDateCarrierLoadnumber.pdf (I used .docx just to test the script). Problem is, I wish to use an asterix (*) and just use something like PATAGONIA* and attach all files starting with PATAGONIA because the date part will be changing everyday, but I am unable to get it to work that way.
If I use the dir function, it can't find the path or file name.

The files are stored on OneDrive.

Is it possible for me to do that ?

[June7]

Would have to loop through files in folder and have IIf condition to check for PATAGONIA string in name and attach if found.

Or concatenate current date into path and file name.

Why is there no header row?

[MasterBash]

There is no header row because it was copied off an old sheet without any formula or VBA, only 2 columns so we didn't see the use for headers, so we did not add any., but since then we added a lot to the sheet, so maybe we should've.

I don't think adding the header row will affect anything. I will just have to change 2 numbers in module so it doesn't turn the first row into a link but the code should still work.

As for the path, I tried using the direct path c:\... And exact file name inside dir function and it still couldn't find the file, so if it can't find anything... I am not sure how I can create a loop if it can't find the path and/or file.

[jdelano]

To find all the files that meet a specific filespec you can simply do a loop, see the attached screenshot where I'm looking for all files that start with Report and are a comma separated value file.

    Dim fileNameToFind As String
    fileNameToFind = Dir("C:\temp\Report*.csv")
    
    Do While Len(fileNameToFind) > 0
        Debug.Print fileNameToFind
        fileNameToFind = Dir()
    Loop

[June7]

Show your code using Dir.

If it doesn't find file, then path must be wrong.

[MasterBash]

Option Explicit
    Dim subject As String
    Dim body As String
    Dim email As String
    Dim emailcc As String
    Dim id As String
    Dim folder As String
    Dim month As String
    Dim day As String
    Dim fileformat As String
    Dim attachment As String
    Dim fileNameToFind As String

Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
'If Range(Target.SubAddress).Column = 2 Then
    'email = Range("D" & Range(Target.SubAddress).Row).Value
    'subject = Range("I1") & Range("K1")
    'body = Range("K2")
    'fileformat = "*.docx"
    'id = Range("H" & Range(Target.SubAddress).Row).Value
    'folder = Range("E" & Range(Target.SubAddress).Row).Value
    'folder = Range("E1")
    'month = Range("F" & Range(Target.SubAddress).Row).Value
    'month = Range("F1")
    'day = Range("G" & Range(Target.SubAddress).Row).Value
    'day = Range("G1")
    'attachment = Dir(folder & month & day & id & "*")
    fileNameToFind = Dir("C:\test\report*.docx")
        Do While Len(fileNameToFind) > 0
        Debug.Print fileNameToFind
        fileNameToFind = Dir()
    Loop
    'Call Send_The_Emails
'End If


End Sub

This works
works.jpg

And inside the email script (and I removed the ' for email, subject and body from the previous code)

Sub Send_The_Emails()


    'Dim emailRange As Range
    Dim WordDoc As Word.Document
    Dim para As Long, paraTotal As Long
    Dim OApp As Object
    Dim OMail As Object


    
    'Excel range to be copied and pasted into the email body
    
    'Set emailRange = ThisWorkbook.Worksheets("email").Range("K3:P9")
    
    'Get active Outlook instance, if any
    
    On Error Resume Next
    Set OApp = CreateObject("Outlook.Application")
    If Err.Number = 429 Then
        'Not found, so create new Outlook instance
        Err.Clear
        Set OApp = New Outlook.Application
    End If
    On Error GoTo 0
            
    'Create new email
    
    Set OMail = OApp.CreateItem(0)
    
    With OMail
        .SentOnBehalfOfName = "me@me.com"
        .To = email
        .subject = subject
        .Attachments.Add fileNameToFind
        .Display
        Set WordDoc = .GetInspector.WordEditor
           
        paraTotal = WordDoc.Paragraphs.Count
        para = 0
        
        'Insert paragraph(s) above the Excel range


        With WordDoc.Paragraphs(1)
            .Range.InsertBefore "Hello," & vbCr & vbCr & _
                                 body & vbCr & vbCr
        End With
        
        para = para + 1 + WordDoc.Paragraphs.Count - paraTotal
        paraTotal = WordDoc.Paragraphs.Count
            
        'Copy and paste Excel range into email body
        
        'With WordDoc.Paragraphs(para)
           ' emailRange.Copy
            '.Range.Paste  'as editable table
            '.Range.PasteAndFormat Type:=wdChartPicture  'or as image
            '.Range.InsertParagraphAfter
        'End With
        
        para = para + 1 + WordDoc.Paragraphs.Count - paraTotal
        paraTotal = WordDoc.Paragraphs.Count


        'Insert paragraph(s) below the Excel range
        
        'With WordDoc.Paragraphs(para)
           ' .Range.InsertBefore Range("S3") & vbCr
        'End With


        '.Send  'send the email immediately
        
    End With
    
    Application.CutCopyMode = False
    
End Sub

So .Attachments.Add fileNameToFind , seems correct, yes ?

In this case, this is error message that I get :

notworking.jpg

I know I made a mistake somewhere, but I can't find it.

If I had the loop code under Send_the_emails... I get

notworking2.jpg

So, although it does find the files, I still get an error. Outlook does not even open whenever I get an error.

[Paul_Hossler]

I'm going to guess that you need the full path on the attachment otherwise it is probably looking in what ever the active folder is

.Attachment.Add = "C:\Test\" & FileToFindToFind

[MasterBash]

.Attachment.Add = "C:\Test\" & FileNameToFind

Operation Failed error

.Attachment.Add "C:\Test\" & FileNameToFind

notworking3.jpg



With all those errors, I am unable to get anything attached to my e-mails unless I have the direct path to the file (no *) and even then, that works only without Dir.

Maybe Dir is not the way to go for me, as I would need to make it dynamic afterward to make sure it covers all clients. So I need a dynamic path and a dynamic file name. Hmm...
Similar to how the rest of the workbook is created.

Sub test()
    fileNameToFind = Dir(folder & month & day & id)
        Do While Len(fileNameToFind) > 0
        Debug.Print fileNameToFind
        fileNameToFind = Dir()
    Loop
End Sub

Returns everything in my Documents folder... When it should return all files with C:\test\july\29\report*.

[MasterBash]

Just a thought... When I hover Attachments.Add fileNameToFind, I do get a file name for fileNameToFind from the correct folder. However, I get the error when I click on Send e-mail. Is it possible that Attachments.Add doesn't like the way that some variables in there ? Even then, it should still work with the direct path... But it could explain why Dir returns "" when I use variables instead of the direct path to the file.

[MasterBash]

Small update... And I would like to apologize in advance for multiple replies, but I am unable to edit and I want to keep people up to date with my progress on this...

I got Dir to find the file, by changing regional language. It works with the Debug.Print (I can see in the immediate window) and I can see it while hovering the variable.
So what I am trying to do Dir(folder & month & day & id & fileformat) is now found.

attachment = Dir(folder & month & day & id & fileformat)

works.jpg

So whatever I write in column H, that has part of the file name, now works.

I did not add the loop yet, because I would like to fix the email error first.

Problem is... I still get an error file not found, only when clicking the e-mail address to send the file.

notworking.jpg

Is it possible that attachments.add is unable to read the variable, maybe because it is "As String" ? Maybe because some of those variables inside Dir have range values like

Range("H" & Range(Target.SubAddress).Row).Value

So I can see it with the debug.print, but attachments.add is unable to do anything with this ?

I don't know how to go from there to troubleshoot this issue.

[June7]

Well, attachment variable has file name but does not include file path. Need full file path.

[MasterBash]

A simple attachment = Dir("C:\test" & "*") sees the file name, but provides no path when I hover the variable.

But if debug.print and variable can find the correct file name in the folder, why doesnt it have the full path once i use it with attachments.add ?

attachment = Dir("C:\test\report123.docx") only gives me the file name and not the full path.

When I don't use Dir, it sees the full path, but then again, I can't use it the way I would like to (with variables that point to dynamic cells).

I am not sure why it is acting like this ?

[jdelano]

What you need to do is have a way of saving each file that matches and loop that in the send the emails code:

I changed the attachments string variable for

Dim filesToAttach As Scripting.Dictionary

then this

Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    If Range(Target.SubAddress).Column = 2 Then
        email = Range("D" & Range(Target.SubAddress).Row).Value
        subject = Range("I1") & Range("K1")
        body = Range("K2")
        fileformat = "*.docx"
        id = Range("H" & Range(Target.SubAddress).Row).Value
        folder = Range("E" & Range(Target.SubAddress).Row).Value
        month = Range("F" & Range(Target.SubAddress).Row).Value
        day = Range("G" & Range(Target.SubAddress).Row).Value
        'attachment = folder & month & day & "\" & id
        
        Dim fileNameToFind As String
        Dim folderForFiles As String
        
        ' build the file specification to search for
        folderForFiles = folder & month & day
        fileNameToFind = Dir(folderForFiles & "PATAGONIA*.*")
        
        ' find all the files that match, place the full name in a dictionary
        ' for the send email sub to use to attach them
        Set filesToAttach = New Scripting.Dictionary
        
        Do While Len(fileNameToFind) > 0
            Debug.Print fileNameToFind
            filesToAttach.Add filesToAttach.Count + 1, folderForFiles & fileNameToFind
            fileNameToFind = Dir()
        Loop
        
        Call Send_The_Emails
    End If
    
End Sub

Added this to Send_The_Emails:

Dim dictItemIndex As Integer

and changed the attachment process to

        For dictItemIndex = 1 To filesToAttach.Count
            .attachments.Add filesToAttach.Item(dictItemIndex)
        Next dictItemIndex

[Paul_Hossler]

But if debug.print and variable can find the correct file name in the folder, why doesnt it have the full path once i use it with attachments.add ?

attachment = Dir("C:\test\report123.docx") only gives me the file name and not the full path.

Because that's what Dir() does -- just returns the file's name

If you know that report123.docx exists (probably because of running Dir() loop) then don't use Dir() with .Add, just use

Attachment.Add = "C:\test\" & Filename

[MasterBash]

Thank you jdelano !! It works amazingly well on my home pc. I will be testing it out next week and provide an update, as those files are on OneDrive, as mentioned in my initial post. I will make sure Mark this thread as solved once I am able to test it out. I thought I was getting closer to a solution but I was still far away.

Thanks everyone. I really appreciate the help. Paul, thank you for the explanation, it clears things up. Unfortunately, those folders and file names are dynamic, so I couldn't use a direct path. Everything is a new folder and everything is a new file under a different date.

I really hope this is going to work with OneDrive.

[jdelano]

As long as you have a local folder that is sync'd w/OneDrive it should be fine.

[MasterBash]

Thank you, it works !

folderForFiles = environ("USERPROFILE") & folder & month & day

I had to add environ for a shared OneDrive, and obviously change the folder path.