Looping

[govi]

Hi

I have a macro which in the end mails a record(sends it to Outlook)

The macro copies a record(from sheet import) to another sheet(mail), the copied record is removed from sheet import
After that the copied record(which is now on sheet mail) is send to Outlook.

The loop has to start with "The macro copies a record(from sheet import)...." and end after the last record from sheet import has been sent to Outlook.

I have attached my file.

Can someone help,

Thanks,

Govert

 

[govi]

Maybe it is convenient that I put part of the code in the post:

--more code above this--
Range("n2").AutoFill Destination:=Range("n2:n" & Range("A" & Rows.Count).End(xlUp).Row)
    Range("o2").Select
    Selection.FormulaR1C1 = "Nee"
    Range("o2").AutoFill Destination:=Range("o2:o" & Range("A" & Rows.Count).End(xlUp).Row)
 
'copy one record to sheet mail
Sheets("Import").Select
    Range("A2").Select
    Selection.End(xlDown).Select
    Range(Selection, Selection.End(xlToRight)).Select
    ActiveCell.Range("A1:O1").Select
    Selection.Copy
    Sheets("Mail").Select
    Range("A2").Select
    ActiveSheet.Paste
'remove copied record
        Sheets("Import").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Sheets("Mail").Select
'send record from sheet mail to outlook
    Call Sheets("Mail").Mail_Selection_Range_Outlook_Body
 
End Sub

The code from "'copy one record to sheet mail" till the "Call" comment has to be looped till there is no record left in sheet "import"

 

[p45cal]

change the last part of your macro to:

'copy one record to sheet mail
With Sheets("Import")
  lr = .Cells(.Rows.Count, "A").End(xlUp).Row  'assumes NOTHING in column A below table.
  For rw = lr To 2 Step -1
    With .Cells(rw, "A").Range("A1:O1")
      .Copy Sheets("Mail").Range("A2")
      'remove copied record:
      .ClearContents
    End With
    'send record from sheet mail to outlook
    Call Sheets("Mail").Mail_Selection_Range_Outlook_Body
  Next rw
End With
End Sub

 

[govi]

Hi p45cal
Thanks for your solution.

I for one for myself already.
Govi

 

[Smallman]

Hi Govi

Would you like to share your solution so others may learn from your endevours?

Thanks

Smallman