VBA Create email & attach 2 files

[mholme58]

Morning,

I'm hoping someone can provide a code for this. I've tried looking at previous posts but can't seem to find anything that works (or more accurately that I can get to work).

I have a spreadsheet that details some files that need to be sent and the recipients as follows
Column A = Recipients email (example@example.com; example@example.com)
Column B = Subject line
Column C = File folder directory
D = File name 1
E = File name 2

Currently I have 19 rows, but should have more in future.

I need the code to create and send the email with the 2 attachments.

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[mholme58]

Thanks. I can't add in XL2BB, so Dropbox it is. Photo also attached for reference.

Dropbox

www.dropbox.com

I need to send the attachments in columns D & E, located in column C, to the emails in column A.

 

[mumps]

Try:

Sub CreateEmails()
    Application.ScreenUpdating = False
    Dim OutApp As Object, OutMail As Object, v As Variant, i As Long
    Set OutApp = CreateObject("Outlook.Application")
    v = Range("A2", Range("A" & Rows.Count).End(xlUp)).Resize(, 5).Value
    For i = LBound(v) To UBound(v)
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .to = v(i, 1)
            .Subject = v(i, 2)
            .HTMLBody = ""
            .attachments.Add v(i, 3) & "\" & v(i, 4)
            .attachments.Add v(i, 3) & "\" & v(i, 5)
            .Display
        End With
    Next i
    Application.ScreenUpdating = True
End Sub

 

[mholme58]

Looks good - thank you

 

[mumps]

You are very welcome.