Solved

VBA code to automate emails in outlook using a Template

Posted on 2016-09-06
19
47 Views
Last Modified: 2016-09-28
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
0
Comment
Question by:Natasha Adams
  • 9
  • 7
  • 2
  • +1
19 Comments
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41787223
Is the Template filename correct? Also verify that the Cell.Value is correct and recognized by Outlook as a valid name/address.
0
 
LVL 51

Expert Comment

by:Ryan Chong
ID: 41787251
what's the error message you getting here?
0
 

Author Comment

by:Natasha Adams
ID: 41787717
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
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41788758
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
 

Author Comment

by:Natasha Adams
ID: 41788991
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
 

Author Comment

by:Natasha Adams
ID: 41788992
is the code that is copied right?
0
 
LVL 47

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 250 total points (awarded by participants)
ID: 41789000
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
 

Author Comment

by:Natasha Adams
ID: 41789005
Thank you.  Let me try it now.
0
 

Author Comment

by:Natasha Adams
ID: 41789007
Wayne I copied your code and still getting an error at .send   What could be the reason?
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41789020
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
 
LVL 30

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 250 total points (awarded by participants)
ID: 41789112
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
 

Author Comment

by:Natasha Adams
ID: 41789401
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
 

Author Comment

by:Natasha Adams
ID: 41789404
Where could I learn VBA online can you recommend some resources.
0
 
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41789440
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
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41790511
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
 

Author Comment

by:Natasha Adams
ID: 41790658
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
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41790669
Natasha, that might be best asked in a separate question.
0
 

Author Comment

by:Natasha Adams
ID: 41792057
ok
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41819424
Both comments lead to the solution working.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question