Code for an emails "To" field

[dfsabrown]

Hi

i have a command button on a form that creates and outlook email with an attachment. I would like to populate the "to" and "cc" fields of that email with email addresses which are completed on a different form in the database. but i just can't seem to work it out.

I want the "to" field to be populated from a form called "Case Details" and the field is "Manager email"


this is my current code:

Private Sub Command122_Click()

Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook


.Attachments.Add ("C:\Users\Sarah Brown\Documents\Banking info request auth.doc")


.To = ""

.Subject = "Authorisation for banking information request"

.Body = "complete attached file and email to case manager (cc the case inbox)"

.Display

End With
'MsgBox MailOutLook.Body

Exit Sub
email_error:
MsgBox "An error was encountered." & vbCrLf & "The error message is: " & Err.Description
Resume Error_out
Error_out:
End Sub



thanks

S

 

[pbaldy]

.To = Forms!FormName.TextboxName

 

[dfsabrown]

thanks Paul, this is what i have added,

.To = Forms!Case Details.Manager Email

but now get a "Compile error: Syntax error" message

so i changed it to:
.To = Forms![Case Details].[Manager Email]

now get a message saying "Run time error 2450: cannot find the referenced form 'Case Details'" but i can see it under forms on the objects list!

 

[Wayne]

Try putting a bang instead of a dot like this:
.To = Forms![Case Details]![Manager Email]

Or is the "Manager EMail" field on a subform instead of the main form?

Wayne

 

[pbaldy]

The form would need to be open; is it?

The brackets are required because of the inadvisable spaces.

 

[dfsabrown]

Paul, yes it works when the Case details form is open, but is there a away of doing it when the form is closed? How about it if i link the two tables (supporting the forms) by a query, would that work?

 

[pbaldy]

You can only use a form if it's open. You could use a DLookup() to get a value directly from the table.