Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 21

Thread: e-mail to multiple recipients

  1. #11
    Board Regular
    Join Date
    Mar 2002
    Location
    Philippines
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Al,
    Your last code works!
    Two bugs though:
    How do I control the number of loops? - It seems to run endless.
    And everytime it has sent an e-mail it gives me the message "E-Mail sent" and I have to respond. Where to put a "hide message" and how to formulate?
    Hans

  2. #12
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It should only run for the number of email addresses in column A (There has to be more than just 1). Delete the line msgbox "Email Sent" to get rid of the box. I just put it there to make sure that it doesn't loop before the mail is sent. That is the bad thing about using SendKeys, which is why I try to avoid using it. Sometimes you just have to use it.

    To make sure that it runs correctly without a msgbox. Replace the msgbox line with the following:
    Application.Wait (Now + TimeValue("0:00:02"))
    You can play around with both instances of this code to see which combination works best.

    The line of code just pauses the macro, so it really shouldn't be needed in this case. I am just using it to make sure things run smoothly.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  3. #13
    Board Regular
    Join Date
    Mar 2002
    Location
    Philippines
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Al,
    done that already, works fine, now I just have the problem that after about 10 e-mails sent the memory of Outlook Exxpress seems to be full, so I have to empty the "Sent Items" window first before I acn run the next sequence.
    I guess there is no solution for that?
    Hans

  4. #14
    Board Regular
    Join Date
    Mar 2002
    Location
    Philippines
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Al,
    it started to work fine with bugs, but now it doesn't send anymore. It just piles up the Outlook Express "New Message" window and I have to manually click the send button.
    Hans

  5. #15
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The sent items question has been discussed on this board before. I don't remember the conclusion, but you can do a search for it.

    The problem with it not sending has to do with this line: Application.SendKeys "%s"
    This is telling your computer to hit ctrl+s, which is the shortcut to send an email. To get it working properly fool around with the two wait lines to make sure that the email is ontop, when you send the keys.

    Does this make sense?
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  6. #16
    Board Regular
    Join Date
    Mar 2002
    Location
    Philippines
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Al,
    actually the shortcut would be Alt+S, but it says in the help manual that the % has to be embedded in braces other then () - the curved ones which I cannot find on my keyboard and there is no way to insert symbols in the VB editor.
    Hans

  7. #17
    Board Regular
    Join Date
    Mar 2002
    Location
    Philippines
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Copied the braces from the help file of VB editor and played around with it, but it doesn't work.
    {}
    Help anybody!!
    Hans

  8. #18
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Actually I mean't Alt+S. The code I gave is correct in that way.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  9. #19
    Board Regular
    Join Date
    Feb 2002
    Location
    Leicestershire, U K
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Bukol,

    Sub SendMsg()
    On Error Resume Next
    ThisWorkbook.VBProject.References.AddFromGuid _
    "{00062FFF-0000-0000-C000-000000000046}", 9, 0

    Dim objOL As New Outlook.Application

    Try using the ' in front of the dim objol line to green it out.

    This is where the error applies.
    I had exactly that today.

    The preceding line (before Dim) references Outlook automatically with out using the libary in VBA menu.

    When you use the Dim ObJol you require the linking to the library via the VBA reference libary noy though the auto linking precing.

  10. #20
    Board Regular
    Join Date
    Apr 2002
    Location
    Cape Town,South Africa
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Good 1 TOM

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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