Trying to send emails through Excel VBA

[bony_tony]

Hi, I want to send emails through VBA in excel, from data in an excel
spreadsheet. The following code sends the first email fine, but when I
loop through to the next email, I get the error 'The item has been
moved or deleted' ...any ideas where i'm going wrong?

Sub Email_test()

Dim objOutlook As Outlook.Application
Dim objMessage As Outlook.MailItem
Dim company, i, cemail As String

Set objOutlook = CreateObject("Outlook.Application")
Set objMessage = objOutlook.CreateItem(olMailItem)

Range("B2").Select
Do Until ActiveCell = ""
company = ActiveCell
ActiveCell.Offset(0, 1).Range("A1").Select
i = ActiveCell
ActiveCell.Offset(0, 2).Range("A1").Select
cemail = ActiveCell
ActiveCell.Offset(1, -3).Range("A1").Select
With objMessage
.Recipients.Add cemail
.Recipients.ResolveAll
.Subject = company
.Body = "Your credit check reference for " & company & " is "
& i
End With

objMessage.Send

Loop

End Sub

Thanks people

 

[Sue Mosher [MVP-Outlook]]

You need to create the message inside the loop, not outside.

FYI, there is a newsgroup specifically for general Outlook programming issues "down the hall" at microsoft.public.outlook.program_vba or, via web interface, at http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.outlook.program_vba

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

 

[bony_tony]

Yeah, that would make sense.
Thanks for your expert advice!
Tony