Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Bulk email - send vs save (XL97/WinNT4)

    Could I pick the brains of someone who knows about 'mailto' and URLs as well as XL VBA, please?

    We have put together the attached spreadsheet (with considerable help from elsewhere, although not the lounge this time!), in which you can enter the required text in the yellow fields and send an email to each record filled in. The text of the email, by the way, needs to be typed elsewhere and copied to the clipboard before running the macro.

    However, I have two problems with it:

    - I can't release it to the users because (1) the errorCatch errorhandling doesn't work - perhaps because the error would be in Outlook not XL? - and (2) the method of stopping a run started in error doesn't work very well, and sometimes not at all!

    - How do I alter the code so that the emails are saved in the Inbox or maybe Drafts folders, rather than sending them straight out, that ? At present, there's no way of testing anything other than by sending the emails, by which time it's often too late! (yes, I have sent them to me, and to others I work with; but this won't help users spot a problem with the specific run they are working on).

    Can anybody who is more up on this than I am, help me, pls?

    Many thanks!
    Attached Files Attached Files
    Beryl M


  2. #2
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Bulk email - send vs save (XL97/WinNT4)

    Hah! (don't worry, I'm not going mad, answering my own post, but something came to me as I was typing it!)

    Actually, I've worked out the second query (about saving instead of sending) - instead of "%s" in the last Application.Sendkeys line, I use "^s%{F4}" - that is, save and close window. However, having said that, I now need to know how to get Outlook to send anything from a few dozen to several hundred emails, without requiring the user to open and send each one manually ... if anyone's got any good ideas?

    I have to admit the first, two-part, query is still giving me a headache, though!
    Beryl M


  3. #3
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Bulk email - send vs save (XL97/WinNT4)

    Beryl,

    Generally you're going to have more control if you manipulate Outlook directly via automation. I'm sure there have been previous posts here with code for doing that, in any case here's code I used in an Excel>Outlook project a couple of years ago - does any of it help?

    <pre>Public Sub SendTheMail(sRecip As String, sSubject As String, sBodyMsg As String)
    Dim objOutlook As Outlook.Application
    Dim nsMAPI As Outlook.NameSpace
    Dim objOutbox As Outlook.MAPIFolder
    Dim objNewMessage As Outlook.MailItem

    On Error GoTo ERR_SendTheMail

    Set objOutlook = New Outlook.Application
    Set nsMAPI = objOutlook.GetNamespace("MAPI")
    Set objOutbox = nsMAPI.GetDefaultFolder(olFolderInbox)
    Set objNewMessage = objOutbox.Items.Add

    With objNewMessage
    .To = sRecip
    .Subject = sSubject
    .Body = sBodyMsg
    .Send
    End With

    Set objOutlook = Nothing
    Set nsMAPI = Nothing
    Set objOutbox = Nothing
    Set objNewMessage = Nothing

    EXIT_SendTheMail:
    Exit Sub

    ERR_SendTheMail:
    MsgBox Err.Number & " : " & Err.Description, vbOKOnly, "Send Chase Messages" & ".SendTheMail"
    Resume EXIT_SendTheMail

    End Sub
    </pre>

    (the sSubject, sRecip and sBodyMsg values are read from cells in a worksheet, in the main calling procedure).

    Gary

  4. #4
    New Lounger
    Join Date
    Jul 2002
    Location
    Melksham, Wiltshire, England
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bulk email - send vs save (XL97/WinNT4)

    I've used the same as Gary but as I wanted it in the drafts folder I changed the .send to .save
    It worked for me

    Rgds

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Bulk email - send vs save (XL97/WinNT4)

    Hi, Jimww, yes, that should work, and a variation I've tried does just that - but then, if you run the code on a couple of hundred (or even several thousand, as I would have to) emails, once you have checked them and are happy for them to go, how do you then get Outlook 98 to send them all without having to open each one individually and click Send?!

    <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Beryl M


Posting Permissions

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