Problem using a macro to send multiple emails with separate attachments

Hi there,

I've written some basic code to send emails from a list of data in Excel but am having trouble getting the last part right.

Code is:

Sub mail()

' Select cell A2, *first line of data*.
Range("A2").Select
' Set Do loop to stop when an empty cell is reached.
Do Until IsEmpty(ActiveCell)

Set Mail_Object = CreateObject("Outlook.Application")
With Mail_Object.CreateItem(o)
.Subject = "Subject goes here"
.To = Range("b2")
.Body = "Body text goes here"
.Attachments.Add ActiveSheet.Range("c2").Text
.Display

End With


' Step down 1 row from present location.
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Column A shows a name, column B shows an email address, column C shows the location of the file to attach.

As you can see, at the moment when I run the code I get the same email to address and attachment on every email it creates because I'm referencing the specific cell.

I'm sure it's very simple but all I need is for the macro to work its way down the list changing the email address and attachment location as it does so (i.e. for the first email it references a2,b2,c2 but then for the next mail references a3,b3,c3, and then a4,b4,c4 etc.)

If someone could point me in the right direction for this I'd be extremely grateful.

Many thanks,

j.

That's perfect, thanks a lot, really helpful.

As you said, .display turned out to be a pretty awful way of running this because there will be hundreds of lines of data so have now revised it as follows and it seems to work pretty well:

Sub mail()

     
      Range("A2").Select
     
      Do Until IsEmpty(ActiveCell)
         
         Set Mail_Object = CreateObject("Outlook.Application")
        With Mail_Object.CreateItem(o)
            .Subject = ActiveCell.Offset(0, 1) + " " + ActiveCell.Offset(0, 2) + " Salary Letter"
            .To = ActiveCell.Offset(0, 3)
            .cc = ActiveCell.Offset(0, 6)
            .Body = "Dear " + ActiveCell.Offset(0, 1) + "," & vbNewLine & vbNewLine & "Please find attached a copy of your salary review letter." & vbNewLine & vbNewLine & "Kind regards," & vbNewLine & vbNewLine & "Oliver"
            .Attachments.Add ActiveCell.Offset(0, 4).Text
            .Sensitivity = 3
            .Save
            .Close olPromptforsave
            
    End With
              
            ActiveCell.Offset(1, 0).Select
      Loop
   End Sub

It now saves all mails to the drafts folder and sets the sensitivity level.

Thanks again for your help, much appreciated.