CODEWITHSUNDEEP
excelvba
Corey B.
Corey B.

Reputation: 1

Emailing selected cells as multiple attachments in Excel VBA

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.

Upvotes: 0

Views: 148

Answers (1)

Corey B.
Corey B.

Reputation: 1

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

Upvotes: 0

Related Questions