Email from Access

  • Thread starter Thread starter Henk
  • Start date Start date
H

Henk

Can some one help me.
In my database is an datafield a Email adress.
I want to select with a querie al the items with an email
adress. Then export the query in MS Outlook as an "Send
to" item.
I've tried everything but i cannot get it rigt.
Is there someone out there who can help me?
 
Henk said:
Can some one help me.
In my database is an datafield a Email adress.
I want to select with a querie al the items with an email
adress. Then export the query in MS Outlook as an "Send
to" item.
I've tried everything but i cannot get it rigt.
Is there someone out there who can help me?
Click to expand...

Here's some working code to send the results of a query to Outlook, with an
attachment, if one exists. If you uncomment the.Send line, it will send it
directly off without opening it first:

Function EmailBrokers(strTo As String, strSubject _
As String, Optional varMsg As Variant, Optional varPath As String =
"")
' ©Arvin Meyer 1999-2003.
' Permission to use is granted if copyright notice is left intact.
' Permisssion is denied for use with unsolicited commercial email

' Set reference to Outlook
On Error GoTo Errhandler
Dim strBCC As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim objOutl As Outlook.Application
Dim objEml As Outlook.MailItem
Dim i As Integer

Set db = CurrentDb

Set qdf = db.QueryDefs!qryEmail
qdf.Parameters(0) = [Forms]![frmEmail]![txtStartDate]
qdf.Parameters(1) = [Forms]![frmEmail]![txtEndDate]

Set rst = qdf.OpenRecordset()

Set objOutl = CreateObject("Outlook.application")
Set objEml = objOutl.createItem(olMailitem)

With rst
If .RecordCount > 0 Then
.MoveLast
.MoveFirst
End If
End With

For i = 1 To rst.RecordCount
If Len(rst!EmailAddress) > 0 Then
strBCC = strBCC & rst!EmailAddress & ";"
End If
rst.MoveNext
Next i
strBCC = Left$(strBCC, Len(strBCC) - 1)

'Debug.Print strBCC

With objEml
.To = strTo
.BCC = strBCC
.Subject = strSubject

If Not IsNull(varMsg) Then
.Body = varMsg
End If

If Len(varPath & vbNullString) > 0 Then
.Attachments.Add varPath
End If

.Display
' .Send
End With

ExitHere:
Set objOutl = Nothing
Set objEml = Nothing
Set rst = Nothing
Set db = Nothing

Exit Function

Errhandler:
MsgBox Err.Number & ": " & Err.Description
Resume ExitHere

End Function
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Thanks for the quick reaction
-----Original Message-----
Henk said:
Can some one help me.
In my database is an datafield a Email adress.
I want to select with a querie al the items with an email
adress. Then export the query in MS Outlook as an "Send
to" item.
I've tried everything but i cannot get it rigt.
Is there someone out there who can help me?
Click to expand...

Here's some working code to send the results of a query to Outlook, with an
attachment, if one exists. If you uncomment the.Send line, it will send it
directly off without opening it first:

Function EmailBrokers(strTo As String, strSubject _
As String, Optional varMsg As Variant, Optional varPath As String =
"")
' ©Arvin Meyer 1999-2003.
' Permission to use is granted if copyright notice is left intact.
' Permisssion is denied for use with unsolicited commercial email

' Set reference to Outlook
On Error GoTo Errhandler
Dim strBCC As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim objOutl As Outlook.Application
Dim objEml As Outlook.MailItem
Dim i As Integer

Set db = CurrentDb

Set qdf = db.QueryDefs!qryEmail
qdf.Parameters(0) = [Forms]![frmEmail]![txtStartDate]
qdf.Parameters(1) = [Forms]![frmEmail]![txtEndDate]

Set rst = qdf.OpenRecordset()

Set objOutl = CreateObject("Outlook.application")
Set objEml = objOutl.createItem(olMailitem)

With rst
If .RecordCount > 0 Then
.MoveLast
.MoveFirst
End If
End With

For i = 1 To rst.RecordCount
If Len(rst!EmailAddress) > 0 Then
strBCC = strBCC & rst!EmailAddress & ";"
End If
rst.MoveNext
Next i
strBCC = Left$(strBCC, Len(strBCC) - 1)

'Debug.Print strBCC

With objEml
.To = strTo
.BCC = strBCC
.Subject = strSubject

If Not IsNull(varMsg) Then
.Body = varMsg
End If

If Len(varPath & vbNullString) > 0 Then
.Attachments.Add varPath
End If

.Display
' .Send
End With

ExitHere:
Set objOutl = Nothing
Set objEml = Nothing
Set rst = Nothing
Set db = Nothing

Exit Function

