PDA

View Full Version : VBA Multiple Attachments



JDW
12-14-2016, 07:47 AM
Hello,

I'm attempting to add multiple "dynamic" attachments to an outgoing e-mail using VBA. These attachments change names (dates) every week. Is there a way to add a wildcard to the file name so that it will still attach the file even if the name changes? Here is what I have so far, I can't get it to run without errors. There are three files total that I would like to attach, the names are app_opdiv_piv_<date>.xlsx, app_opdiv_nonpiv_<date>.xlsx, app_opdiv_pdf_<date>.pdf.


Sub app_opdiv_rep()

Dim wb As Workbook
Dim strFile As String, strDir As String, sendMail As String, toMail As String, ccMail As String, bccMail As String
Dim fso As Object, oFolder As Object, oFile As Object
strDir = Range("E12")
strFile = Dir(strDir & Range("G12"))
sendMail = Range("B12")
toMail = Range("H12")
ccMail = Range("I12")
bccMail = Range("J12")

Set wb = Workbooks.Open(fileName:=strDir & strFile, Local:=True)

If (sendMail = "Yes") Then
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
Set fso = CreateObject("Scripting.FileSystemObject")
Set oFolder = fso.GetFolder("C:\SCMS Reports\")
SearchString = "app_opdiv_"
With OutMail
.To = toMail
.CC = ccMail
.BCC = bccMail
.Subject = "App OpDiv Report"
.Body = "App OpDiv Report"
For Each oFile In oFolder.Files
If InStr(1, oFile.Name, "app_opdiv_") > 0 Then Attachments.Add oFile.Path
Next oFile
.Display

End With
End If
End Sub

JKwan
12-14-2016, 08:47 AM
you just made a slight error, update line to

If InStr(1, oFile.Name, SearchString) > 0 Then .Attachments.Add oFile.Path

JDW
12-14-2016, 09:35 AM
Thanks for that!

I made the changes but now I'm getting another error and it's highlighting ".Attachments.Add oFile.Path".

Run-time error '-2147024864(80070020)':The operation failed.

JKwan
12-14-2016, 09:59 AM
hmm, I don't have any error, my attached fine.... what do you have for a value for oFile.Path (just msgbox oFile.Path)

JDW
12-14-2016, 12:18 PM
hmm, I don't have any error, my attached fine.... what do you have for a value for oFile.Path (just msgbox oFile.Path)

I have Dim oFile as Object but it's not set to any specific file/path. I apologize, I just started learning VBA a couple days ago so I'm definitely a greenhorn.

I appreciate the help.