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!
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!
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)
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?
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: ua-cam.com/video/7HckYjH_wg4/v-deo.html
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?
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.
@@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"
@@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
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
Very helpful indeed! Many thanks!
Glad to hear that!
hello sir how can l create a database that remind me patient review due date automatically in ms acess
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!
Thanks for the help!
No problem!
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!
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)
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?
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: ua-cam.com/video/7HckYjH_wg4/v-deo.html
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.
Interesting.. can you post your code to see?
Thank you, but I can’t figure the prblem I follow the code , compiler said sent
But actually nothing sent or received from email
Interesting.. can you post your code?
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?
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.
@@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"
@@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
Could you do an example using Gmail?
Great idea - I'll check it out and add it to my list!
Can u share with us the file or the code plz
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
@@seanmackenziedataengineering thanks a lot
@@seanmackenziedataengineering This just gives me a run on error in the " Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)" part, can you help me!
dear sir, i like your video it so nice useful. do you share this code? thank you.
Thanks for asking! I'm thinking about the best way to post it. Stay tuned!