Errhandler:
MsgBox Err.Number & ": " & Err.Description
Resume ExitHere

End Function
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.554 / Virus Database: 346 - Release Date: 12/20/2003


.
Click to expand...
 
Hi Arvin,

Would you mind posting the SQL clause for qryEmail and any code that is behind frmEmail?
I'm not sure what qdf.Parameters(0) and qdf.Parameters(1) refers to....

Tom
_____________________________________


Henk said:
Can some one help me.
In my database is an datafield a Email adress.
I want to select with a querie al the items with an email
adress. Then export the query in MS Outlook as an "Send
to" item.
I've tried everything but i cannot get it rigt.
Is there someone out there who can help me?
Click to expand...

Here's some working code to send the results of a query to Outlook, with an
attachment, if one exists. If you uncomment the.Send line, it will send it
directly off without opening it first:

Function EmailBrokers(strTo As String, strSubject _
As String, Optional varMsg As Variant, Optional varPath As String =
"")
' ©Arvin Meyer 1999-2003.
' Permission to use is granted if copyright notice is left intact.
' Permisssion is denied for use with unsolicited commercial email

' Set reference to Outlook
On Error GoTo Errhandler
Dim strBCC As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim objOutl As Outlook.Application
Dim objEml As Outlook.MailItem
Dim i As Integer

Set db = CurrentDb

Set qdf = db.QueryDefs!qryEmail
qdf.Parameters(0) = [Forms]![frmEmail]![txtStartDate]
qdf.Parameters(1) = [Forms]![frmEmail]![txtEndDate]

Set rst = qdf.OpenRecordset()

Set objOutl = CreateObject("Outlook.application")
Set objEml = objOutl.createItem(olMailitem)

With rst
If .RecordCount > 0 Then
.MoveLast
.MoveFirst
End If
End With

For i = 1 To rst.RecordCount
If Len(rst!EmailAddress) > 0 Then
strBCC = strBCC & rst!EmailAddress & ";"
End If
rst.MoveNext
Next i
strBCC = Left$(strBCC, Len(strBCC) - 1)

'Debug.Print strBCC

With objEml
.To = strTo
.BCC = strBCC
.Subject = strSubject

If Not IsNull(varMsg) Then
.Body = varMsg
End If

If Len(varPath & vbNullString) > 0 Then
.Attachments.Add varPath
End If

.Display
' .Send
End With

ExitHere:
Set objOutl = Nothing
Set objEml = Nothing
Set rst = Nothing
Set db = Nothing

Exit Function

Errhandler:
MsgBox Err.Number & ": " & Err.Description
Resume ExitHere

End Function
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Tom Wickerath said:
Hi Arvin,

Would you mind posting the SQL clause for qryEmail and any code that is behind frmEmail?
I'm not sure what qdf.Parameters(0) and qdf.Parameters(1) refers to....
Click to expand...

You asked for it:

Actually, the Parameters(index) statement refers to:

Parameters(0) = [Forms]![frmMenuEmail]![txtStartDate]
Parameters(1) = [Forms]![frmMenuEmail]![txtEndDate]

When you call a query like the one below from code, you must tell it what
the parameters for the criteria are or you will get an error. In this case
the error would have expected 2 parameters. (Hint: Look at the where clause.
If the values in the criteria are variables, you must tell the SQL what
those variables are.

SELECT DISTINCT tblProspect.ProspectFirstName, tblProspect.ProspectLastName,
tblProspect.ProspectEmail AS EmailAddress, tblProspect.ProspectAddress,
tblProspect.ProspectCity, tblProspect.ProspectState,
tblProspect.ProspectZip, Trim([ProspectFirstName] & " " &
IIf(Mid([ProspectLastName],2,1)="c",[ProspectLastName],StrConv([ProspectLast
Name],3))) AS FullName, Trim(StrConv([ProspectCity],3) & ", " &
UCase([ProspectState]) & " " & [ProspectZip]) AS CSZ,
tblLot.SubdivisionCode, tblLotStatus.ActualCloseDate
FROM ((tblProspect INNER JOIN tblContract ON tblProspect.ProspectID =
tblContract.BuyerID) INNER JOIN tblLot ON tblContract.LotID = tblLot.LotID)
INNER JOIN tblLotStatus ON tblLot.LotID = tblLotStatus.LotID
WHERE (((tblProspect.ProspectEmail) Is Not Null) AND
((tblLotStatus.ActualCloseDate)>[Forms]![frmMenuEmail]![txtStartDate] And
(tblLotStatus.ActualCloseDate)<[Forms]![frmMenuEmail]![txtEndDate]) AND
((tblContract.ContractCancelled)=False))
ORDER BY tblProspect.ProspectLastName;
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Back
Top