VBA send email via Outlook using loop; No response when run [Solved/Closed]
x.dominicraj7
Posts Thursday May 18, 2017Registration date
June 1, 2017
Last seen
-
May 18, 2017 at 11:54 AM - Latest reply: TrowaD2433
Posts Sunday September 12, 2010Registration dateModeratorStatus
September 17, 2018
Last seen
- May 29, 2017 at 10:56 AM
I am trying to write a VBA code to send auto email reminders from an excel sheet however I am getting some error. Below is the code:
Sub SendEmail()
Dim olapp As Outlook.Application
Set olapp = CreateObject("Outlook.Application")
Dim olmail As Outlook.MailItem
Set olmail = olapp.CreateItem(olMailItem)
Dim maildest As String
Dim i As Integer
For i = 2 To 5
maildest = ""
maildest = Cells(i, 5).Value
olmail.To = maildest
olmail.Subject = "is it running yet?"
olmail.Body = "A gentle reminder that XXX tasks are still pending"
olmail.Send
Next i
Range("A8").Select
Set olmail = Nothing
Set olapp = Nothing
End Sub
Could you kindly guide as to what is wrong with the code? The loop never functions.
When I click the debug option it highlights the line : "olmail.To = maildest"
The code is able to shoot mail just to the 1st address in the excel sheet. For some reason the loop is not running the cycle beyond counter 1. Any suggestions? Thanks
TrowaD2433
Posts Sunday September 12, 2010Registration dateModeratorStatus
September 17, 2018
Last seen
>
x.dominicraj7
Posts Thursday May 18, 2017Registration date
June 1, 2017
Last seen -May 23, 2017 at 11:45 AM
Hi x.dominicraj,
Ok, move the following code line:
Set olmail = olapp.CreateItem(0)
Dim olapp As Object
Set olapp = CreateObject("Outlook.Application")
Dim olmail As Object
Dim maildest As String
Dim i As Integer
For i = 2 To 5
Set olmail = olapp.CreateItem(0)
maildest = ""
maildest = Cells(i, 5).Value
olmail.To = maildest
olmail.Subject = "is it running yet?"
olmail.Body = "A gentle reminder that XXX tasks are still pending"
olmail.Send
Next i
On Error GoTo 0
Range("A8").Select
Set olmail = Nothing
Set olapp = Nothing
End Sub
Still shows the same error as mentioned above by me :
When I click the debug option it highlights the line : "olmail.To = maildest"
TrowaD2433
Posts Sunday September 12, 2010Registration dateModeratorStatus
September 17, 2018
Last seen
>
x.dominicraj7
Posts Thursday May 18, 2017Registration date
June 1, 2017
Last seen -May 23, 2017 at 12:24 PM
Hi x.dominicraj,
Are you sure the mail addresses in range E2:E5 are correct and still existing? Maybe try using the same address as in E2.
Also try to place
Set olmail = Nothing
at the end of the loop, so before 'Next i'
