Attach multiple files from a directory folder to email .pdf & .jpg or other image files

[Hamish 237]

Hi all im sure there is posts out there that cover this but everything i found was very old or seemed overly complex so i thought id ask the question fresh.
My Case file i have a simple database that stores vehicle data and repair history new repairs are documented and the report / workcard is stored in a subfolder under the respective vehicle main folder Application.CurrentProject.Path & "\" & Me.VHID.Column(1) & "\" & "Fault" & "\" & Me.CLID the report is stored as a pdf in this folder named the same as the folder along with images of the fault that was repaired
Where the vehicle is still in its warranty period this information is then forwarded to the supplier as a warranty claim submission,
I cant control the names of or the file type of image being saved. so i'm looking for a "wild card" way to attach the PDF and the jpg / bitmap/mpeg etc and if possible open up the default windows compress images to help the users control the size of images being sent
Currently the DB opens an email with the report attached as a PDF then the user has to save the email as a draft first then navigate to the folder to drag and drop the pictures in to the draft email
I did find some coding to loop through files in the folder that i think would have helped with the below but i just wasn't sure how to combine it I did get the below to work with just a single PDF attachment from the folder

PS
go gentle on me people i'm no programmer i just enjoy learning new stuff and getting satisfaction from making things work

' PROGRESS SO FAR
'------------------------------------------------------------
' emlclm_Click
'
'------------------------------------------------------------
Private Sub emlclm_Click()
On Error GoTo emlclm_Click_Err

Dim objOutlook As Object
Dim objMail As Object
Dim Filepath As String

Filepath = Application.CurrentProject.Path & "\" & Me.VHID.Column(1) & "\" & "Fault" & "\" & Me.CLID

Set objOutlook = CreateObject("Outlook.Application")

Set objMail = objOutlook.CreateItem(0)

With objMail
.To = "Her@him.com.au"
.Subject = "Test"
.Body = "Hope this works"
'\\Attacment from here
'THIS IS WHERE I THINK I REALLY NEED THE HELP "
.Attachments.Add Filepath & "\" & "*.PDF"
.Attachments.Add Filepath & "\" & "*.jpg"
.Display True
End With


Set objMail = Nothing

Set objOutlook = Nothing

emlclm_Click_Exit:
Exit Sub

emlclm_Click_Err:
MsgBox Error$
Resume emlclm_Click_Exit

End Sub

 

[theDBguy]

Hi. The Attachments.Add method expects one file (not multiple), so using a wildcard will probably not work. Are you really trying to attach all JPG and PDF files from a folder into your email? My guess is probably not. What do you mean by you can't control the name of the file? Would it be an option to let the user "pick" which file(s) to attach to the email?

 

[Hamish 237]

Hi Again thanks for the help earlier to yes Picking the files would be a workable option though hopefully they will have only placed the required files in the folder in the first place

 

[vba_php]

so i'm looking for a "wild card" way to attach the PDF and the jpg / bitmap/mpeg etc

Hamish,

you can do this many ways, actually. do you know what the File Scripting object is? it is usually declared as the string "FSO" in VBA by the pros. this can let your uses select multiple files in a popup window, regardless of extension. and after they do that, you can simple write a FOR, WHILE, or DO loop to attach each file selected to the email object (which is what GUY was telling you). DBguy is right, you can only add 1 file at a time, but that doesn't mean you *can't* add more than 1 file to any given email message. alternatively, if you don't want your users selecting files because they might mess it up, you can get an input specification from them anyway you want, and then take control by writing the code that attaches all the files they want for them through automation (vba code). That is pretty easy as well. Allen Browne's vba code example can be easily modified to do that.

 

[Hamish 237]

Thanks All, Selecting the Files was a great Idea I managed to get this to work great Code below which should be much quicker for the users
but is there anyway i could add script to control any selected image file size before its attached
like windows would normally do if your selecting image files to send.. noting I'm selecting a PDF as well

The code below works great
Just images are all still full size which would block emails in most servers

Private Sub SNDCLM_Click()
On Error GoTo SNDCLM_Click_Err
'Open Directory for current record report and allow user to select files to send within the email to teh address Specified
'Operates from Send Claim Button on Claims entry form

