Email address only in current record of form

[mterrill]

Hi, I am new here and new to VBA so any help will be greatly appreciatied. I have a form in my database (Access 2003) and a button to email a report based on the form, I am using a parameter in the query the report is based on to limit the report to a single record (i don't know if this is the best approach or not). I would also like to email a person based on an entry in the form and have the email address show up automatically along with the report attached when the email button is clicked. I have tried using docmd.sendobject but it will not let me specify the email address based on user entry in the form. I have tried a couple of code variations which i will add below. It should be noted that the email address field is not in the form but tied to it via a lookup (displays name of the person the email belongs too) of another table. If I add the email address to the form will it be easier to accomplish my goal? Let me know if you need any more information or clarification.

Here are the two of the codes i have tried:

1.

Dim rs As New ADODB.Recordset
rs.Open "MyQuery", CurrentProject.Connection

DoCmd.SendObject acSendReport, "ReportName", acFormatRTF, rs("Email"), , , "New Report Request", "Please find the attached report request, if you have any questions please contact the DS lead for request.", , True
rs.MoveNext

rs.Close
Set rs = Nothing

2.

Dim stDocName As String
Dim stToName As String
Dim stCCName As String
Dim stSubject As String
Dim stMessage As String

stDocName = "MyReport"
stToName = DLookup("", "MyQuery")
stCCName = "[EMAIL="email@email.com"]email@email.com"
stSubject = "New Report Request"
stMessage = "Attached is a new report request, if you have any questions contact the DS Lead."

DoCmd.SendObject acSendReport, stDocName, acFormatRTF, stToName, stCCName, stSubject, stMessage, , True

Thanks in advance and sorry for it being so long.

 

[CraigDolphin]

Have you used the Debug options in vba to step through the code and see what is going wrong?

To do this, open the vba code window to the code you want to check. Left-click on the gray border to the left of the code itself (try doing this at the line stDocName = "MyReport"). A brown dot should appear where you clicked. This is a code interuption flag and you can remove it by left-clicking it after you're done debugging.

Then, close the vba window and trigger the event that starts the code running. The vba window will automatically open once the code reaches the line you marked with the flag.

You can then step through the code by pressing F8. That way, you can use the immediate window or simply mouseover the variables to see what the values are at each step along the way. This allows you to see the actual values in your final sendobject command. My guess is that maybe there's a null result from the line:

stToName = DLookup("", "MyQuery")

This could be caused by a misspelling of the field name (case sensitive!) or by no record being returned by the query (for example if the parameter in the query cannot be accessed ...e.g., form closed, or some other issue), or by there being no email address entered for that record.

If so, you might want to use the Nz() function to specify what value to return if the DLookup returns no result. You could then test the value and only attempt the sendobject if a valid email address exists, and provide a suitable explanation in a message box if not.

All that said, I am not that familiar with the sendobject aspect of this (I never use it) so there may be other issues at play here. Regardless, knowing how the debugger works is very helpful for figuring out all manner of code glitches.