Selecting specific data from anonther table.

[Armitage2k]

Hi!

I am a little experienced with VBA from an excel point of view, however, am a complete newbie to access. In that relation, I am running into a probably minor problem:

I wrote a piece of vba which lets me create an email with data from the entry currently shown on the form. However, since I do not want to write the whole email body into the sub, I am trying to pull the strbody variable from another table, specifically a memo field of another table. Now my problem is, HOW can I address one specific piece of data of a table?

here my line:

Private Sub SendEmail2_Click()
Dim EmailApp, NameSpace, EmailSend As Object
Dim strTitle, strFn, strLN, strbody As String


Set EmailApp = CreateObject("Outlook.Application")
If EmailApp Is Nothing Then Set EmailApp = CreateObject("Outlook.Application")
Set NameSpace = EmailApp.GetNamespace("MAPI")
Set EmailSend = EmailApp.CreateItem(0)


strTitle = Me![Title]
strFn = Me![First Name]
strLN = Me![Last Name]
[B]strbody = "Dear " & strTitle & ". " & strFn & " " & strLN & "," & Chr(13) & Chr(13)
strbody = strbody & "Warm greetings from me![/B]

If IsNull([Email]) Or ([Email]) = "" Then
       MsgBox "There is no E-mail address entered for this person!"
       Exit Sub
       
    Else
    
    With EmailSend
        .Subject = "Warm Greetings from Crowne Plaza Shenzhen!"
        .To = Me.Email
        '.CC = ""
        '.From = "patrick.maurer@cpsz.com"
      [B].Body = strbody[/B]
        '.Attachments.Add "C:\attachment.txt"
        '.Attach = strFilePath & "\" & strFileName
        '.AttachFile strFilePath & "\" & strFileName, strFileName
        .Display
    End With
    
End If

Set EmailApp = Nothing
Set NameSpace = Nothing
Set EmailSend = Nothing

End Sub

Now instead of using the bold part of the code, I would like to pull the email body from another table named 'Emails' and its column 'Message'. The message I need would have keynumber 3, respectively is the first line of data of the 'Message' column.

sort of like this:

set strbody = Emai!Message!3

but without success.

Can someone point me in the right direction please?
thanks
A2k

 

[pbaldy]

You could try a DLookup:

strbody = DLookup(...)

More info on syntax here:

http://www.mvps.org/access/general/gen0018.htm

 

[Armitage2k]

works like a charm, thanks a lot!
A2k

 

[pbaldy]

No problemo, glad it worked for you.