Send Separate text messages with one macro from excel

[kadenjp]

I am trying to send different texts through outlook using a macro in excel. I have the macro working to send one email, but when I try and loop to the second email address, I get a runtime error saying the item has been moved or deleted. I have the following code:

Sub sendText()
Dim aOutlook As Object
Dim aEmail As Object
Dim rngeAddresses As Range, rngeCell As Range, strRecipients As String

Set aOutlook = CreateObject("Outlook.Application")
Set aEmail = aOutlook.CreateItem(0)

'set sheet to find address for e-mails as I have several people to mail to

'Find how many people to email to
Dim x As Integer

x = Sheet1.Range("m2").Value

Set rngeAddresses = Sheet3.Range("L1:L" & x)

For Each rngeCell In rngeAddresses.Cells
strRecipients = strRecipients & ";" & rngeCell.Value
Next
'set Importance
aEmail.Importance = 2
'Set Subject
aEmail.Subject = ""
'Set Body for mail
Dim eText As Integer

Do
For eText = 1 To 16

Sheet3.Activate
Range("L" & eText).Select
If ActiveCell.Value <> "" Then
aEmail.Body = Sheet3.Range("J" & eText).Value
Else
Exit Do
End If
aEmail.To = rngeAddresses(1).Value
aEmail.Send
Next
Loop

'Set Recipient

'or send one off to 1 person use this static code
'aEmail.Recipients.Add "e-mail.address"
'Send Mail

End Sub

Any help would be great. Thanks.

p.s. I have commented out other methods I have tried that also did not work for sending separate emails.

 

[baitmaster]

your problem is that you're doing the following via code:
- opening outlook
- creating a new message

- adding text to that message, setting the address etc. and sending it

- trying to then add new text, address etc. to that same message and send it again. This fails because you've already sent it

You need to move the creation of the email inside your loop so the logic works as follows:
- open outlook
- get generic text, range of email addresses etc. for use later

- begin loop
- create email x
- add generic text plus any specific text
- add email addresses etc. and send
' restart loop and create next email

Get the logic right and set up your comments. Then write the code in the right places - having a comment structure really helps keep the logic in the right order

Another useful approach is to blind copy everyone so you can send the same email to multiple recipients without them seeing each others email address, although you can't customise the content