Solved

Excel, VBA, sending an email with muliple attachments

Posted on 2013-10-29
7
1,743 Views
Last Modified: 2013-11-01
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
0
Comment
Question by:jlove88
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 47

Expert Comment

by:Martin Liss
ID: 39609034
Option Explicit
Public gcolAttachments As Collection

Sub GetAttachments()
Dim fd As FileDialog
Dim FileChosen As Integer
Dim intFile As Integer

Set gcolAttachments = New Collection

Do
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    'the number of the button chosen
    FileChosen = fd.Show
    fd.title = "Choose file to attach"
    ' Allow multiple file selection
    fd.AllowMultiSelect = True
    ' Set the initial FOLDER
    fd.InitialFileName = "C:\"
    fd.InitialView = msoFileDialogViewDetails
    fd.Filters.Clear
    fd.Filters.Add "All files", "*.*"
    fd.FilterIndex = 1
    fd.ButtonName = "&OK"
    If FileChosen <> -1 Then
        MsgBox "'Cancel' selected"
        Exit Sub
    Else
        For intFile = 1 To fd.SelectedItems.Count
            gcolAttachments.Add fd.SelectedItems(intFile)
        Next
    End If
    If vbNo = MsgBox("More attachments?", vbQuestion + vbYesNo, "Add attachments") Then
        Exit Sub
    End If
Loop

End Sub

' someplace else in your code...
            For intAttachment = 1 To gcolAttachments.Count
                .Attachments.Add gcolAttachments(intAttachment)
            Next

Open in new window

0
 

Author Comment

by:jlove88
ID: 39611947
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.Application")
    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.Application")
    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.Application")
    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
0
 
LVL 47

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 39612124
The code you posted seems to have the email-generation code copied 3 times (I'm referring to the 3 End/Ifs) and I assume you only have one of them in your real code. And I also assume that only those 3 files are ever involved. Are my assumptions correct?

If so then forget about my code and just do this.

If Sheet1.Cells(4, 20) > 0 And Sheet1.Cells(4, 21) > 0 Then

    Set sh = Sheets("Setup Information")

    Set OutApp = CreateObject("Outlook.Application")
    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
                On Error Resume Next
                .Attachments.Add wrk_fname1
                .Attachments.Add wrk_fname2
                .Attachments.Add wrk_fname3
                On Error GoTo 0
                .Send
            End With

            Set OutMail = Nothing
            Set OutApp = Nothing

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

End If
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jlove88
ID: 39612199
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?
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 39612215
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.
0
 

Author Closing Comment

by:jlove88
ID: 39616692
Thank you.  Worked perfectly.
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 39616721
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Outlook Free & Paid Tools
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question