Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OK, this is a subject that has been beaten flatter than my programming ego, but it's not as flat as my skin is thick.

    I've trolled this so-excellent forum many, many times on this subject and have found many variations with respect to the answer. But, being the simple soul that I am, I'm going to try a simplistic approach and try to state the question as clearly as possible and wishfully hope that there is an equally simple answer.

    1. Club Member Table: tblClubMember
    2. Contains 100 member records
    3. Field in said table: Email
    4. Word document called Notice01.doc
    5. Sitting in: C:\Club\Notices
    6. What is the cleanest way of sending Notice01.doc to those 100 members using their individual email addresses found in the field Email in tblClubMember?

    There, without shame, I did it.

    I would seriously appreciate any help with this. I realize that I could be pointed to several posting with variations of answers to this question, but again, a possible easy answer referring specifically to my question exists.

    Thanks in advance for any help with this
    Cheers,
    Andy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Will all users have Outlook?

    Will all users be able to install some extra software?

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='794762' date='24-Sep-2009 15:55']Will all users have Outlook?

    Will all users be able to install some extra software?[/quote]


    Hi Hans,

    Nice to hear from you. I've just come into this situation as a "volunteer", so I'm just getting up to speed. I checked with them and all users have access to Outlook.
    With respect to extra software, I'm sure that anything is possible.
    Cheers,
    Andy

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Would it be OK to send 1 e-mail to all members (i.e. add all e-mail addresses to the To: or Bcc: box), or do you want to send an individual e-mail to each member? I'd guess the latter, but I want to make sure.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='794766' date='24-Sep-2009 16:26']Would it be OK to send 1 e-mail to all members (i.e. add all e-mail addresses to the To: or Bcc: box), or do you want to send an individual e-mail to each member? I'd guess the latter, but I want to make sure.[/quote]
    What I forsee happening is the recipients will be selected via some query filter and the same notice would go to all. As a for instance 26 of the 100 members would get an email telling them that there was going to be a beer bust the coming Saturday. So 1 notice in the Notice directory going to 26 selected bodies. I'm sure that someone will also want to be able to send individual emails to individual bodies as well. And I'm sure they'll come up with other weird variations, but this is sure a good step in the righ direction.

    I'm going to be away from my system for the next few hours, so I probably won't be responding until tomorrow. Thanks again for your help,
    Cheers,
    Andy

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Here is a version that will send an individual e-mail to each member.
    I've indicated in comments how to adjust it to send to a selected group.
    The code could be called from a command button.

    Code:
    Sub SendIndividualMails()
      Dim dbs As DAO.Database
      Dim rstMembers As DAO.Recordset
      Dim olApp As Object
      Dim olMailItem As Object
      Dim blnStartOutlook As Boolean
      Dim strBody As String
    
      On Error Resume Next
      Set olApp = GetObject(, "Outlook.Application")
      If olApp Is Nothing Then
    	Set olApp = CreateObject("Outlook.Application")
    	If olApp Is Nothing Then
    	  MsgBox "Can't start Outlook", vbExclamation
    	  Exit Sub
    	End If
    	blnStartOutlook = True
      End If
      On Error GoTo Err_SendMail
    
      Set dbs = CurrentDb
      ' If you want to send to a selected group of members,
      ' replace tblClubMember with the name of a query
      ' or with an SQL string
      Set rstMembers = dbs.OpenRecordset("tblClubMember")
      ' Loop through members
      Do Until rstMembers.EOF
    	Set olMailItem = olApp.CreateItem(0)
    	olMailItem.Recipients.Add rstMembers!EMail
    	olMailItem.Subject = "Notice"
    	strBody = "You will find the notice in the attached Word document"
    	olMailItem.Body = strBody
    	olMailItem.Attachments.Add "C:\Club\Notices\Notice01.doc", 1
    	olMailItem.Send
    	rstMembers.MoveNext
      Loop
    
    Exit_SendMail:
      On Error Resume Next
      rstMembers.Close
      Set rstMembers = Nothing
      Set olMailItem = Nothing
      If blnStartOutlook Then
    	olApp.Quit
      End If
      Set olApp = Nothing
      Exit Sub
    
    Err_SendMail:
      MsgBox Err.Description, vbExclamation
      Resume Exit_SendMail
    End Sub

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    And here is a version that will send one e-mail to the entire group.

    Code:
    Sub SendOneMailToAll()
      Dim dbs As DAO.Database
      Dim rstMembers As DAO.Recordset
      Dim olApp As Object
      Dim olMailItem As Object
      Dim blnStartOutlook As Boolean
      Dim strBody As String
    
      On Error Resume Next
      Set olApp = GetObject(, "Outlook.Application")
      If olApp Is Nothing Then
    	Set olApp = CreateObject("Outlook.Application")
    	If olApp Is Nothing Then
    	  MsgBox "Can't start Outlook", vbExclamation
    	  Exit Sub
    	End If
    	blnStartOutlook = True
      End If
      On Error GoTo Err_SendMail
    
      Set olMailItem = olApp.CreateItem(0)
      olMailItem.Subject = "Notice"
      strBody = "You will find the notice in the attached Word document"
      olMailItem.Body = strBody
      olMailItem.Attachments.Add "C:\Club\Notices\Notice01.doc", 1
      Set dbs = CurrentDb
      ' If you want to send to a selected group of members,
      ' replace tblClubMember with the name of a query
      ' or with an SQL string
      Set rstMembers = dbs.OpenRecordset("tblClubMember")
      ' Loop through members
      Do Until rstMembers.EOF
    	olMailItem.Recipients.Add rstMembers!EMail
    	rstMembers.MoveNext
      Loop
      ' If you want to inspect the e-mail before sending it,
      ' change .Send to .Display
      olMailItem.Send
    
    Exit_SendMail:
      On Error Resume Next
      rstMembers.Close
      Set rstMembers = Nothing
      Set olMailItem = Nothing
      If blnStartOutlook Then
    	olApp.Quit
      End If
      Set olApp = Nothing
      Exit Sub
    
    Err_SendMail:
      MsgBox Err.Description, vbExclamation
      Resume Exit_SendMail
    End Sub

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Oh, and something else. when you run this code, Outlook is going to issue a warning that a program is trying to access your address book and/or to send mail, and ask if you want to allow this.
    To suppress this warning, you (i.e. the user) could install the free utility ClickYes. It sits in the notification area (aka system tray) and clicks Yes for you.

  9. #9
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='794771' date='24-Sep-2009 16:46']Oh, and something else. when you run this code, Outlook is going to issue a warning that a program is trying to access your address book and/or to send mail, and ask if you want to allow this.
    To suppress this warning, you (i.e. the user) could install the free utility ClickYes. It sits in the notification area (aka system tray) and clicks Yes for you.[/quote]

    Hans,

    Thanks ever so much for the quick and concise reply. I think that I've learned something over the years! in participating in this Forum, and that is if you pose a question as clearly and accurately as possible with all of the necessary information, almost without fail, someone will come back with the correct solution. I realize that my questions may appear a little juvenile on occasion, but the results have always been exactly what I was looking for.

    In the meantime, I've got Outlook up and running on my system and I'm going into test mode. I'll probably be back with the odd question over the next couple of weeks, but hopefully not too many.

    Hans, thanks again,
    Cheers,
    Andy

Posting Permissions

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