Hi All

I've robbed this code form this forum - thanks v much. And I can get it to work if I put "" marks around stSubject on the DoCmd line. But I don't want people to see "stSubject" but rather the content of stSubject.

How can I do this?


Sub test()

Dim rsEmail As DAO.Recordset
Dim strEmail As String
Dim stSubject As String
Set rsEmail = CurrentDb.OpenRecordset("EMailTable")

If rsEmail.Fields("Today").Value = Date Then
Do While Not rsEmail.EOF
strEmail = rsEmail.Fields("EMailAddress").Value
stSubject = rsEmail.Fields("Intervention").Value
DoCmd.SendObject , , , strEmail, , , stSubject, "This is today's message", False

rsEmail.MoveNext

Loop
Set rsEmail = Nothing
End If

End Sub

Make sure you are actually getting a value in stSubject because if you are getting a null from rsEmail.Fields("Intervention").Value then it will squawk at you since it can't assign a null to the subject line.

You can also try doing this:
change

stSubject = rsEmail.Fields("Intervention").Value

to this:

stSubject = Nz(rsEmail.Fields("Intervention").Value,"")

Hi Bob

Thanks for that - I changed the code to your suggested line and it works a treat.

Can you talk me through how this works : stSubject = Nz(rsEmail.Fields("Intervention").Value,"")

Cheers

JP

Hi Bob

Thanks for that - I changed the code to your suggested line and it works a treat.

Can you talk me through how this works : stSubject = Nz(rsEmail.Fields("Intervention").Value,"")

Cheers

JP

When assigning a value to a variable, you want to avoid null values, so if it is possible that nulls might occur you can use the NZ function which replaces nulls with something of your choosing. In this case ("") it is an empty string.