Thanks Gasman. Error form/box (displayed in .jpg) is all I get when trying to use DKinley code here.
My code adjusted to my query (UNION) and column values are pasted below.
Thanks for giving a shot at this.
Private Sub lbl_selectGenEmail_Click()
Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String
Set MyDb = CurrentDb()
Set rsEmail = MyDb.OpenRecordset("frm_rptCommMainEmail", dbOpenSnapshot)
Do Until rsEmail.EOF
If IsNull(frm_rptCommMainNotesUNION.UEMP_EMAIL1(0)) = False Then
sToName = frm_rptCommMainNotesUNION.UEMP_EMAIL1(0)
sSubject = "PROD and CORE Schedule Changes: " & frm_rptCommMainNotesUNION.USHORT_DATE(2)
sMessageBody = "Hi. The Schedule has changed which includes your shift/shifts. Please go to the following link to insure all is correct. Email us only if this change is not correct. " & vbCrLf & _
"Field A: " & .Fields(a) & vbCrLf & _
"Field B: " & .Fields(b) & vbCrLf & _
"Field C: " & .Fields(c)
DoCmd.SendObject acSendNoObject, , , _
sToName, , , sSubject, sMessageBody, False, False
Set MyDb = Nothing
Set rsEmail = Nothing
Original code from DK with adjustment instructions:
Access Hack by Choice
Join Date: Jul 2008
Location: MidSouth, USA
Thanked 8 Times in 5 Posts
Re: Send E-mail from Query Results
Think something like this will work for you. You can set this up on a button or a timer, or what-have-you.
Dim MyDb As DAO.DatabaseDim rsEmail As DAO.RecordsetDim sToName As StringDim sSubject As StringDim sMessageBody As String Set MyDb = CurrentDb()Set rsEmail = MyDb.OpenRecordset("qryQueryName", dbOpenSnapshot) With rsEmail .MoveFirst Do Until rsEmail.EOF If IsNull(.Fields(x)) = False Then sToName = .Fields(x) sSubject = "Invoice #: " & .Fields(y) sMessageBody = "Email Body Text " & vbCrLf & _ "Field A: " & .Fields(a) & vbCrLf & _ "Field B: " & .Fields(b) & vbCrLf & _ "Field C: " & .Fields(c) DoCmd.SendObject acSendNoObject, , , _ sToName, , , sSubject, sMessageBody, False, False End If .MoveNext LoopEnd With Set MyDb = NothingSet rsEmail = Nothing
You can test this on a button, but here is what goes down ...
First, look at your query and see how your columns are defined. Note, the order of your fields, for instance if the e-mail address is in the first column, that column index is 0 (the query columns go from 0 to n).
Note a recordset uses the term 'fields' for columns so assign the correct field/column numbers in the above code:
qryQueryName = the name of the query you want to send e-mails from
x = # of the field with e-mail address
y = # of field with invoice number
a,b,c = # of fields if you want the e-mail body to have more information from the query (if not/more, you can delete/add as appropriate)
With (y), I put the invoice number in the subject line - you can move it whereever, just wanted to give you a good enough example to work off of.
Also, I had it check field(x) (the email field) to see if there was an e-mail there, if not, it ignores that record.
Hope that helps,
Last edited by dkinley; 11-13-2008 at 10:21 AM.