Email from Access

[halem2]

Hi folks:

VBA newbee. Running Access 2000. I have a Vendor form that shows a vendor at a time. I need to send an email to the vendor by either clicking on the vendor email box or a button on the form. I also need to pick the correct email address based on the vendor currently displayed.

This is what I have so far. It only picks the email of the first vendor regardless if I'm on a different one.

this is a code I found on the web and I'm trying to customize it to fit my needs:

Private Sub Label1555_Click()

On Error GoTo Err_cmdMailTicket_Click
Dim stWhere As String '-- Criteria for DLookup
Dim varTo As Variant '-- Address for SendObject



varTo = DLookup("[VendorEmail]", "[Vendor]")

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

Err_cmdMailTicket_Click:

End Sub

thanks for any help you can give.

 

[KeithG]

You need to add criteria to you dLookup.

 

[halem2]

ok...I did that but it still only reads the first record and not the one it should. After it opens outlook using the first record's email address then it open a seond outlook unaddressed.

Private Sub Label1555_Click()

On Error GoTo Err_cmdMailTicket_Click
Dim stWhere As String '-- Criteria for DLookup
Dim varTo As Variant '-- Address for SendObject
Dim stWho As String '-- Reference to frm Vendor
'-- Looks up email address from TblUsers
stWhere = "Vendor.VendorEmail = " & "'" & stWho & "'"


varTo = DLookup("[VendorEmail]", "[Vendor]", stWho)

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

Err_cmdMailTicket_Click:

End Sub

 

[KeithG]

stWhere = "Vendor.VendorEmail = " & "'" & stWho & "'"

Shouldn't you use stWhere as your criteria for the Dlookup? Also I don't see where you are assigning a value to stWho.

 

[modest]

Keith is saying, change this:

varTo = DLookup("[VendorEmail]", "[Vendor]", stWho)

To

varTo = DLookup("[VendorEmail]", "[Vendor]", stWhere)

Additionally, your stWhere is messed up, so change

stWhere = "Vendor.VendorEmail = " & "'" & stWho & "'"

To

stWhere = "[VendorEmail] = '" & stWho & "'"

 

[modest]

[COLOR="blue"]Private Sub[/COLOR] Label1555_Click()
[COLOR="Blue"]On Error GoTo Err_cmdMailTicket_Click[/COLOR]

[COLOR="blue"]Dim[/COLOR] stWhere [COLOR="blue"]As String  [/COLOR][COLOR="green"]'-- Criteria for DLookup[/COLOR]
[COLOR="blue"]Dim[/COLOR] varTo [COLOR="blue"]  As Variant [/COLOR][COLOR="Green"]'-- Address for SendObject[/COLOR]
[COLOR="blue"]Dim [/COLOR]stWho [COLOR="blue"]  As String  [/COLOR][COLOR="green"]'-- Reference to frm Vendor[/COLOR]

[COLOR="green"]'-- Looks up email address from TblUsers[/COLOR]
stWhere = "[VendorEmail] = '" & stWho & "'"

varTo = DLookup("[VendorEmail]", "[Vendor]", stWhere)

[COLOR="Green"]'Write the e-mail content for sending to assignee[/COLOR]
DoCmd.SendObject , , acFormatTXT, varTo, , , , , -1

Err_cmdMailTicket_Click:

[COLOR="Blue"]End Sub[/COLOR]

 

[halem2]

thanks everyone. I went a different route...

On Error GoTo Err_cmdemail_Click

Dim stMailadress As String

If IsNull([Forms]![frmMaterials]![VendorEmail].Value) Then
MsgBox "No Email address", vbOKOnly

GoTo Exit_cmdemail_Click
Else
stMailadress = [Forms]![frmMaterials]![VendorEmail].Value
FollowHyperlink "mailto:" & stMailadress
End If

Exit_cmdemail_Click:
Exit Sub

Err_cmdemail_Click:
MsgBox Err.Description
Resume Exit_cmdemail_Click

it does what I need. Thanks again to all.