Emailing Multiple Attachmetns with VBA (1 Viewer)

[dkeg2005]

Hello and thank you for your time.
I am currently working on a loop that will either print out a letter if we do not have an email address on file for the individual or it emails the individual attaching the same report letter and, at this time, a .zip file with the 7 different files that need to go to tell the individual how to complete their training.
The code I have at this time is:

Dim MyDb As DAO.Database
Dim oOutlook As Outlook.Application
Dim oEmail As Outlook.MailItem

Set oOutlook = Outlook.Application

Set oEmail = Outlook.CreateItem(olMailItem)

While Forms![Driver Training Due Letter]![ID] <> 0

DoCmd.OutputTo acOutputReport, "Driver Training Due Letter", acFormatPDF, "Y:\VTN - DAV\TrainingLetters\" & Forms![Driver Training Due Letter]![Full_Name] & ".pdf"

If Forms![Driver Training Due Letter]![E-mail Address] <> 0 Then

With oEmail
.To = Forms![Driver Training Due Letter]![E-mail Address]
.Subject = Forms![Driver Training Due Letter]!Subject
.Body = Forms![Driver Training Due Letter]!Body
.Attachments.Add "Y:\VTN - DAV\TrainingLetters\" & Forms![Driver Training Due Letter]![Full_Name] & ".pdf"
.Attachments.Add "Y:\VTN - DAV\TrainingPackage\Training Package.zip"
.Send
End With

Else
DoCmd.OpenReport "Driver Training Due Letter", acViewReport
DoCmd.SelectObject acReport, "Driver Training Due Letter"
DoCmd.PrintOut , , , acLow, 1
DoCmd.Close acReport, "Driver Training Due Letter", acSaveNo
End If

DoCmd.GoToRecord acForm, "Driver Training Due Letter", acNext

Wend

End Sub

It does work but I need some help simplifying some things.
1. "Y:\" refers to a network drive that may or may no always be a "Y" but when I try to use the absolute path I get an error "Run-time error '-2147024894(80070002) Cannot find this file. Verify the path and file name are correct". :banghead:
2. Since most of the audience is not that good with computers it would be much better if I could attach the files individually without having a line of code for each file. This has its own issue in that if some of the users name on of the files wrong it will not send so it would be best if I could send all files in a specified folder, if that is not possible for some reason I will work around it.

 

[pr2-eugin]

1. How are you trying to get the Absolute path? Code? If so could you please provide it - using CODE tags.

2. You mean you want to attach all the files in a folder? If so what about file size limitations? What is your exact need?

Please edit your original post by using CODE tags.

 

[dkeg2005]

Absolute path would be \\v07.med.va.gov\ then the rest of the folders to get to where I need to go.
I will be attaching a few .pdf files that will not exceed the 10MB attachment limitations we currently have. I am just trying to attach multiple files without having to do each one in its own line of code.

 

[mh123]

can you post an example of your absolute path to the file that's giving you an issue? likely a missing \ as that's usually the culprit

You can send all the files in a folder by looping through them, or you could alternatively search for the specific files using the * wildcard. eg if the filename always had PIANO in it but the bits before and after that get typos you can search the file using *PIANO* etc

Also if you open a cmd window and type 'net use' double check the absolute path you have is actually the one being used (just in case your absolute path came from an IT administrator or something !)