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.Application")
        Set MyItem = myOlApp.CreateItemFromTemplate("C:\Users\.oft")
       
        With MyItem
                    .To = cell.Value
                    .Subject = "Help"
                    .Send
                End With
           
            Set MyItem = Nothing
            Set myOlApp = Nothing
       
    Next cell
    Next row

End Sub
Natasha AdamsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wayne Taylor (webtubbs)Commented:
Is the Template filename correct? Also verify that the Cell.Value is correct and recognized by Outlook as a valid name/address.
0
Ryan ChongCommented:
what's the error message you getting here?
0
Natasha AdamsAuthor Commented:
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.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Wayne Taylor (webtubbs)Commented:
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?
0
Natasha AdamsAuthor Commented:
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.
0
Natasha AdamsAuthor Commented:
is the code that is copied right?
0
Wayne Taylor (webtubbs)Commented:
What error did you get?

Otherwise the code looks OK. I would remove one of the For/Next loops though and just loop through the cells in the specified range. Also, there's no need to recreate the Outlook object for each email. Do it once and use that for each email, then quit at the end.

Sub SendEmail()

    Dim rng As Range
    Dim cell As Range
    Dim myOlApp As Object
    Dim MyItem As Object
    
     'Specifies number of rows to search for email addresses
    Set rng = Range("A1:A5")
    Set myOlApp = CreateObject("Outlook.Application")
     
    For Each cell In rng.Cells
        Set MyItem = myOlApp.CreateItemFromTemplate("C:\Users\.oft")
        With MyItem
            .To = cell.Value
            .Subject = "Help"
            .Send
        End With
        Set MyItem = Nothing
    Next
    
    myOlApp.Quit
    Set myOlApp = Nothing

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Natasha AdamsAuthor Commented:
Thank you.  Let me try it now.
0
Natasha AdamsAuthor Commented:
Wayne I copied your code and still getting an error at .send   What could be the reason?
0
Wayne Taylor (webtubbs)Commented:
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.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
To make sure all the fields are being properly filled in generated emails, Replace .Send with .Display in the Wayne's proposed code and if you are sure that all the fields are filled properly, try to send one of the generated emails manually.
If you cannot send a correct email manually that means outlook is not configured properly.
0
Natasha AdamsAuthor Commented:
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.
0
Natasha AdamsAuthor Commented:
Where could I learn VBA online can you recommend some resources.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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.
0
Wayne Taylor (webtubbs)Commented:
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
0
Natasha AdamsAuthor Commented:
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?
0
Wayne Taylor (webtubbs)Commented:
Natasha, that might be best asked in a separate question.
0
Natasha AdamsAuthor Commented:
ok
0
Wayne Taylor (webtubbs)Commented:
Both comments lead to the solution working.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.