Generate email from access form

[oggsta]

Hi

I would like to generate an email using fields from my database from a command button.

The following code below works well if all the fields are located within the form I am in at the time when I click on the button.

However I want to get the email address from a different form. Forms!clientdetails!clientemail

If I place this in the code I receive an error. I.e email = Forms!clientdetails!clientemail

Is there a way I can use a query such as DoCmd.OpenQuery "mergequery",

If so how would I change the code below to make reference to the query

Thanks


Private Sub Command103_Click()
Dim email, Sitename, sitespecificnumber, Client, Region As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem


'**gathers information from your form. this sets the string variable to your fields
email = Me.clientemail
Sitename = Me.Sitename
sitespecificnumber = Me.sitespecificnumber
Client = Me.Client
Region = Me.Region

'***creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

'***creates and sends email
With objEmail
.To = clientemail
.Subject = Sitename & " " & sitespecificnumber & " " & Client
.Body = Region
.Display
End With


Exit Sub
'****end code****

End Sub

 

[PHV]

You may take a look at the DLookUp function.

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

 

[oggsta]

Thanks, got it
Works well as below.


Dim strEmail, strBody As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

'**creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

'**************************************************************
'*create string with email address

strEmail = strEmail & DLookup("clientemail", "mergequery", [ClientID])

strBody = strBody & DLookup("Clientfirstname", "mergequery", [ClientID]) & Chr(13)
strBody = strBody & "" & Chr(13)
strBody = strBody & "" & Chr(13) & Chr(13)
strBody = strBody & "Regards" & Chr(13)
strBody = strBody & Chr(13)
strBody = strBody & DLookup("name", "mergequery", [employeeID]) & Chr(13)
strBody = strBody & DLookup("empjobtitle", "mergequery", [employeeID]) & Chr(13)
strBody = strBody & DLookup("empcompany", "mergequery", [employeeID]) & Chr(13)
strBody = strBody & Chr(13)
strBody = strBody & "m: " & DLookup("empmobile", "mergequery", [employeeID]) & Chr(13)
strBody = strBody & "t: " & DLookup("emptelephone", "mergequery", [employeeID]) & Chr(13)
strBody = strBody & "f: " & DLookup("empfax", "mergequery", [employeeID]) & Chr(13)
strBody = strBody & "e: " & DLookup("empemail", "mergequery", [employeeID]) & Chr(13)
strBody = strBody & Chr(13)


'***creates and sends email
With objEmail
.To = strEmail
.CC = ""
.Subject = Me.sitespecificnumber & " " & Me.Sitename & " Update"
.Body = strBody
.Display
End With

Exit Sub