jlove88
asked on
Excel, VBA, sending an email with muliple attachments
I have code for a macro that allows me to send an email to receipients with one or two attachments. What I need is code that will attach multiple files, say up to 7, that are in a specific folder. All of the files have a .csv extension but the actual filename will change. The number of files in the folder could be anything from 1 to 7.
Is there code to attach multiple files to an email without specifying each filename? The alternative, would be code that prompted the sender to choose the files to send.
Any assistance would be appreciated.
Thanks
Is there code to attach multiple files to an email without specifying each filename? The alternative, would be code that prompted the sender to choose the files to send.
Any assistance would be appreciated.
Thanks
ASKER
Hi, thank you for the code. I guess am in need of some further assistance as to how to put it all together as I am not "getting it". Here is the code I am currently using to generate the email. In this example, wrk_fname3 will always be attached; one or both of the other two files may be attached. This is ok if one is dealing with two options, but when there are more than two, it creates a lot of unnecessary coding which I want to avoid as there must be a better way to do this. How can I incorporate the code you suggested into the code below?
Dim OutApp As Object
Dim OutMail As Object
Dim sh As Worksheet
Dim wrk_fname1 As String
Dim wrk_fname2 As String
Dim wrk_fname3 As String
fcomp = Sheet3.Cells(3, 2) ' the cell contains the name of the company
path1 = Sheet3.Cells(6, 2) 'the name of the folder where the files to attach are found
dt1 = Sheet1.Cells(4, 19)
wrk_fname1 = path1 + "PR" + "ABCo" + dt1 + ".csv"
wrk_fname2 = path1 + "PR" + "CDCo" + dt1 + ".csv"
wrk_fname3 = path1 + "Totals for ABCDCo" + ".csv"
subject_line = fcomp + ":" + " Pay Data output & Totals files "
If Sheet1.Cells(4, 20) > 0 And Sheet1.Cells(4, 21) > 0 Then
Set sh = Sheets("Setup Information")
Set OutApp = CreateObject("Outlook.Appl ication")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = Sheets("Setup Information").Range("B1"). Value
.Cc = Sheets("Setup Information").Range("B2"). Value
.Subject = subject_line
.Attachments.Add wrk_fname1
.Attachments.Add wrk_fname2
.Attachments.Add wrk_fname3
.Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End If
If Sheet1.Cells(4, 20) > 0 And Sheet1.Cells(4, 21) = 0 Then
Set sh = Sheets("Setup Information")
Set OutApp = CreateObject("Outlook.Appl ication")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = Sheets("Setup Information").Range("B1"). Value
.Cc = Sheets("Setup Information").Range("B2"). Value
.Subject = subject_line
.Attachments.Add wrk_fname2
.Attachments.Add wrk_fname3
.Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End If
subject_line = secure + fcomp + ":" + " Pay Data output & Totals files "
If Sheet1.Cells(4, 20) = 0 And Sheet1.Cells(4, 21) > 0 Then
Set sh = Sheets("Setup Information")
Set OutApp = CreateObject("Outlook.Appl ication")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = Sheets("Setup Information").Range("B1"). Value
.Cc = Sheets("Setup Information").Range("B2"). Value
.Subject = subject_line
.Attachments.Add wrk_fname1
.Attachments.Add wrk_fname3
.Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End If
Dim OutApp As Object
Dim OutMail As Object
Dim sh As Worksheet
Dim wrk_fname1 As String
Dim wrk_fname2 As String
Dim wrk_fname3 As String
fcomp = Sheet3.Cells(3, 2) ' the cell contains the name of the company
path1 = Sheet3.Cells(6, 2) 'the name of the folder where the files to attach are found
dt1 = Sheet1.Cells(4, 19)
wrk_fname1 = path1 + "PR" + "ABCo" + dt1 + ".csv"
wrk_fname2 = path1 + "PR" + "CDCo" + dt1 + ".csv"
wrk_fname3 = path1 + "Totals for ABCDCo" + ".csv"
subject_line = fcomp + ":" + " Pay Data output & Totals files "
If Sheet1.Cells(4, 20) > 0 And Sheet1.Cells(4, 21) > 0 Then
Set sh = Sheets("Setup Information")
Set OutApp = CreateObject("Outlook.Appl
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = Sheets("Setup Information").Range("B1").
.Cc = Sheets("Setup Information").Range("B2").
.Subject = subject_line
.Attachments.Add wrk_fname1
.Attachments.Add wrk_fname2
.Attachments.Add wrk_fname3
.Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End If
If Sheet1.Cells(4, 20) > 0 And Sheet1.Cells(4, 21) = 0 Then
Set sh = Sheets("Setup Information")
Set OutApp = CreateObject("Outlook.Appl
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = Sheets("Setup Information").Range("B1").
.Cc = Sheets("Setup Information").Range("B2").
.Subject = subject_line
.Attachments.Add wrk_fname2
.Attachments.Add wrk_fname3
.Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End If
subject_line = secure + fcomp + ":" + " Pay Data output & Totals files "
If Sheet1.Cells(4, 20) = 0 And Sheet1.Cells(4, 21) > 0 Then
Set sh = Sheets("Setup Information")
Set OutApp = CreateObject("Outlook.Appl
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = Sheets("Setup Information").Range("B1").
.Cc = Sheets("Setup Information").Range("B2").
.Subject = subject_line
.Attachments.Add wrk_fname1
.Attachments.Add wrk_fname3
.Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End If
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi, actually I might need your code. The code I provided was ok when I was only dealing with 3 possible files. The way I wrote the code used If Then statements so that if there was no data to send for one of the companys e.g. CDCo and therefore no file to send, then only file file for ABCo would be attached together with a "Totals" (wrk_fname3) file. If there were data files for both companies, then both would be attached together with the 3rd file; if there was a data file for CDCo but not one for ABCo, then only the CDCo file would be attached together the 3rd file. To add If Then statements to account for anything from 1 to 7 files would get unwieldy.
Is the way your code is written, designed to attach wrk_fname2 and wrk_fname3 even if wrk_fname1 is not present?
Is the way your code is written, designed to attach wrk_fname2 and wrk_fname3 even if wrk_fname1 is not present?
When you say "my code" I'm not sure which code you mean. If you are referring to the two new lines that I added to your code, then yes.
If you're referring to the code I originally posted in post ID 39609034 then what it does is to show the user a dialog box from which he can select however many files he wants.
If you're referring to the code I originally posted in post ID 39609034 then what it does is to show the user a dialog box from which he can select however many files he wants.
ASKER
Thank you. Worked perfectly.
You're welcome and I'm glad I was able to help.
Marty - MVP 2009 to 2013
Marty - MVP 2009 to 2013
Open in new window