VBA to Send Email from Form

[jysharp2003]

Hi,
I am trying to use some code from DKin (new member and not at post count to insert link at this time but can email it to you) but coming up with error. Checked all required fields but code results in object required. Curious if this code works with a UNION query. Any help would be appreciated.
Thanks.

 

[jysharp2003]

Code from DKinley is found with search on ID 775884. He must be retired.

 

[Gasman]

Showing all the code and the error line would help?

There are plenty of examples of email from a form on this site.
I've posted my code once or twice in the past.

 

[jysharp2003]

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)

With rsEmail
.MoveFirst
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
End If
.MoveNext
Loop
End With

Set MyDb = Nothing
Set rsEmail = Nothing
End Sub

Original code from DK with adjustment instructions:

Access Hack by Choice

Join Date: Jul 2008
Location: MidSouth, USA
Posts: 2,016
Thanks: 0
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.


Code:
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 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:
Quote:
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 , 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,
-dK
Last edited by dkinley; 11-13-2008 at 10:21 AM.

 

[isladogs]

Your code is unclear in several places

Your recordset appears to be based on a form or is it a report. Neither of those will work. Recordsets must be based on a table or a query or a SQL statement.

Why does the If IsNull line have the (0) part?
Similarly for the next 2 lines

Why does the SendObject line have as its first argument acSendNoObject?

BTW you can simplify the next line to

Do Until .EOF

 

[jysharp2003]

Gasman/Ridders, Thanks for your time. dkinley has some good code and found my issue. Thought he said replace .fields with the column name but leaving .fields and adjustment the number defining the column to link to was the fix. Email works I think since my Mail program is linked to GMAIL and laptop is not configured correctly I am not able to view the actual email.
Hey, if you guys have an easier snippet shoot it here and I will give you a double thank you!!
This app is a Schedule generator where admins have to adjust the schedule every now and then (schedule is exported to Google Sheets) but instead of emailing EVERYONE my effort is to only email the folks with the small changes and not the full group of 50.
Thanks again.

 

[Cronk]

It seems to me that
frm_rptCommMainEmail
is either a query or table, otherwise the recordset would not open.

frm_rptCommMainNotesUNION.UEMP_EMAIL1(0)
whatever it is, is supposed to contain the email address because

sToName = frm_rptCommMainNotesUNION.UEMP_EMAIL1(0)

Maybe it should be

sToName = forms!frm_rptCommMainNotesUNION.UEMP_EMAIL1(0)

Unlike the code in the code supplied by the Kinley person, where the email address was in one of the recordset fields, the OP's code has this email address will be static. So if the code ran, there would be x copies of the email sent to one recipient.