sending emails from queries

[kjspear]

Hello everyone,

I have a question about sending emails using access 2002. I have created a database with contains customer information such as names, addresses, phone numbers and email addresses. OK, I set up a query for all customers that preferred to be contacted by email. I would like to know is there a way that Access can automatically send an email to all of these customers from the query? I'm using Outlook 2003. If I can get Access to simply place all of the email addresses in to 'To' or 'Bcc' fields of Outlook, that would be excellent. I then would compose the message and or attachment from there. My manager is trying to reduce administrative hours.

Any assistance will be apreciated.

Thanks,
KJ

 

[SiJP]

KJ, yes this can be done.. no not directly from the query window.

You would need to write a small vba routine to do this that uses the SendObject function.

Let me know if you need some more help with this.

------------------------
Hit any User to continue

 

[kjspear]

Hello, Thanks for your prompt response.

Yes I would like some help with this one. I'm not too familiar with vba yet.

Thanks,
KJ

 

[SiJP]

You will need to create a form that has one button on it.. create an onclick event for this button which calls fSendEmail. Then dump this code below the onclick event

Function fSendEmail()
On Error GoTo fSendMail_Err

    Dim rs As DAO.Recordset
    Dim strEmailTo As String
    
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryYourQueryName")
        rs.MoveFirst
        While Not rs.EOF
            If strEmailTo <> "" Then
             strEmailTo = strEmailTo & "; " & rs!FieldOfEmailAddress
            Else
             strEmailTo = rs!FieldOfEmailAddress
            End If
            rs.MoveNext
        Wend
        
    DoCmd.SendObject , "", "", strEmailTo, "", "", "Message Subject", "Any message body you wish to include by default, True, """

fSendMail_Exit:
    Exit Function
    
fSendMail_Err:
    MsgBox Err.Number & " " & Err.Description, vbOKOnly + vbExclamation
    Resume fSendMail_Exit:
End Function

------------------------
Hit any User to continue

 

[kjspear]

O.K., thanks. I will give this a try and let you know of the status.

KJ

 

[kjspear]

Ok,tried the code. I think I'm missing something. When I create the button this code appears;

Private Sub Command0_Click()

End Sub

I guess the code goes between the two lines? When I place the suggested code, Access prompts me for the macro name. It is because I didn't call fSendEmail? How should this look?

Thanks,
KJ

 

[SiJP]

yup

Private Sub Command0_Click()
Call fSendEmail
End Sub


------------------------
Hit any User to continue

 

[kjspear]

O.K.. Now I've got it!

Thanks a lot.

KJ

 

[keymania]

I'm also needing this same procedure. I'm getting a compile error "sub or function not defined" at the sub line.

A form with one button...
Private Sub Command0_Click()
Call fSendEmail
End Sub

Function fSendMail()
....exactly as written
End Function

What's my error?

 

[lespaul]

I believe that you need to declare Function fSendMail as public. Not sure how you do that in Access though.

Sorry I can't help more, but maybe that will point you in the right direction!

Leslie

 

[kjspear]

O.K., Keymania perhaps I can help you since I just went through this.

What I was doing was once the user clicks on a button, the query is ran and then Outlook populates the 'To:' with all the email addresses from the query.

If this is something that you are trying to do then please provide me a sample of your code.

KJ

 

[kjspear]

I have a database that sends emails out to customers based on queries.
For example, I want to send emails to our Premier Customers only. I make the selection and the query creates 6 records.

The problem I'm having is that this will not take the 6 emails from the query file which is named
'BSNMAILING Queryfgemails'. Instead it only goes to the table named 'BSNMAILING'. Then when Outlook opens,
it places all the email addresses from the table rather than just the ones from my saved query.
Is there any way that I can get this code to use the query that is saved with the 6 emails?

I have the following code below;

Function fSendEmail()
On Error GoTo fSendMail_Err

Dim rs As DAO.Recordset
Dim strEmailTo As String

Set rs = CurrentDb.OpenRecordset("SELECT * FROM BSNMAILING Queryfgemails")
rs.MoveFirst
While Not rs.EOF
If strEmailTo <> "" Then
strEmailTo = strEmailTo & "; " & rs!EMAIL
Else
strEmailTo = rs!EMAIL
End If
rs.MoveNext
Wend

DoCmd.SendObject , "", "", strEmailTo, "", "", "Message Subject", " ", True, """"

fSendMail_Exit:
Exit Function

fSendMail_Err:
MsgBox Err.Number & " " & Err.Description, vbOKOnly + vbExclamation
Resume fSendMail_Exit:
End Function

------------------------------------------------------
Any assistance would be appreciated.

Thank you,
KJ

 

[PHV]

Replace this:
("SELECT * FROM BSNMAILING Queryfgemails")
By this:
("SELECT * FROM [BSNMAILING Queryfgemails]")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kjspear]

O.K., I think I know what the problem is now. As the code listed above, if I'm correct, a query is simply a filter and not an actual file? The VB code does what it suppose to do in that it pull out the email addresses from the EMAIL field and places them in the 'To:' field in Outlook. But I also need it to place only the email addresses that reflect the current date only. I have an update query that updates the Last Contact Date field based on the selection of the query which works fine. But now I need it to give me only the current date. So i.e. the update query will place lets say 12/19/04. Therefore, I would need all the email addresses for 12/19/04 only. Right now it appears that it's taking the email addresses from the entire database.

I'm sure it's a line or a few lines of small code. But I'm still learning VB.

Any assistance will be appreciated.

Thank you,
Kyle

 

[GingerR]

duplicate post thread705-970609