Excel VBA problem with sending email

[MyGiG]

Hello,
first of all thank you for taking an interest in my problem.
every other time or so i get this error when i run my code (if i don't get the error it works normally) (run-time error 424: Object required) and if i click debug it highlights the following line:
Set OutApp = CreateObject("Outlook.Application").
can anyone tell me what should i change in my code so i won't get the error?
explanation of code: upper part of the code checks if all conditions are met and if yes the it will send an email that is taken from excel file, lower part of the code is just sending an email. (the code for email is from Microsoft's website). there is also some old code for previous method of sending email, but it is a comment and lastly, there is some code i use for debugging (like cells(i,5)="x").

Sub Button1_Click()


i = 2


Do While Len(Cells(i, 1)) > 0


If Len(Cells(i, 3)) > 0 Then
If Cells(i, 5) < 15 Then
b = Cells(i, 2)
If 0 < Cells(i, 5) And Cells(i, 5) < 15 Then

If Cells(i, 1) = "Rafal Stuglik" Then
' ActiveWorkbook.SendMail Recipients:=Cells(i, 2), Subject:="Dein Airsoft Mitgliedsbeitrag wird in " & Cells(i, 5) & " Tagen ablaufen."
Cells(i, 7) = "a"
Else
' ActiveWorkbook.SendMail Recipients:=Cells(i, 2), Subject:="Članarina ti poteče čez " & Cells(i, 5) & " dni."
Cells(i, 8) = "q"
a = "Pozdravljen " & Cells(i, 1) & vbNewLine & "To sporočilo je bilo generirano avtomatsko in služi kot prijazen opomnik, da ti čez " & Cells(i, 5) & " dni/an poteče članarina za airosft." & vbNewLine & "Lep pozdrav," & vbNewLine & "Airsoft team Unique"
End If

End If

If Cells(i, 5) < 0 Then
If Cells(i, 1) = "Rafal Stuglik" Then
'ActiveWorkbook.SendMail Recipients:=Cells(i, 2), Subject:="Dein Airsoft Mitgliedsbeitrag ist abgelaufen."
Cells(i, 9) = "w"
Else
' ActiveWorkbook.SendMail Recipients:=Cells(i, 2), Subject:="Članarina ti je potekla."
Cells(i, 10) = "e"
a = "Pozdravljen " & Cells(i, 1) & vbNewLine & "To sporočilo je bilo generirano avtomatsko in služi kot prijazen opomnik, da ti je potekla članarina za airosft." & vbNewLine & "Če ne želiš več prejemati tega obvestila, potem pošlji email na xx@gmail.com." & vbNewLine & "Lep pozdrav," & vbNewLine & "Airsoft team Unique"
End If
End If

If Cells(i, 5) = 0 Then
If Cells(i, 1) = "Rafal Stuglik" Then
' ActiveWorkbook.SendMail Recipients:=Cells(i, 2), Subject:="Dein Airsoft Mitgliedsbeitrag ist heute abgelaufen."
Cells(i, 11) = "r"
Else
' ActiveWorkbook.SendMail Recipients:=Cells(i, 2), Subject:="Članarina ti je danes potekla."
Cells(i, 12) = "t"
a = "Pozdravljen " & Cells(i, 1) & vbNewLine & "To sporočilo je bilo generirano avtomatsko in služi kot prijazen opomnik, da ti je danes potekla članarina za airosft." & vbNewLine & "Lep pozdrav," & vbNewLine & "Airsoft team Unique"
End If
End If

Dim OutApp As Object
Dim OutMail As Object


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


On Error Resume Next
' Change the mail address and subject in the macro before you run it.
With OutMail
.To = b
.Subject = "Airsoft članarina"
.Body = a
' You can add other files by uncommenting the following line.
'.Attachments.Add ("C:\test.txt")
' In place of the following statement, you can use ".Display" to
' display the mail.
.Send
End With
On Error GoTo 0


Set OutMail = Nothing
Set OutApp = Nothing

Cells(i, 7) = b
End If
End If

i = i + 1
Loop


End Sub

 

[Robertvk]

I assume you have set your references in VBA to outlook? (otherwise it would probably never work, but Im not sure).

Sometime it is necesarry to create a "new" instance when using external applications. At least, that is something I vaguely recall.


Also, please use code tags for your code. It makes it much more easy to read.

 

[MyGiG]

sorry for the code tags, i am new here

I forgot to mention one thing, the code sends multiple emails, if it works correctly. when it doesn't work it sends 2 emails and by the 3th it stops and i get the before mentioned error message.

 

[MyGiG]

I assume you have set your references in VBA to outlook? (otherwise it would probably never work, but Im not sure).

Sometime it is necesarry to create a "new" instance when using external applications. At least, that is something I vaguely recall.


Also, please use code tags for your code. It makes it much more easy to read.

could you explain what you mean with "new" istance?