Send email based on query

mari_hitz

Registered User.
Local time
Today, 14:44
Joined
Nov 12, 2010
Messages
120
Hi everybody,

Hope you are great. I have a question about something I want to know if can be done or not. I have an access database that sends one e-mail to each individual in a table, that works fine. Now I would like to send an e-mail to the leaders of these individuals, however, some leaders have more than individual under their scope so to avoid sending several emails, I would like to know if there is any way I can send one e-mail to the person with the names of the individuals he leads in the body of the e-mail. Is this possible?
This is the code I have that sends emails to the individuals:
Code:
Private Sub Command2_Click()
Dim MyDB As Database
  Dim MyRS As Recordset
  Dim MyForm As Form
  Dim objOutlook As Outlook.Application
  Dim objOutlookMsg As Outlook.MailItem
  Dim objOutlookRecip As Outlook.Recipient
  Dim TheAddress As String
    Dim objOutlookAttach As Outlook.Attachment
patha = "C:\Users\lucila.planich\Documents\WBSE.jpg"
  Set MyDB = CurrentDb
  Set MyRS = MyDB.OpenRecordset("WBS - 2nd communication")
  MyRS.MoveFirst
 
 
  ' Create the Outlook session.
  Set objOutlook = CreateObject("Outlook.Application")
  
  Do Until MyRS.EOF
  ' Create the e-mail message.
  Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
  TheAddress = MyRS![Enterprise]
  

     With objOutlookMsg
        ' Add the To recipients to the e-mail message.
        Set objOutlookRecip = .Recipients.Add(TheAddress)
        objOutlookRecip.Type = olBCC

        ' Set the Subject, the Body, and the Importance of the e-mail message.
        .To = MyRS![Enterprise]
        .Subject = "CORRIGENDUM: URGENT ACTION REQUIRED"
        .HTMLBody = "<html><body><font face=calibri> Dear Assignee,</font></body></html>" & vbNewLine & _
                .Importance = olImportanceHigh  'High importance
 
  ' Add attachments to the message.
             'If Not IsMissing(AttachmentPath) Then
                 Set objOutlookAttach = .Attachments.Add(patha)
             'End If
        ' Resolve the name of each Recipient.
        For Each objOutlookRecip In .Recipients
           objOutlookRecip.Resolve
           If Not objOutlookRecip.Resolve Then
             objOutlookMsg.Display
           End If
        Next
        .Send
      End With
      MyRS.MoveNext
   Loop
   Set objOutlookMsg = Nothing
   Set objOutlook = Nothing
End Sub
 
G'd afternoon,
You can add almost anything to the body's message. You can even create the email for "individuals" and send it as attachment to the leader.
G'd luck
 
Thanks Eduardo for your reply. I would like, however, to receive assistance modifying my code in order to make what I want possible. I am pretty new at VBA so I would need some guidance. Thanks!
 
A quick solution would be to have a query create a TempTable of all of your Leaders with a simple "If name already exists don't append" thrown in. This way your TempTable would only have each name once and send your emails based off of that. Then just clear the table at the end of your code.

Not the most elegant method, but simple and quick to implement.
 
Temp tables create bloat and should be avoided except in rare circumstances.

Send the manager email in a separate loop. Use a query based on the original query that groups by the manager so the manager only occurs once.
 
Thanks Pat for your help!

If it is not too much trouble, how can I do what you have said before. Should a create a query with the field Leader and count? Sorry but I'm a little burned out and do not know how to do it.

Thanks!
 
Assuming that "WBS - 2nd communication" contains your selection criteria as well as the managerID, create a new query that uses that one and just extract the manager either by using Distinct or Group By so you only get the manager once. Then just duplicate the code from "Set MyRS = " down so you can send emails to the managers only.
 
G'd evening all,
I'm sorry Mari, i'm just in the night-morning shift :( . But you can't be in better hands :) . People assisting you is top class.
 

Users who are viewing this thread

Back
Top Bottom