Email attachment to range loop

[GDunn]

Hi,
The following code will send an email to the MailAdd listed in range("L9"), using the file name created strPath & strFile which is listed in range ("M9")

I would like to loop and send an email to each of the MailAdd in range ("L9:L25"), using the file names in range("M9:M25")

Is this possible? Any help would be much appreciated.

Sub SendEmail2() 'test
Dim oLookApp As Outlook.Application
Dim oLookMail As Outlook.MailItem
Dim MailAdd As String
Set oLookApp = New Outlook.Application
Set oLookMail = oLookApp.CreateItem(olMailItem)
MailAdd = Worksheets("Data").Range("L9:L25").Value
strPath = "G:\Sales\Sales Support\Sales and Investment Tracker\Corp Super Process\"
strFile = Worksheets("Data").Range("M9:M25").Value
With oLookMail

.To = MailAdd
.Subject = "Test email"
.Body = "Please find attached Weekly Tracker detail"
.Attachments.Add strPath & strFile
.Send

End With
End Sub

 

[cjo]

I don't have Outlook installed on my home computer, so I haven't tested this out, but this should work.

Option Explicit

Sub SendEmail2()    'test
    Dim oLookApp                   As Outlook.Application
    Dim oLookMail                  As Outlook.MailItem
    Dim MailAdd                    As String
    Dim strPath                    As String
    Dim strFile                    As String
    Dim i                          As Long

    Set oLookApp = New Outlook.Application
    For i = 9 To 25    'adjust range as needed in future
        Set oLookMail = oLookApp.CreateItem(olMailItem)
        MailAdd = Worksheets("Data").Range("L" & i).Value
        strPath = "G:\Sales\Sales Support\Sales and Investment Tracker\Corp Super Process\"
        strFile = Worksheets("Data").Range("M" & i).Value
        With oLookMail

            .To = MailAdd
            .Subject = "Test email"
            .Body = "Please find attached Weekly Tracker detail"
            .Attachments.Add strPath & strFile
            .Send
        End With
    Next i
End Sub

 

[GDunn]

Thanks CJO, this works perfectly!