send Email based on query results 2

[martinot]

I have an SQL query that returns a list of contact names and other info such as email names. I would like to have a command button that opens up outlook and gets all the email names listed in my query results box (lstresults) in the "To" field of outlook. I could setup the opening up of outlook ok but can't get the email names in the "To" box

Thanks in advance for your help !

Martinot

 

[redapples]

Hi, I'm not sure what your specific problem is but here goes.

If the problem is getting all the email addresses into a String then a simple for next or for each routine should do that ok, remembering of course to seperate each address with the appropriate delimiter (comma or semi-colon).

If the problem is setting a string to the TO field in an email the answer depends on how you are sending the email.
If you are using an Outlook object then the code will be something like:

    objItem.To = strMailTo

where objitem = an email object and strMailTo is the string of names gathered from the lstresults control.

there are a ton of posts that go into more detail.
here are a few:
thread702-620746
Send e-mail from access databases
Thread702-396121
how to connect access with outlook's contact folder
Thread702-593217


Readapples.

Want the best answers? See FAQ181-2886

 

[martinot]

Thanks for your reply readapple. I had looked at the other posts but they didn't seem to match my particular need plus I'm quite a newbie in SQL. So please bear with me

OK so here is my code;
Private Sub RefreshQuery()
Dim SQL As String
Dim SQLWhere As String
SQL = &quot;SELECT OrganisationID, ContactID, FirstName, LastName, EmailName, Title, Board, Communications, Executive, Expert FROM Contacts Where Contacts!ContactID <> 0 &quot;
If Not Me.chkExp Then
SQL = SQL & &quot;And Contacts!Expert like '*&quot; & Me.txtSrchExp & &quot;*' &quot;
End If
If Not Me.chkexec Then
SQL = SQL & &quot;And Contacts!Executive like '*&quot; & Me.txtSrchExec & &quot;*' &quot;
End If
If Not Me.ChkTech Then
SQL = SQL & &quot;And Contacts!Technical like '*&quot; & Me.txtSrchTech & &quot;*' &quot;
End If
If Not Me.chkCom Then
SQL = SQL & &quot;And Contacts!Communications like '*&quot; & Me.TxtSrchCom & &quot;*' &quot;
End If
If Not Me.chkBoard Then
SQL = SQL & &quot;And Contacts!Board like '*&quot; & Me.TxtSrchBoard & &quot;*' &quot;
End If

