Email From form from query 1

[ptrifile]

I have a button on a form that I would like when pressed to email a group of people. I have a query that lists email addresses of the individuals that should receive the email but only the first email address is entered into the "TO" field instead of the entire list. Was wondering if someone could help out. The code for my button that i am using is below:

 Dim olapp As Outlook.Application
    Dim olNewMail As Outlook.MailItem
    Dim eTo, eSubject, ebody As String
    eTo = DLookup("emailadress", "qryemail")
    eSubject = "KNOWLEDGEBASE - Missing Document(s)"
    ebody = "test"
    Set olapp = New Outlook.Application
    Set olNewMail = olapp.CreateItem(olMailItem)
    olNewMail.To = eTo
    olNewMail.Subject = eSubject
    olNewMail.Body = [Main_category] & Chr$(13) & [Second_category] & Chr$(13) & [Third_category] & Chr$(13)
    olNewMail.Display

Thanks for any help or suggestions.

Paul

 

[PHV]

DLookUp returns only the first value found.
You have to use a RecordSet based on qryemail to get all the values you want.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

It's also worth mentioning that generally speaking, any domain aggregate lookups (DLOOKUP, DCOUNT, etc) will run slowly. So that's 2 reasons to use a recordset instead.

I also just notice your Dim statements (declarations) - you need to specify the type for each listed item. Otherwise, those without specific types will be given the type variant, not string, as what it appears you're trying to do.

So with that in mind, here's what I'd do:

Dim olapp As Outlook.Application
    Dim olNewMail As Outlook.MailItem
    Dim db as DAO.Database
    Dim rs as DAO.Recordset
    Dim eTo [b]As String[/b], eSubject [b]As String[/b], ebody As String

    [b]Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * FROM qryEmail")[/b]

    [b]eTo = rs.Fields("emailadress")[/b]
    'etc...
    eSubject = "KNOWLEDGEBASE - Missing Document(s)"
    ebody = "test"

    Set olapp = New Outlook.Application
    Set olNewMail = olapp.CreateItem(olMailItem)
    olNewMail.To = eTo
    olNewMail.Subject = eSubject
    olNewMail.Body = [Main_category] & Chr$(13) & [Second_category] & Chr$(13) & [Third_category] & Chr$(13)
    olNewMail.Display

Does that make sense for what you're trying to do?

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[ptrifile]

Thank you PHV, i have been researching using the recordset since your post with minimal success (I will continue trying).

Thanks kjv1611! When using your solution I still have the problem of it only filling the "TO" field in the email with the first record from the query.

Thank you for the help guys, I truly appreciate it.

Paul

 

[Andrzejek]

Try this logic:

Dim i as integer
Dim strTo As String

    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * FROM qryEmail")
Do While  Not rs.EOF
   strTo = strTo & rs.Fields("emailadress") & ";"
Loop
    eTo = strTo

Have fun.

---- Andy

 

[PHV]

You have to loop thru the recordset, eg:

Set rs = db.OpenRecordset("SELECT * FROM qryEmail")
While Not rs.EOF
  eTo = eTo & ";" & rs.Fields("emailadress")
  rs.MoveNext
WEnd
rs.Close
eTo = Mid(eTo, 2)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[oxicottin]

Here is a Private function I use in one of my databases, Hope this helps...

Private Function EmailData()

'//Function to email some data using outlook
  'Uses the table tblEmailAddress field strEMail to retrieve the email address to send
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sEmailList As String
    Dim stSubject As String
    Dim stText As String
  
    stSubject = "Enter a Subject Here”
    stText = "A new Accident Illness Entry has been created." & Chr$(13) & _
             "Please review the Accident Description with your team members." 
              
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("SELECT strEMail " & _
                 "FROM tblEmailAddresses;")

    With rs
        If (Not .BOF) And (Not .EOF) Then
            .MoveFirst
            sEmailList = .Fields("strEMail")
            .MoveNext
        End If

        If (Not .BOF) And (Not .EOF) Then
            Do Until .EOF
                sEmailList = sEmailList & "; " & .Fields("strEMail")
                .MoveNext
            Loop
        End If

        .Close

    End With

    'Write the e-mail content for sending to assignee
    DoCmd.SendObject , , acFormatTXT, sEmailList, , , stSubject, stText, -1

Thanks,
SoggyCashew.....

 

[Andrzejek]

All of this:

    With rs
        If (Not .BOF) And (Not .EOF) Then
            .MoveFirst
            sEmailList = .Fields("strEMail")
            .MoveNext
        End If

        If (Not .BOF) And (Not .EOF) Then
            Do Until .EOF
                sEmailList = sEmailList & "; " & .Fields("strEMail")
                .MoveNext
            Loop
        End If

        .Close

    End With

Could be a lot shorter:

With rs
    Do Until .EOF
        If Len(sEmailList) = 0 then
            sEmailList = .Fields("strEMail")
        Else
            sEmailList = sEmailList & "; " & .Fields("strEMail")
        And If
        .MoveNext
    Loop
    .Close
End With

Have fun.

---- Andy