How to Send Email from Access Using VBA - Table Queue Example

Email is one of the most used notification tools for applications, and the Access application world is no different. We need to send alerts for all kinds of situations, from critical levels of X to polite reminders to staff that certain batch processing has been completed. In this episode, we’ll look not just at how to send email using VBA, but how to create a simple queue in a table and have an email process send emails for those records that have not yet been sent. Let’s take a look at sending emails from Access!
Related Videos:
How to Send Email from Access Using VBA - Table Queue Example
You are watching this video now!
How to Send WhatsApp Messages from Microsoft Access
• How to Send WhatsApp M...
How to Send SMS Text Messages from Microsoft Access
• How to Send SMS Text M...
Want to get access to premium content made just for you and have a chat with me? Find me on Patreon :
/ mackenziedataengineering
Demo of my BZ RDP Cloaker:
www.patreon.co...
Want to work with me 1:1? Contact me today and book your free 20 min consultation!
Contact form you can find at www.mackenziema...
Follow me on social media:
/ mackenziedataanalytics
/ seamacke
/ seamacke
/ seamacke
/ psmackenzie
Get Microsoft Office including Access:
click.linksyne...
Got a TH-cam Channel? I use TubeBuddy, it is awesome. Give it a try:
www.tubebuddy....
#MSAccessVBA #VBA #SendEmail
• How to Send Email from...

[@kyopan23]

Are you sending unencrypted emails with port 25 (2525 in your case) ?
Is there a way to get this working with an encrypted smtp port sich as 465 or 587?
Amazing video, you've gained a subscriber! Keep the great work!

[@seanmackenziedataengineering]

Awesome, welcome aboard! Yes you can use other ports by switching those during setup, and you can use more secure authentication (though the email will still be unencrypted)

[@robinsackmann2828]

Very helpful indeed! Many thanks!

[@seanmackenziedataengineering]

Glad to hear that!

[@panashechakaodza3418]

hello sir how can l create a database that remind me patient review due date automatically in ms acess

[@seanmackenziedataengineering]

You could start with a table or query that has your patients, their next review date, and a completed date. Create a query that looks for review dates before today and has a null for completed date. This list will always have the patients that are due but have not been completed yet. Good luck!

[@DescomplicaAssim]

Thanks for the help!

[@seanmackenziedataengineering]

No problem!

[@HankMegens]

I have a query that displays all the shifts from an employee for next week. I like to send him/her that report. But as far as I am now, It will sent an email to an employee for every record in the query. So 2 shifts two mails with report, 5 shifts 5 mails. How Do I correct this?

[@seanmackenziedataengineering]

Assemble a string for that employee from the query output and then put the string in the message of the report. Loop through the records to build the string, taking the values on each row as you build the list. Here's how you can loop through the records: th-cam.com/video/7HckYjH_wg4/w-d-xo.html

[@ibrahimalrabiah1179]

Thank you, but I can’t figure the prblem I follow the code , compiler said sent
But actually nothing sent or received from email

[@seanmackenziedataengineering]

Interesting.. can you post your code?

[@shNMIB]

Hi sir, how you doing? I was able to run the code, but the email info is kinda duplicating for just one receiver. can please help me.

[@seanmackenziedataengineering]

Interesting.. can you post your code to see?

[@danviala8816]

Hi Sean, this worked perfectly until recently when I switched to Microsoft Exchange. I've since had a Transport Error Code 0x800040217, which relates to SMTP authentication. No amount of research has helped, and there's a lot of contradictory online info. Is this an issue you've come across and can you help resolve?

[@seanmackenziedataengineering]

Interesting. Some people may get this error for a wrong username and password. Maybe you need to put the full username in or a slightly modified username. I've seen cases where you need to put in username+domain.com instead of @ etc, OR, just the first part of the username without @domain.com etc.

[@danviala8816]

@@seanmackenziedataengineering Thank you Sean. It may be a problem with the way I'm using sendmail. I found this on Microsoft " Microsoft 365 or Office 365 does not allow you to send bulk messages via SMTP client submission"

[@kyopan23]

​@@danviala8816were you able to get this working with Exchange? I believe I encountered something similar a while ago. No much information was available but from what I understood had to do with TLS and all that

[@jungjungdelosreyes4147]

dear sir, i like your video it so nice useful. do you share this code? thank you.

[@seanmackenziedataengineering]

Thanks for asking! I'm thinking about the best way to post it. Stay tuned!

[@bumpersmith]

Could you do an example using Gmail?

[@seanmackenziedataengineering]

Great idea - I'll check it out and add it to my list!

[@mustaphati]

Can u share with us the file or the code plz

[@seanmackenziedataengineering]

Sure:
Sub SendMailFromTable()
'Send mail from a table
Dim db As Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strFrom As String
Dim strTo As String
Dim strCC As String
Dim strSubject As String
Dim strBody As String
Dim lngCounter As Long
Set db = CurrentDb
lngCounter = 0
strSQL = "Select * From EMAIL_TO_SEND Where SentDate Is Null;" 'do not re-send them every time!
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset) ', dbSeeChanges if on SQL Server linked table
Do Until rst.EOF
strFrom = Nz(rst!EmailFrom, "")
strTo = Nz(rst!EmailTo, "")
strCC = Nz(rst!EmailCC, "")
strSubject = Nz(rst!EmailSubject, "")
strBody = Nz(rst!EmailBody, "")
lngCounter = lngCounter + 1
SendMail strFrom, strTo, strCC, strSubject, strBody
rst.Edit
rst!SentDate = Now()
rst.Update
rst.MoveNext 'VERY IMPORTANT! or never ending loop occurs lol
Loop
rst.Close
Debug.Print lngCounter & " emails sent!"
Set rst = Nothing
Set db = Nothing
'add error handling if needed
End Sub
Sub SendMail(strFrom As String, strTo As String, strCC As String, strSubject As String, strBody As String)
'Send an email!
Dim objMail
Dim Url As String
Url = "schemas.microsoft.com/cdo/configuration/"
Set objMail = CreateObject("CDO.Message")
With objMail
.From = strFrom
.To = strTo
.CC = strCC
.Subject = strSubject
.HtmlBody = strBody
End With
With objMail.Configuration.Fields
.Item(Url & "sendusing") = 2 'port
.Item(Url & "smtpserver") = "smtp.myemailserver.com"
.Item(Url & "smtpserverport") = 2525
.Item(Url & "smtpauthenticate") = 1 'basic
.Item(Url & "sendusername") = strFrom
.Item(Url & "sendpassword") = EMAIL_PASSWORD 'set before, use yours here
.Update
End With
objMail.Send
Debug.Print "Sent!"
End Sub

[@mustaphati]

@@seanmackenziedataengineering thanks a lot

[@jbbiron2211]

@@seanmackenziedataengineering This just gives me a run on error in the " Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)" part, can you help me!