Emailing selected cells as multiple attachments in Excel VBA

Question

I have some pretty basic VBA code in Excel that creates an email out of an active cell- the cell contains the entire filepath of the attachment. Is there a way that to select multiple cells (each with different filepaths) and create an email containing multiple attachments based off of the selected cells?

I've tried replacing the code to use Selection but I keep getting an error.

Sub Send_email_fromexcel()
Dim edress As String
Dim subj As String
Dim message As String
Dim filename As String
Dim outlookapp As Object
Dim outlookmailitem As Object
Dim myAttachments As Object
Dim path As String
Dim lastrow As Integer
Dim attachment As String
Dim rngAttach As Range

    Set outlookapp = CreateObject("Outlook.Application")
    Set outlookmailitem = outlookapp.createitem(0)
    Set myAttachments = outlookmailitem.Attachments
    path = ActiveCell
    attachment = path
        outlookmailitem.Subject = subj
        myAttachments.Add (attachment)
        outlookmailitem.display
        Set outlookapp = Nothing
Set outlookmailitem = Nothing

End Sub

I suspect I'll have to specify the range number and write a conditional statement, but I'm not too sure as my vba coding is a little shaky.

There's actually several examples how to do this here on SO. This one might get you pointed in the right direction. — BigBen
I've reviewed this particular post last week. In the example they are pulling data from a specific known cell. I'm trying to send attachments based on selected cells that have been highlighted with CTRL+click. — Corey B.
Ahhh... sorry reread your question, I originally thought you were trying to get away from Selection. You just need to loop through all cells in the selection then. — BigBen

Corey B. Corey B. · Accepted Answer · 2019-05-21T20:47:58

For those who are curious:

Sub Send_email_fromexcel()
Dim subj As String
Dim outlookapp As Object
Dim outlookmailitem As Object
Dim myAttachments As Object
Dim attachment As String
Dim cel As Range
Dim selectedRange As Range
    Set selectedRange = Application.Selection
    Set outlookapp = CreateObject("Outlook.Application")
    Set outlookmailitem = outlookapp.createitem(0)
    Set myAttachments = outlookmailitem.Attachments
            For Each cel In selectedRange.Cells
                attachment = cel.Value
                myAttachments.Add attachment
            Next cel
        outlookmailitem.display
    Set outlookapp = Nothing
    Set outlookmailitem = Nothing

End Sub

Emailing selected cells as multiple attachments in Excel VBA

1 Answers