Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-11-2018, 05:26 AM   #1
jysharp2003
Newly Registered User
 
Join Date: Feb 2018
Posts: 5
Thanks: 2
Thanked 0 Times in 0 Posts
jysharp2003 is on a distinguished road
VBA to Send Email from Form

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.
Attached Images
File Type: jpg dinkHelp.jpg (101.7 KB, 24 views)

jysharp2003 is offline   Reply With Quote
Old 03-11-2018, 05:48 AM   #2
jysharp2003
Newly Registered User
 
Join Date: Feb 2018
Posts: 5
Thanks: 2
Thanked 0 Times in 0 Posts
jysharp2003 is on a distinguished road
Re: VBA to Send Email from Form

Code from DKinley is found with search on ID 775884. He must be retired.
jysharp2003 is offline   Reply With Quote
Old 03-11-2018, 06:59 AM   #3
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,046
Thanks: 259
Thanked 307 Times in 292 Posts
Gasman will become famous soon enough Gasman will become famous soon enough
Re: VBA to Send Email from Form

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.

__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
The Following User Says Thank You to Gasman For This Useful Post:
jysharp2003 (03-11-2018)
Old 03-11-2018, 11:52 AM   #4
jysharp2003
Newly Registered User
 
Join Date: Feb 2018
Posts: 5
Thanks: 2
Thanked 0 Times in 0 Posts
jysharp2003 is on a distinguished road
Re: VBA to Send Email from Form

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(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:
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 (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,
-dK
Last edited by dkinley; 11-13-2008 at 10:21 AM.
jysharp2003 is offline   Reply With Quote
Old 03-11-2018, 12:05 PM   #5
ridders
Newbee moderator
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 5,530
Thanks: 79
Thanked 1,368 Times in 1,277 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: VBA to Send Email from Form

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
Code:
Do Until .EOF
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


-------------------------
nil illegitimi carborundum est
ridders is offline   Reply With Quote
The Following User Says Thank You to ridders For This Useful Post:
jysharp2003 (03-11-2018)
Old 03-11-2018, 12:27 PM   #6
jysharp2003
Newly Registered User
 
Join Date: Feb 2018
Posts: 5
Thanks: 2
Thanked 0 Times in 0 Posts
jysharp2003 is on a distinguished road
Re: VBA to Send Email from Form

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.
jysharp2003 is offline   Reply With Quote
Old 03-11-2018, 09:11 PM   #7
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 1,769
Thanks: 2
Thanked 379 Times in 375 Posts
Cronk will become famous soon enough
Re: VBA to Send Email from Form

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
Code:
sToName = frm_rptCommMainNotesUNION.UEMP_EMAIL1(0)
Maybe it should be
Code:
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.


Cronk is offline   Reply With Quote
Reply

Tags
email , object , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Form to send email (email blast) hooby1 Forms 22 01-10-2017 03:25 PM
Send report to email & insert email address off current form MichaelWaimauku Forms 17 12-11-2012 06:32 PM
Email Button to send email in a form nickblitz Forms 2 11-02-2012 09:31 AM
Simple Email Code: Send an email while looking at a Form travismp Modules & VBA 3 04-08-2005 11:29 AM
send email from form rlambkin Macros 1 07-13-2003 05:31 AM




All times are GMT -8. The time now is 12:19 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World