Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
display a calendar with appointments in body of html email with access vba
- Thread starter MikkiJ
- Start date
SoftwareRT
Vendor
From your post, I think you have this setup:
1) you have calendar details in an Access table,
2) you have a form (or will create a form) with a button, when that button is clicked, it sends an email with calendar with appointments by month.
I don't know what appointment fields you have or what you want to send, so I'll generalize the code...
Add a Command Button: In the design view of the form, add a command button (let's name it btnSendCalendar).
VBA Code to Send Email: Open the VBA editor (Alt + F11).
-- Double-click on your form to open the code window and add the following code behind the button click event:
This is what the code is doing:
Outlook Initialization: The code initializes the Outlook application and creates a new mail item.
Recordset: It retrieves appointments for the current month from the Appointments table.
Building the Email Body: It constructs an HTML table with appointment details (subject, start date, end date, location).
Sending Email: The email is displayed (you can change .Display to .Send if you want to send it directly without displaying).
Clean Up: It closes the recordset and cleans up the Outlook objects.
1) you have calendar details in an Access table,
2) you have a form (or will create a form) with a button, when that button is clicked, it sends an email with calendar with appointments by month.
I don't know what appointment fields you have or what you want to send, so I'll generalize the code...
Steps to Create the Command Button
Create a Form: Open your MS Access database and create a form where you want the button to reside.Add a Command Button: In the design view of the form, add a command button (let's name it btnSendCalendar).
VBA Code to Send Email: Open the VBA editor (Alt + F11).
-- Double-click on your form to open the code window and add the following code behind the button click event:
Code:
Private Sub btnSendCalendar_Click()
Dim olApp As Object
Dim olMail As Object
Dim rs As DAO.Recordset
Dim strBody As String
Dim strDate As String
' Initialize Outlook application
Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(0) ' 0 = olMailItem
' Open the recordset for appointments
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Appointments WHERE StartDate >= Date() AND EndDate < Date() + 30 ORDER BY StartDate")
' Build the email body
strBody = "<html><body><h2>Your Appointments for the Month</h2><table border='1'>"
strBody = strBody & "<tr><th>Subject</th><th>Start Date</th><th>End Date</th><th>Location</th></tr>"
' Loop through the appointments and add to the body
Do While Not rs.EOF
strBody = strBody & "<tr>"
strBody = strBody & "<td>" & rs!Subject & "</td>"
strBody = strBody & "<td>" & Format(rs!StartDate, "mm/dd/yyyy hh:nn AM/PM") & "</td>"
strBody = strBody & "<td>" & Format(rs!EndDate, "mm/dd/yyyy hh:nn AM/PM") & "</td>"
strBody = strBody & "<td>" & rs!Location & "</td>"
strBody = strBody & "</tr>"
rs.MoveNext
Loop
strBody = strBody & "</table></body></html>"
' Configure email properties
With olMail
.To = "recipient@example.com" ' Change to recipient's email
.Subject = "Monthly Appointments"
.HTMLBody = strBody
.Display ' Use .Send to send it directly
End With
' Clean up
rs.Close
Set rs = Nothing
Set olMail = Nothing
Set olApp = Nothing
End SubThis is what the code is doing:
Outlook Initialization: The code initializes the Outlook application and creates a new mail item.
Recordset: It retrieves appointments for the current month from the Appointments table.
Building the Email Body: It constructs an HTML table with appointment details (subject, start date, end date, location).
Sending Email: The email is displayed (you can change .Display to .Send if you want to send it directly without displaying).
Clean Up: It closes the recordset and cleans up the Outlook objects.
Similar threads
- Locked
- Question
- Locked
- Question