Excel VBA problem with sending email

MyGiG

New Member
Joined
Mar 17, 2016
Messages
10
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
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Robertvk

Board Regular
Joined
Oct 15, 2015
Messages
121
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.
 
Last edited:

MyGiG

New Member
Joined
Mar 17, 2016
Messages
10
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

New Member
Joined
Mar 17, 2016
Messages
10
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,463
Messages
5,596,283
Members
414,051
Latest member
tabecker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top