Database Text Field Does Not Evaluate

[KevinBrennan]

I have an Access application with a home-built email generator. It pulls the text of the message from a table. The message text includes variable names that are set by a program. For instance

msg="Dear " & FName & ", " & vbCRLF & " blah..."
FName = "Bill"

Now, msg is pulled correctly from the table and FName is set by the program. When I launch Outlook, I get an error that FName is not found, and the body of the message contains the string for msg without processing the concatenations and resolving the value for FName.

I hope this is clear. PLease help!

 

[Remou]

Please post more code.

 

[KevinBrennan]

msg="Dear " & FName & ", " & vbCRLF & " blah..."
' msg is pulled form database table

With rs1
.MoveFirst
Do While Not rs1.BOF And Not rs1.EOF
If Not IsNull(!EMail) Then
Resp = MsgBox("OK to Process EMail Message?", vbOKCancel)
If Resp = vbOK Then
Recip = !EMail
FName = ![Name_First]
RetCode = SendMail(Recip,Subj,Msg)
End If
End If
.MoveNext
Loop
End With

SendMail is simple function that starts mail object and sends.
Result is "Dear " & strFName & "," & vbCRLF & "blah.."
in outlook message. IN other words, message does not resolve variable name and CRLF. Quotes are not deleted.

 

[KevinBrennan]

If I hard code the message text in the code, it works fine. The problem is when I try to pull the exact same string from a text field in the database. Is it a quoting problem?

 

[Golom]

You're setting the value of msg before you have assigned a value to FName
Try

msg = "Blah ..."

With rs1 
   .MoveFirst
   Do While Not rs1.BOF And Not rs1.EOF
      If Not IsNull(!EMail) Then
         Resp = MsgBox("OK to Process EMail Message?", vbOKCancel)
         If Resp = vbOK Then
            Recip = !EMail
            FName = ![Name_First]
            [COLOR=red]msg = "Dear " & FName & ", " & vbCRLF & msg[/color]
            RetCode = SendMail(Recip,Subj,Msg)
          End If
       End If
       .MoveNext
   Loop
End With

 

[Remou]

It is to do with where you have put the line, I think. try:

   ' msg is pulled form database table

   With rs1 
       .MoveFirst
        Do While Not rs1.BOF And Not rs1.EOF
            If Not IsNull(!EMail) Then
                Resp = MsgBox("OK to Process EMail Message?", vbOKCancel)
                If Resp = vbOK Then
                    Recip = !EMail
                    FName = ![Name_First]

                    msg="Dear " & FName & ", " & vbCRLF & " blah..."
                    RetCode = SendMail(Recip,Subj,Msg)
                End If
            End If
            .MoveNext
        Loop
    End With

If this still does not work, perhaps you could post the SendMail function.

 

[KevinBrennan]

I apologize -- I edited my code to show that the value of msg was created in advance. In fact, my code is written as you suggested, with msg set after FName.

The problem is not in the SendMail function -- it is that the string is not being processed with the concatenation operators and the variable substitution.

 

[Remou]

What happens if you put in a message box after 'msg ='?
MsgBox msg
Does it not show up right?

 

[stix4t2]

Maybe you could post the sql statement for the recordset being used. Or maybe the code that is setting the msg variable, or why you are not using the !msg variable from the recordset.

Stix 42
Long Live Rock and Roll
Pop is for drinking