Creating new email and DLookup problem

[mrtn]

Hi

I have got some basic code that creates an email in which body text depends on a value od my Combobox. I have created a small table with pre-defined Email messages and their descriptions. The comboox on a form referrs to description of an email message and a dlookup returns an appropriate message.

The problem is that even though description matches the value in a combobox, the pre-defined body is not inserted and returns null value.

My email table is designed as follows:

ID | Description | Body

1 | PaperworkIn | abc
2 | ClientCode | def

I have used the code I have found somewhere on the internet and it looks as follows:

Private Sub Command228_Click()
 
'******begin code******
Dim Email As Variant
Dim CaseName As String
Dim Content As String
 
'**create variables for Outlook
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!Email
ref = Me!CaseName
Content = Nz(DLookup("[Body]", "[Emails]", "[Description]='" & [Combo234] & "'"), "")
 
'***creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)
 
'***creates and sends email
With objEmail
    .To = Email
    .Subject = " Documents received - " & ref & ""
    .Body = Content
    .Display 'sends the email in Outlook.  Change to DISPLAY if you want to be able to
      'modify or see what you have created before sending the email
End With
Set objEmail = Nothing
'****end code****
End Sub

 

[pbaldy]

If the combo holds the ID, you'd want that in the criteria. That said, I'd avoid the DLookup and use this:

http://www.baldyweb.com/Autofill.htm

and simply refer to the appropriate column to get your text.

 

[mrtn]

Fantastic! Thanks

 

[pbaldy]

Happy to help!