Dim oLook As Object
Dim oMail As Object
Dim FD As Object
Dim vrtSelectedItem As Variant
Dim strname As String

strname = Application.CurrentProject.Path & "\" & Me.VHID.Column(1) & "\" & "Fault" & "\" & Me.CLID & "\" & Me.CLID & ".pdf"

Set oLook = CreateObject("Outlook.Application")
Set oMail = oLook.createitem(0)
Set FD = Application.FileDialog(3)

With oMail
.to = "enter@email.com"
.body = "Please see attached Claim Submission & Images."
.Subject = "STC CLAIM" & Me.CLID

FD.AllowMultiSelect = True
FD.Filters.Clear
FD.Filters.Add "All Files", "*.*"
FD.InitialFileName = strname

If FD.Show = True Then
For Each vrtSelectedItem In FD.SelectedItems
.Attachments.Add vrtSelectedItem
Next
End If

.Display

End With

Set FD = Nothing
Set oMail = Nothing
Set oLook = Nothing

SNDCLM_Click_Exit:
Exit Sub

Any advice appreciated as i'm kind of reaching new levels with this stuff now so i'm not even sure i fully understand it

 

[Hamish 237]

OH and If anyone knows how to force the opening folder containing the attachments to open in large thumbnail icons that would be fantastic making image selection very easy

 

[theDBguy]

OH and If anyone knows how to force the opening folder containing the attachments to open in large thumbnail icons that would be fantastic making image selection very easy

Try:

FD.InitialView = 6

 

[vba_php]

Just images are all still full size which would block emails in most servers

if I'm not mistaken, you can include images in an HTML-formatted email a variety of ways. are you saying that you believe images that are included in an email that is written in HTML, using the <img> tag are going to result in your email being blocked??? If that's what your belief is, I think you're off base. I've worked for many employers that sent mass emails out as marketing campaigns that included images embedded inside the email, and their potential customers almost always got the email in their INBOX. this includes the ever so popular GMAIL, which are supposed to have the *smartest* email servers in the world, aren't they?

OH and If anyone knows how to force the opening folder containing the attachments to open in large thumbnail icons that would be fantastic making image selection very easy

this this: https://www.google.com/search?q=vba+open+folder+large+thumbnail+icons , or something similar to that. remember, google's algorithm LOVES keywords, not full english sentences, and certainly not actual questions!

 

[FrostByte]

Thanks All, Selecting the Files was a great Idea I managed to get this to work great Code below which should be much quicker for the users
but is there anyway i could add script to control any selected image file size before its attached
like windows would normally do if your selecting image files to send.. noting I'm selecting a PDF as well
View attachment 78940

The code below works great
Just images are all still full size which would block emails in most servers

Private Sub SNDCLM_Click()
On Error GoTo SNDCLM_Click_Err
'Open Directory for current record report and allow user to select files to send within the email to teh address Specified
'Operates from Send Claim Button on Claims entry form

Dim oLook As Object
Dim oMail As Object
Dim FD As Object
Dim vrtSelectedItem As Variant
Dim strname As String

strname = Application.CurrentProject.Path & "\" & Me.VHID.Column(1) & "\" & "Fault" & "\" & Me.CLID & "\" & Me.CLID & ".pdf"

Set oLook = CreateObject("Outlook.Application")
Set oMail = oLook.createitem(0)
Set FD = Application.FileDialog(3)

With oMail
.to = "enter@email.com"
.body = "Please see attached Claim Submission & Images."
.Subject = "STC CLAIM" & Me.CLID

FD.AllowMultiSelect = True
FD.Filters.Clear
FD.Filters.Add "All Files", "*.*"
FD.InitialFileName = strname

If FD.Show = True Then
For Each vrtSelectedItem In FD.SelectedItems
.Attachments.Add vrtSelectedItem
Next
End If

.Display

End With

Set FD = Nothing
Set oMail = Nothing
Set oLook = Nothing

SNDCLM_Click_Exit:
Exit Sub

Any advice appreciated as i'm kind of reaching new levels with this stuff now so i'm not even sure i fully understand it

Can this code be set to select every file by default? I want to attached all files without selecting them.

Cheers