Menu
Ask a question Report

VBA send email via Outlook using loop; No response when run [Solved/Closed]

Posts
7
Registration date
Thursday May 18, 2017
Status
Member
Last seen
June 1, 2017
 -
Excel Ask a question Report
Hello,

the above code is not functional as described. Is there a way to check what is wrong? the screen does not show anything upon clicking the run button.

Thank you in advance.

See more 

3 replies

Report
Posts
51376
Registration date
Monday February 1, 2010
Status
Moderator
Last seen
October 29, 2019
 13112
0
Thank you
I don't see any code above. What about you?
Report
Posts
7
Registration date
Thursday May 18, 2017
Status
Member
Last seen
June 1, 2017
0
Thank you
Hi Ambucias,

Thank you for your response.

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.

Thank you and look forward to your help

Best,
Dominic
Report
Posts
2559
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 12, 2019
 370
0
Thank you
Hi x.dominicraj,

Try changing the following:
Dim olapp As Outlook.Application
into:
Dim olapp As Object

Dim olmail As Outlook.MailItem
into:
Dim olmail As Object

Set olmail = olapp.CreateItem(olMailItem)
into:
Set olmail = olapp.CreateItem(0)

When it is still not working try adding the line:
On Error GoTo 0
after the 'Next i' line.

Hope that helps.

Best regards,
Trowa
View all 6 comments
Report
TrowaD
Posts
2559
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 12, 2019
 370 > x.dominicraj
Posts
7
Registration date
Thursday May 18, 2017
Status
Member
Last seen
June 1, 2017
 -
Hi x.dominicraj,

Ok, move the following code line:
Set olmail = olapp.CreateItem(0)

and place it below:
For i = 2 To 5

That line needs to be within the loop.

Best regards,
Trowa
Report
x.dominicraj
Posts
7
Registration date
Thursday May 18, 2017
Status
Member
Last seen
June 1, 2017
 -
Hi TrowaD,

Thank you for your response.

The code now looks like this:

Sub SendEmail()

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"

Any suggestions on this error?

Thanks.

Best,
Dominic
Report
TrowaD
Posts
2559
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 12, 2019
 370 > x.dominicraj
Posts
7
Registration date
Thursday May 18, 2017
Status
Member
Last seen
June 1, 2017
 -
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'

Besides that I'm out of suggestions.

At a last effort to help you out, here is a site with a lot of code examples for Outlook:
http://www.rondebruin.nl/win/s1/outlook/mail.htm

Do keep us posted on your findings.

Good luck and best regards,
Trowa
Report
x.dominicraj
Posts
7
Registration date
Thursday May 18, 2017
Status
Member
Last seen
June 1, 2017
 -
Hi Trowa,

Set olmail = Nothing did the trick. The mail is going to all the contacts in the column.

Thank you so much for your help!

Best Regards,
Dominic
Report
TrowaD
Posts
2559
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 12, 2019
 370 -
That's great! Thanks for the feedback Dominic.
Ask a question