+ Reply to Thread
Results 1 to 14 of 14

VBA code for mass email not working HELP!!!!!

  1. #1
    Registered User
    Join Date
    05-28-2015
    Location
    london
    MS-Off Ver
    2013
    Posts
    11

    VBA code for mass email not working HELP!!!!!

    Hi,

    I tried to follow some instructions on youtube to create a button in excel to send mass emails, but its not working quite right, can anyone help please?

    This is my first time using VBA, so please go slow.

    I've attached a screenshot of the error.

    Many Thanks
    Attached Files Attached Files
    Last edited by jcushing82; 05-28-2015 at 02:07 PM.

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: VBA code for mass email not working HELP!!!!!

    I usually use this block of code and edit from here:


    I usually work in a For/Next Loop with multiple email addresses, and a concatenated string of things to incorporate in the email body customized by recipient.
    Attached Images Attached Images
    Last edited by daffodil11; 05-28-2015 at 02:28 PM.
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Registered User
    Join Date
    05-28-2015
    Location
    london
    MS-Off Ver
    2013
    Posts
    11

    Re: VBA code for mass email not working HELP!!!!!

    Thanks for your reply, although, i was hoping somone could fix the code i had already used as I dont really know how to get yours working, like I said I a first timer using this. I was following step bu stp instuctions on youtube and dont really have the knowledge to edit your code you have kindly provided.
    Last edited by jcushing82; 05-28-2015 at 02:33 PM.

  4. #4
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: VBA code for mass email not working HELP!!!!!

    I'd recommend attaching a sample of your work instead of pasting a picture and asking someone to retype it all out by hand.

  5. #5
    Registered User
    Join Date
    05-28-2015
    Location
    london
    MS-Off Ver
    2013
    Posts
    11

    Re: VBA code for mass email not working HELP!!!!!

    My work is confidental, so i cant post a sample, i thought an expert might be able to look at the code and tell me where its going wrong?

  6. #6
    Registered User
    Join Date
    05-28-2015
    Location
    london
    MS-Off Ver
    2013
    Posts
    11

    Re: VBA code for mass email not working HELP!!!!!

    Sub SendEmail(what_address As String, subject_line As String, mail_body As String)

    Dim olApp As Outlook.Application
    Set olApp = CreateObject("Outlook.Application")

    Dim olMail As Outlook.MailItem
    Set olMail = olApp.CreateItem(olMailItem)

    olMail.To = what_address
    olMail.Subject = subject_line
    olMail.Body = mail_body
    olMail.Send

    End Sub
    Sub SendMassEmail()

    row_number = 1

    Do
    DoEvents
    row_number = row_number + 1
    Call SendEmail(Sheet1.Range("A" & row_number), "This is a test e-mail", Sheet1.Range("j23"))
    Loop Until row_number = 6

    End Sub

    Heres the code without .doc if that helps?? Like i said this is my first time, i dont have the know how to use and edit your code

  7. #7
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: VBA code for mass email not working HELP!!!!!

    Then just copy and paste the whole code. Or cut out the confidential parts and paste the whole thing.

    I'd recommend a read through of the RULES, just so you don't run afoul of the moderators.

    Please edit the post and select your code and hit the button with the hashtag number sign to add [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags around it to format it properly.

  8. #8
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: VBA code for mass email not working HELP!!!!!

    Let's begin by discussing what is the basic concept that you're trying to accomplish with this?

    The example I most often write for management is where I might have 1000 lines of data, with column A = email, column B = a name?, column C might be some identifier and maybe column D has some numbers.

    In that instance I'd be sending one email to everyone in A where C met some qualification, and in the body of the email I'd address them by the name in B and include the statistics in D.

  9. #9
    Registered User
    Join Date
    05-28-2015
    Location
    london
    MS-Off Ver
    2013
    Posts
    11
    I just have a list of email addresses in column a2 - a6 and when I click the button I would like the email to be created using the text from column j23.

  10. #10
    Registered User
    Join Date
    05-28-2015
    Location
    london
    MS-Off Ver
    2013
    Posts
    11

    Re: VBA code for mass email not working HELP!!!!!

    https://m.youtube.com/watch?v=i_uJNMJVW1k

    I am basically trying the copy what the YouTube video is showing.

  11. #11
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: VBA code for mass email not working HELP!!!!!

    I've adjusted your code to Late Binding, as opposed to Early Binding which requires References.

    Dim is short for dimension, which means to describe the how you intend to use a variable.
    Set is used to declare the specific property of said variable.

    A basic For Each / Loop or For / Loop can be used to run through a series of ranges to evaluate each. For you, instead of evaluating and sending one at a time we can just use the loop to build the SendTo field.


    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    05-28-2015
    Location
    london
    MS-Off Ver
    2013
    Posts
    11

    Re: VBA code for mass email not working HELP!!!!!

    Ok, great, doesnt send the email automatically but i can press the final send. Thank you for all your help.

  13. #13
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: VBA code for mass email not working HELP!!!!!

    You can make it go automatically by substituting .Display with .Send

    Also, if you trade .Body with .HTMLBody you can also incorporate some basic HTML tags.

    For example, let's say you wanted to include a list of whatever was in Column G for each person.

    The tags <ul> to start a list is invoked, along with <li> to start each item and </li> to end each item. The string concludes with </ul> to end the list.
    You can get pretty creative with color, font size, etc with some basic HTML (which I've all just looked up as I've gone).

    Please Login or Register  to view this content.
    Last edited by daffodil11; 05-28-2015 at 03:56 PM.

  14. #14
    Registered User
    Join Date
    05-28-2015
    Location
    london
    MS-Off Ver
    2013
    Posts
    11

    Re: VBA code for mass email not working HELP!!!!!

    Ok Great, thanks again, rep added :-)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Mass email a column of email addresses?
    By bmblack in forum Excel General
    Replies: 3
    Last Post: 09-30-2015, 03:06 PM
  2. VBA to perform mass email functions with Apple Mail (Macbook default email program)
    By thianphat in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-24-2014, 04:09 PM
  3. [SOLVED] How to add an email attachement to VBA Mass Email by referencing file name in column
    By XCL88 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 04-10-2014, 06:20 AM
  4. Code for email alerts from excel isn't working, wrong code possibly?
    By jessthorogood in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-27-2012, 01:45 AM
  5. [SOLVED] Email addresses in Excel need to format for mass email
    By Boomer in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-09-2006, 08:50 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1