SQLWhere = Trim(Right(SQL, Len(SQL) - InStr(SQL, &quot;Where &quot - Len(&quot;Where &quot + 1))

SQL = SQL & &quot;;&quot;

Me.lblStats.Caption = DCount(&quot;*&quot;, &quot;Contacts&quot;, SQLWhere) & &quot; / &quot; & DCount(&quot;*&quot;, &quot;Contacts&quot
Me.lstResults.RowSource = SQL
Me.lstResults.Requery

How can I send a mass email to all the people listed in the list results ? I don't need a mail body or anything just to have all the email addresses in the 'To' box of outlook

Thanx

Martinot

 

[redapples]

here are a couple of routines I use
1. get a string with all the addresses from a list box

Private Sub lstGroups_DblClick(Cancel As Integer)
    dim stremailto as string
    dim i as integer
    For i = 0 To lstGroups.ListCount - 1
        stremailto = stremailto & lstGroups.ItemData(i) & ";" 'my email is in column 1 of the list box. SEE NOTES BELOW!!! 
        
    Next
    stremailto = Left(stremailto, Len(stremailto) - 1) 'takes of the last ; not needed but tidy.
    GroupEmailSender (stremailto)
End Sub

having got all my email addresses from my listbox I use this to start an email to all those people.

Function GroupEmailSender(strMailTo As String)
    Dim objOutlook As Object
    Dim objItem As Object

    On Error GoTo starterror
    
    'Create a Microsoft Outlook object.
    Set objOutlook = CreateObject("Outlook.Application")

    'Create and open a new email.
    Set objItem = objOutlook.CreateItem(olMailItem)
    If strMailTo <> &quot;&quot; Then 
        objItem.To = strMailTo
        objItem.Display
    End If

    'Quit Microsoft Outlook.
    Set objOutlook = Nothing

    
ExitHere:
    Exit Function

starterror:
    Select Case Err.Number
        Case Else
            MsgBox &quot;An error occured: &quot; & Err.Number _
            & &quot; &quot; & Err.Description
    End Select
    
End Function

NOTES
Your email address in the 5th column or Column(4) if I read your SQL right. You will need to change this!!! Your SQL will just need to put the email in the first Column. If you need to display it in a particular way you might have to resort to another method or have email twice and hide the first column. Another more complicated method would be to use a record set to return an email address for each OrganisationID in the listbox to a string instead.
-----------------------------------------------------------
You will need to set the reference in Access to include the Outlook objects. (In VBA Tools | References | micorsoft Outlook 9.0 Object Library) Also if you intend to send an email rather than open one as I have done you might need to look at Outlook Redemption as a work around to the security contstraints imposed in XP and higher Service packs of Outlook 2K.

Red. Hope this helps


Want the best answers? See FAQ181-2886

 

[martinot]

Thanks Red Apples !! I really appreciate you took the time to answer. I will try your code and let you know how I got on.

 

[martinot]

Ok I've just tried your code. And it works fine ! Thx a million !
Unfortunately I have column heads in my listbox
so when using your solution of having the Email column twice and hiding the first column, I get 'Email Name ;' followed by all the correct email addresses in the 'To box of Outlook. Any idea how I could get rid of this ?

Thx

Martinot

 

[redapples]

Martinot, can you clarify.
Does the words Email Name appear before all names or just at the start?

If it is just infront of the first one then (at a guess) the List index 0 of the itemdata(i) in the for next statement is picking it up. in which case 2 possible solutions are
1. start the for i = statement at 1 (this is pure guess work but worth a try)
2. right(stremailto(len(stremailto)-10)

the second of these would trim the words &quot;email name&quot; and the following semicolon from the string stremailto.

If it is before each email address then a similar Right statement on the ItemData removing the words email name would need to be built inside the For...Next statement.

Hopefully this will point you in the right direction.

Redapples.


Want the best answers? See FAQ181-2886

 

[martinot]

Thanx Redapples !! Sorry to get back to you so late but I was away for 3 weeks !
Setting the list index to 1 does the trick. Unfortunately I just found out I cannot use the EmailName in column 1. Well actually I can but I can't use the column 1 as bound column then. I need to have the column 2 'organisationID' as bound column as I use 'organisationID' to open (by double-clicking the listbox 'lstresults' ) another form.

Private Sub lstResults_DblClick(Cancel As Integer)

DoCmd.OpenForm &quot;frmResultsDetailsContact&quot;, acNormal, , &quot;[OrganisationID] = &quot; & Me.lstResults
End Sub

Can you think of a way to get around that problem ?

Thanks again for your help !! I wouldn't have gotten that far without you !!

Chiron

 

[martinot]

Well thx for your help again !! I managed to sort out my prob differently so everything works fine

Cheers

Marti

 

[redapples]

Seems you sorted this problem before I saw your latest post, was off site. Glad its all working. It might help other users for you to post your solution in this thread.

Redapples

Want the best answers? See FAQ181-2886

 

[martinot]

I'm not sure that would help anyone but you're right I should definitely explain my solution.

So here it is !!
I just kept column 1 'emailname' as bound column and used
the .column(i,j) to point to the second column for opening the detailed form
i.e.
DoCmd.OpenForm &quot;frmResultsDetailsContact&quot;, acNormal, , &quot;[OrganisationID] = &quot; & Me.lstResults.column(1)

Thx again for your help !!

Marti