Sending email to one user

[will1128]

I've managed to create a button and get it to send an email through outlook for one specified user, but I don't know understand how to get it to choose the user of the form I'm currently on. I've done some research and it appears the DLookup is the way to do this. I don't understand how to reference my table (Person). I've seen examples that keep using Me.cboAssign, but I don't know what this means.

Could someone explain this to me and maybe help me out a little?

Here's the code I'm stuck on:

Private Sub Command69_Click() 'send email
On Error GoTo Err_Command69_Click
Dim stWhere As String 'criteria for a DLookup
Dim varTo As Variant 'Address for SendObject (who I am sending the e-mail to)
Dim strWho As String 'ref to Personnel table
Dim strText As String 'body of the email
Dim strSubj As String 'subject of the email
stWho = " " 'not sure what belongs here to use below
stWhere = "Personnel.UserID = " & "'" & stWho & "'"
varTo = DLookup("", "Person", stWhere)
strSubj = "Email sent"
strText = "Dear User" & " "
DoCmd.SendObject acSendNoObject, , , varTo, , , strSubj, strText, -1
Exit_Command69_Click:
Exit Sub
Err_Command69_Click:
MsgBox Err.Description
Resume Exit_Command69_Click

End Sub

 

[TedMartin]

Before I send you all the code you need; one quick question. Do you just need to create an eMail with the address of the person on your form already filled in so that the subject and body can be completed manually? If so, then the this simple button code would suffice, where eMail.value if the eMail addy and cmdeMail is the button

cmdeMail.HyperlinkAddress = "mailto:" + eMail.Value

If not, then respond and I will send you a full example code.

 

[TedMartin]

Here is the code anyway BUT make sure you check in Module/Tools/References the Microsoft Outlook Object Library.


Private Sub Command0_Click()

On Error GoTo Err_Send

Dim objOutlook As Outlook.Application
Dim objeMail As Outlook.MailItem

Set objOutlook = CreateObject("Outlook.Application")

Set objeMail = objOutlook.CreateItem(olMailItem)

objeMail.To = "fred@hotmail.com" ' this could be a DlookUp or a text control.
objeMail.Subject = "Your Subject"
objeMail.Body = "Your eMail text"
objeMail.Send


Exit_Send:
Set objOutlook = Nothing
Set objeMail = Nothing

Exit Sub

Err_Send:
MsgBox Err.Number & vbLf & Err.Description, vbCritical, "eMail send"
Resume Exit_Send


End Sub

 

[ghudson]

The OP is asking how to get the email address for the current record.

Google can be your friend. Check these threads out on how to use the Dlookup function to get the email address for the current record...

http://www.access-programmers.co.uk/forums/showthread.php?t=168199

http://www.utteraccess.com/forum/Dlookup-Email-Address-Ba-t1939942.html&p=1943956

 

[will1128]

I do need ot have the email looked up from the form I'm on and other information. I've seen examples, but I'm so inexperienced with VBA I don't know what to do next.

So the body of the email message will have other values from a table.

 

[Balder]

Hi

The following code works great for me.
In is in the ON CLICK event of a button on my form.

Private Sub sendmailbutton_Click()
'send email to email adr. in the email field.'
'If email field is empty tell user that there is no email adr.'
On Error GoTo PROC_ERR

Dim stDocName As String
Dim stLinkCriteria As String
Dim Subject As String
Dim Message As String

If IsNull() Or ([email]) = "" Then
MsgBox "The emaild field is empty !"
Exit Sub
Else
stLinkCriteria = Me![email]
stSubject = ""
DoCmd.SendObject acSendNoObject, , , stLinkCriteria, , , stSubject
End If

proc_exit:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " " & Err.Description
Resume proc_exit
End Sub[/B]

Good luck

Balder
Norway