sendobject command

[DCV0204]

I hope someone can tell me the best way to to this:

I have a button that on click I send an email message to staff letting them know letters were mailed. I would like to add code to auto populate the number of letters based off a query I have created that contains the count.

QueryName= "ltrcount"
FieldName="sndltrcount" (this field calculates the number of records using GroupBy/Count)

Can I auto populate the calculation from this field from query in the message being sent below?



Here is my code:
DoCmd.SendObject acSendNoObject, , , "FName.LName@tn.gov", "FName.LName@tn.gov", , "Non-Registered Prescriber Notification", "FName, " & [sndltrcount] & "letters have been mailed to a list of Non-Registered Prescribers," & vbNewLine & "if any of the letters come back undeliverable; please deliver to ______." & vbNewLine & vbNewLine & "Do Not log these in the Return Mail Database" & vbNewLine & vbNewLine & "Attached is a copy of the Template Letter." & vbNewLine & vbNewLine & "Thanks," & vbNewLine & "____", 1

Thanks,
Lisa

[pbaldy]

You can use a DLookup() to get the value of that field from the query and put it in the email. By the way, at some point it's easier to use a variable for the body of the email (or any argument that starts to get long).

[DCV0204]

thanks I get a number when the email opens but it is the wrong number.

attached is a print screen of my open query showing a count of 185.

My email only shows 8.

Can you tell me why it is only picking up the 8?
Thanks,

[pbaldy]

Put the field in quotes too.

[DCV0204]

Here is my final code that works. Thanks pbaldy for your expertise....

DoCmd.SendObject acSendNoObject, , , "FName.LName@tn.gov", "FName.LName@tn.gov; FName.LName@tn.gov", , "Non-Registered Prescriber Notification", "FName, On " & DLookup("Send_LtrDate", "NumLtrsMailed") & ", " & DLookup("Count", "NumLtrsMailed") & " letters were mailed to a list of Non-Registered Prescribers," & vbNewLine & "if any of the letters come back undeliverable; please deliver to FName." & vbNewLine & vbNewLine & "Do Not log these in the Return Mail Database" & vbNewLine & vbNewLine & "Attached is a copy of the Template Letter." & vbNewLine & vbNewLine & "Thanks," & vbNewLine & "FName", 1

Thanks,
DCV0204

[pbaldy]

Happy to help.