Attaching files to Email from Excel via VBA

[weaselmcguff]

Hello,

I am trying to attach multiple files to an automatically generated email. I know what 1 of the files names is going to be. But I am not sure if there are others files and what the names are going to be. I do know where they are located. I want all the files in that sub directory to be attached to the email.

I know how to do multiple files IF I know the names. But in this case I will not know if there are any other files and if there are wont know the name. I do know that they all will have the .JPG extension.

Here is my code for the sending of the email.

With OutMail
If email2 = "" Then
email2 = Application.InputBox("Please enter Email To Automatically Email this To:")
Worksheets("sheet2").Range("a45").Value = email2
Else
.To = email2 ' Who it is going to
.CC = email3 'CC to who it is going to
.Subject = subject1 ' sugbject of email
.Body = msgbody 'template for the body of the email
.Attachments.Add dir2 & thisfile & ".xls" 'dir2 is location where file is. thisfile is name and .xls extension
.Attachments.Add dir2 & "2.jpg" 'here adding the 2.jpg file from the dir2 location
'.Attachments.Add ActiveWorkbook.FullName
.Send 'or use .Display
End If
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing

I can not figure out how to add multiple if any .jpg files to the email

Any suggestions.

Thanks for your time

 

[Domenic]

I haven't really looked through the rest of your code, but to loop through each .jpg file in the specified folder and attach it to the email, first declare the following variable...

Dim strFile as String

Then, try replacing...

.Attachments.Add dir2 & "2.jpg"

with

strFile = Dir(dir2 & "*.jpg")

Do While Len(strFile) > 0
    .Attachments.Add dir2 & strFile
    strFile = Dir
Loop

 

[weaselmcguff]

I did the changes it is bombing at:

strFile = Dir(dir2 & "*.jpg")

giving me the error:

Compile Error:

Expected Array

Im lost

 

[Domenic]

Dir2 should hold the path (ending with a backslash) to your folder. What value has Dir2 been assigned?

 

[weaselmcguff]

dim dir2 as string

dir2 = Worksheets("data").Range("i8").Value & "\" ' Directory to where it is being saved

this is what dir2 is set to

I get the error with the "dir" part of it

strFile = Dir(dir2 & "*.jpg")

 

[Domenic]

Unfortunately, I see no reason why you would get that error. Maybe the error stems from something else. Which line of code gets highlighted when the error occurs (try stepping through the code, line by line, using F8)?

 

[weaselmcguff]

I tried stepping through the whole code and I error out with the same error at the:

strFile = Dir(dir2 & "*.jpg")

that is what is confusing me. It should be giving me an error. All my variables are defined.

i have not defined the DIR as a variable anywhere does that matter?

strFile = Dir(dir2 & "*.jpg")

Do While Len(strFile) > 0
.Attachments.Add dir2 & strFile
strFile = Dir
Loop

 

[Domenic]

Actually, DIR is a function, not a variable...