VBA help - Sending report to contacts from a query/table

[kshope25]

Hi

I"m a novice at anything access, so I apologize in advance for my ignorance. I am trying to get access to email a report to email addresses in a query (qry_contacts). Filed Names within the query is called "email". I am able to get access to generate the email, but the only way I can get anything in the "TO" field is if i hard code it in the VBA (type the actual email address in there). Please see below for my code. I appreciate your help in advance. I apologize also, if there are a million examples of this elsewhere. But if you point me to one of them, I'm not going to be able to figure out how that applies to mine. Thanks. Also, I dont know if it matters or not, but I'm using Lotus Notes as my email client.



Private Sub MAIL_Deferred_DTSR_List_Click()
On Error GoTo Err_MAIL_Deferred_DTSR_List_Click

Dim stDocName As String

stDocName = "rpt_Deferred_DTSR_List_By_Plant"
DoCmd.SendObject acReport, stDocName, "Rich Text Format"

Exit_MAIL_Deferred_DTSR_List_Click:
Exit Sub

Err_MAIL_Deferred_DTSR_List_Click:
MsgBox Err.Description
Resume Exit_MAIL_Deferred_DTSR_List_Click

End Sub​

 

[AUGuy]

I'm writing up a vbscript for you, for clarification, what is the field name that contains the email addresses you want to extract from the query?

 

[kshope25]

Thank you so much! I really appreciate it! Query name is (qry_contacts), feild name is (email)

 

[AUGuy]

This should do what you need it to

Sub EmailFromQuery()
Dim rs As Recordset
Dim strRecipients As String
Dim strDocName As String
 
strDocName = "rpt_Deferred_DTSR_List_By_Plant"
 
On Error GoTo Err_Handler:
'open the query containing the email addresses as a recordset
Set rs = CurrentDb.OpenRecordset("qry_contacts", dbOpenSnapshot)
 
rs.MoveFirst
'loop through the query results, appending each email address to a string variable
Do Until rs.EOF = True
    strRecipients = strRecipients & rs("email") & ";"
    rs.MoveNext
Loop
'change the True to False at the end of the following statement to send the email without opening a window for you to view
DoCmd.SendObject acSendReport, strDocName, , strRecipients, , , "Email subject goes here", "Message body goes here", True
 
rs.Close
Exit Sub
 
Err_Handler:
MsgBox Err.Description
rs.Close
 
End Sub

 

[kshope25]

Thanks so much for your help... i'm getting an error report, but I think I can track it down from here!
I really appreciate it!

 

[AUGuy]

No problem. It worked for me when I tested it, but if you're still having problems let me know and I can dig into it further.

 

[AUGuy]

Thanks so much for your help... i'm getting an error report, but I think I can track it down from here!
I really appreciate it!

Just noticed i forgot to throw the exit sub in front of the err handler. that would return a empty msg box every time it was run. I've modified the code above.

 

[kshope25]

Thanks... one more thing. I have it tied to a command button called (command198). is there any way to tie that into the code above?

 

[kshope25]

or actaully i dont have it tied into the command button but i'd like to. i have a command button on my form called (command198) and i'd like to be able to tie that code to that command button...meaning when I hit that command button, it mails the report to those particular contacts in qry_contacts.

 

[AUGuy]

or actaully i dont have it tied into the command button but i'd like to. i have a command button on my form called (command198) and i'd like to be able to tie that code to that command button...meaning when I hit that command button, it mails the report to those particular contacts in qry_contacts.

Click on the command button, click on it's properties. goto Events, find the OnClick event and type in EmailFromQuery

 

[kshope25]

got it!! Thanks again for everything! You are a lifesaver! This helps me out more than you know!!!