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 Andrzejek 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

Status
Not open for further replies.

MikkiJ

Programmer
Oct 24, 2024
1
Good Day~
How would I send a calendar in an email body with a command button? I have a table with all my Outlook appointments need to send a calendar with appointment on each work day by month in an email with a button click.
Thanks for any help!
 
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...

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 Sub

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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top