Natasha Adams
asked on
VBA code to automate emails in outlook using a Template
I am getting an error at .Send can you please let me know what is wrong? Thanks in advance
Sub SendEmail()
Dim rng As Range
Dim row As Range
Dim cell As Range
Dim from As String
Dim template As String
'Specifies number of rows to search for email addresses
Set rng = Range("A1:A5")
Dim myOlApp As Object
Dim MyItem As Object
For Each row In rng.Rows
For Each cell In row.Cells
Set myOlApp = CreateObject("Outlook.Appl ication")
Set MyItem = myOlApp.CreateItemFromTemp late("C:\U sers\.oft" )
With MyItem
.To = cell.Value
.Subject = "Help"
.Send
End With
Set MyItem = Nothing
Set myOlApp = Nothing
Next cell
Next row
End Sub
Sub SendEmail()
Dim rng As Range
Dim row As Range
Dim cell As Range
Dim from As String
Dim template As String
'Specifies number of rows to search for email addresses
Set rng = Range("A1:A5")
Dim myOlApp As Object
Dim MyItem As Object
For Each row In rng.Rows
For Each cell In row.Cells
Set myOlApp = CreateObject("Outlook.Appl
Set MyItem = myOlApp.CreateItemFromTemp
With MyItem
.To = cell.Value
.Subject = "Help"
.Send
End With
Set MyItem = Nothing
Set myOlApp = Nothing
Next cell
Next row
End Sub
Is the Template filename correct? Also verify that the Cell.Value is correct and recognized by Outlook as a valid name/address.
what's the error message you getting here?
ASKER
I copied the code from the internet so not sure what should go to .cell value. It stops at .Send
please help I have created a template in .oft and have an excel spreadsheet with email address's to send the template to all on the emails.
please help I have created a template in .oft and have an excel spreadsheet with email address's to send the template to all on the emails.
The code you have loops through cells A1:A5 of your worksheet and uses the values in those cells for the email address. If any of the values in cells A1:A5 is blank or an invalid address, Outlook will give you an error when you try to Send it.
Secondly, the path to the template needs to the actual template file, not just the folder. What is the actual name of the template?
Secondly, the path to the template needs to the actual template file, not just the folder. What is the actual name of the template?
ASKER
it is still giving an error at .send the template file is correct. Can you please help as I have to send these emails tomorrow.
ASKER
is the code that is copied right?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you. Let me try it now.
ASKER
Wayne I copied your code and still getting an error at .send What could be the reason?
I've tested the code here (although without a template as I currently only have access to Outlook 2007), and it works fine UNLESS a value in cells A1:A5 is not a valid name or email address.
Otherwise I don't know if you don't tell me what error you are getting.
Otherwise I don't know if you don't tell me what error you are getting.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Wayne and Subodh it worked Thank you. I put .Display and it worked then I went back and put .send and it worked. OMG thank you so much. I really appreciate your help.
ASKER
Where could I learn VBA online can you recommend some resources.
You're welcome Natasha! Glad we could help.
There is plenty of stuff available online, just Google "Excel VBA tutorials for beginner", you can search for the same on YouTube as well.
There is plenty of stuff available online, just Google "Excel VBA tutorials for beginner", you can search for the same on YouTube as well.
Natasha,
I'm glad it's finally worked for you. Next time please don't forget to include the actual error message you received. We might have been able to solve this almost immediately if you had.
Wayne
I'm glad it's finally worked for you. Next time please don't forget to include the actual error message you received. We might have been able to solve this almost immediately if you had.
Wayne
ASKER
Thank you Wayne and Subodh I will next time. How would I add to the existing code Hi and to pick up the persons name from excel and include?
Natasha, that might be best asked in a separate question.
ASKER
ok
Both comments lead to the solution working.