Trying to automatically add attachments to emails

[MasterBash]

Hello,

Cross-posted here : https://www.excelforum.com/excel-programming-vba-macros/1426719-automatically-send-e-mail-to-clients.html

I am attaching my workbook as an example of what I am trying to do :
31729

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
31731

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 :

31732

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

31733

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

31734

:(

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)

31735

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.

31736

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.