VBA send email via Outlook using loop; No response when run
Solved/Closed
x.dominicraj
Posts
7
Registration date
Thursday 18 May 2017
Status
Member
Last seen
1 June 2017
-
18 May 2017 à 11:54
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 - 29 May 2017 à 10:56
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 - 29 May 2017 à 10:56
Related:
- "-2147221238" "the item has been moved or deleted"
- "The item has been moved or deleted" 8004010a - Best answers
- "8004010a" "the item has been moved or deleted" outlook vba - Best answers
- Vba send email with attachment - Guide
- Net send - Guide
- Vba find - Guide
- Outlook email software free download - Download - Email
- How to refresh outlook email - Guide
3 responses
Ambucias
Posts
47312
Registration date
Monday 1 February 2010
Status
Moderator
Last seen
28 March 2026
11,166
18 May 2017 à 16:49
18 May 2017 à 16:49
I don't see any code above. What about you?
x.dominicraj
Posts
7
Registration date
Thursday 18 May 2017
Status
Member
Last seen
1 June 2017
23 May 2017 à 08:40
23 May 2017 à 08:40
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
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
TrowaD
Posts
2921
Registration date
Sunday 12 September 2010
Status
Contributor
Last seen
27 December 2022
555
23 May 2017 à 11:11
23 May 2017 à 11:11
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
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
x.dominicraj
Posts
7
Registration date
Thursday 18 May 2017
Status
Member
Last seen
1 June 2017
23 May 2017 à 11:28
23 May 2017 à 11:28
HI TrowaD,
Thank you for your response.
I made the changes as you had mentioned.
Now it gives the following error :
Runtime error : '-2147221238 (8004010a)':
The item has been moved or deleted.
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
Best,
Dominic
Thank you for your response.
I made the changes as you had mentioned.
Now it gives the following error :
Runtime error : '-2147221238 (8004010a)':
The item has been moved or deleted.
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
Best,
Dominic
TrowaD
Posts
2921
Registration date
Sunday 12 September 2010
Status
Contributor
Last seen
27 December 2022
555
>
x.dominicraj
Posts
7
Registration date
Thursday 18 May 2017
Status
Member
Last seen
1 June 2017
23 May 2017 à 11:45
23 May 2017 à 11:45
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
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
x.dominicraj
Posts
7
Registration date
Thursday 18 May 2017
Status
Member
Last seen
1 June 2017
23 May 2017 à 11:51
23 May 2017 à 11:51
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
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
TrowaD
Posts
2921
Registration date
Sunday 12 September 2010
Status
Contributor
Last seen
27 December 2022
555
>
x.dominicraj
Posts
7
Registration date
Thursday 18 May 2017
Status
Member
Last seen
1 June 2017
23 May 2017 à 12:24
23 May 2017 à 12:24
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:
https://www.rondebruin.nl/win/s1/outlook/mail.htm
Do keep us posted on your findings.
Good luck and best regards,
Trowa
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:
https://www.rondebruin.nl/win/s1/outlook/mail.htm
Do keep us posted on your findings.
Good luck and best regards,
Trowa
x.dominicraj
Posts
7
Registration date
Thursday 18 May 2017
Status
Member
Last seen
1 June 2017
23 May 2017 à 13:03
23 May 2017 à 13:03
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
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
