Sending multiple e-mail messages VBA

Question

I tried the following code (I changed the real e-mail address), and it does work on the first cell in range, but after the first one, it gives me an error says: "run time error, the item has been moved or deleted", and then, it does not sending the others.... what should I need to fix in code ?

Sub sendMailWithLoop()

    Dim missmatchCell As Range
    Dim Missmatches_Rng As Range
    Dim entityForRepeatedValues_Rng As Range
    Dim OutMail As Object
    Dim OutApp As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    If Range("D1000").End(xlUp).Value <> "Name" Then

        Set Missmatches_Rng = Range(Range("D1000").End(xlUp), Range("D1000").End(xlUp).End(xlUp).Offset(1, 0))

        Missmatches_Rng.Select

        For Each missmatchCell In Selection

            With OutMail    

                .To = "[email protected]"
                .Subject = "Attention !! missmatch found"
                .Body = "The missmatch name is: " & missmatchCell.Offset(0, 1) & ", on: " & missmatchCell
                .Send   

            End With

        Next

    End If

End Sub

thx !!

hstay · Accepted Answer · 2014-02-12 22:33:39Z

3

Move your Set OutMail = OutApp.CreateItem(0) inside the For as:

Sub sendMailWithLoop()

    Dim missmatchCell As Range
    Dim Missmatches_Rng As Range
    Dim entityForRepeatedValues_Rng As Range
    Dim OutMail As Object
    Dim OutApp As Object

    Set OutApp = CreateObject("Outlook.Application")


    If Range("D1000").End(xlUp).Value <> "Name" Then

        Set Missmatches_Rng = Range(Range("D1000").End(xlUp), Range("D1000").End(xlUp).End(xlUp).Offset(1, 0))

        Missmatches_Rng.Select

        For Each missmatchCell In Selection
            Set OutMail = OutApp.CreateItem(olMailItem)
            With OutMail    

                .To = "[email protected]"
                .Subject = "Attention !! missmatch found"
                .Body = "The missmatch name is: " & missmatchCell.Offset(0, 1) & ", on: " & missmatchCell
                .Send   

            End With

        Next

    End If

End Sub

edited Feb 12, 2014 at 22:33

answered Feb 12, 2014 at 16:53

hstay

1,4391 gold badge11 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Batman Over a year ago

It gives an error for the oMailItem... what actually does it mean ? I haven't found a descent explanation on web... thx !

hstay Over a year ago

Sorry, I made a misspelling error. Anyway you could use 0 as well. See msdn.microsoft.com/en-us/library/office/ff869291.aspx for further explanation.

Batman Over a year ago

can you explain what you did that fixed my code to be working ?

hstay Over a year ago

Set OutMail = OutApp.CreateItem(OlMailItem) creates a new mail item. You need to create as many mail items as mails you are sending, so puting that line inside the loop creates as many mail items as mails you send. When it was outside the For loop you were just creating one mail item which only worked with the first mail you were sending, once the loop was hited a second time the OutMail had already gone so it made the code fail.

Batman Over a year ago

Another one.. If I want to create another condition that might (or might not, depends the results of the condition)also send an e-mail, how do I create such condition that wont disturb the objects definitions (dim's) from the first condition ? the second condition is out of the loop of the first one, there's no connection between them both (another IF condition...), thx !!

|

Collectives™ on Stack Overflow

Sending multiple e-mail messages VBA

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related