Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Email Multiple Addresses (2000 - SR2)

    In my database I track several hundred emails. We have a newsletter that gets emailed out once a quarter. Under the current (and painful) method, I import my email addressed into Outlook, manipulate some of the Contacts to make them work (very time consuming) and then send the newsletter. I'm wondering if there is a method of selecting all of the addresses in a table, using coding, and have those addresses auto-fill the BCC field in my outlook message. No more importing - No more messing with the Contact fields, etc. This would be really great, as email addresses get added, some deleted, some changed, etc.

    Any ideas? As always, thanks in advance for your assistance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Email Multiple Addresses (2000 - SR2)

    In what format is the newsletter? If it is an Access report, you can send it directly from Access. Otherwise, you can use Automation to control Outlook in code from Access. My fellow moderator <!profile=WendellB>WendellB<!/profile> has a short tutorial on Automation (with useful links) on his website (see his profile), and there are several posts in this forum with example code that can easily be modified, for example <post#=133130>post 133130</post#>.

  3. #3
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Email Multiple Addresses (2000 - SR2)

    As crazy as it sound, the newsletter is nothing more then an Outlook Rich Text Message. I use the Access database to maintain all of the information for our organization, and am trying to take the several hundred email addresses and get them into the BCC field in Outlook in one smooth motion, instead of the current situation of importing addresses, making changes to current ones, etc.

    I was just wondering out to get the email address out of the table, into the BCC field, and the "let her go".

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Email Multiple Addresses (2000 - SR2)

    Have you tried the links I posted?

  5. #5
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Email Multiple Addresses (2000 - SR2)

    I looked at the sample code from the tread you provided, but it really didn't help much. I didn't see any reference in the code as to where it is getting the email addresses from (either table or query). It looks as if the email addresses need to be typed into the code. Plus it refers to an "attachment" which I will not have. I think my situation is much simplier, unfortunately I am not a complete expert in coding.

    I have a table (tblEmail) with one field [Email} that contains between 300-400 email addresses. I would like to use a command button, or module, to open Outlook, and autofill all of the email addresses into the BCC field. From there I will complete the rest of the email manually. My current practice of importing the email address into the Outlook Contants area is VERY time consuming, and with new addresses being added, others changing, others being deleted, it is not worth it to keep doing it that way.

  6. #6
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Email Multiple Addresses (2000 - SR2)

    I think I'm almost there, but I keep getting an error when I run the sub. The message is "object variable or With block variable not set" (The W in With is capatilized). I should mention that my email addresses are entered into the [email] field as 'text' and do not contain a "MailTo" before each address. Is this a problem, either being entered as 'text' or not having the MailTo? I have attached the code as I have it in a module.

    I just want to make sure....this code goes in a module, and not a Command button?
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Email Multiple Addresses (2000 - SR2)

    Oops, stupid mistake, sorry about that. The variable name olMailItem duplicates an Outlook constant, this confuses the code. I have attached your code with the necessary modifications.

    This code is to go into a standard module or into the module behind a form. You can call it from the On Click code of a command button, if you like:

    Private Sub cmdCreateMail_Click()
    SendMail
    End Sub
    Attached Files Attached Files

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Email Multiple Addresses (2000 - SR2)

    Attachment removed - contained stupid error. See my next reply for corrected version.

    See if you can adapt the code in the attachment. To use it, you need to set references (in Tools | References...) to

    1) Microsoft DAO 3.6 Object Library (for the recordset object)
    2) Microsoft Outlook 9.0 Object Library (to automate Outlook)

    At the very least, you will have to modify the name of the table (tblRecipients) and of the field (Email) in the code.

  9. #9
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Email Multiple Addresses (2000 - SR2)

    Well I picked up a couple of new error message. The first states "Application-defined or object-defined error". The second message I have attached. It has me more complexed as it makes distinct reference to email address STORED in Outlook, and not the addresses in the Access table. I have seen this error message before, tied to automatic generated email message. I don't particularlry want to have to click OK 300-400 times.

    Update to posting: Just for the heck of it I pressed the OK button about 320 times and it loaded my email addressed into an Outlook email message. Any ideas on how I can get this message box to disappear?

    Thanks for you patience and help.
    Attached Images Attached Images

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Email Multiple Addresses (2000 - SR2)

    About the error message: temporarily replace the line "On Error GoTo Err_SendMail" by "On Error GoTo 0". When an error occurs, the code will pause, and if you click Debug, the offending instruction will be highlighted. Where does the error occur?

    There are several workarounds for the security warning. Search this forum or the Outlook forum for Redemption or for ClickYes and you'll find links.

  11. #11
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Email Multiple Addresses (2000 - SR2)

    I had to revise the code and use the SendObject command to overcome that error message. I have attached the code I ended up with. It works really nice. Thanks for your assistance. It got me going in the right direction.
    Attached Files Attached Files

Posting Permissions

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