create Outlook invitation and add body an users to the email

M

megatronixs

Registered User.
Local time
Today, 16:04
Joined
Aug 17, 2012
Messages
719
Hi all,

I wanted to create a automation to send invitation out for users on a list for the training.
It seems hard to implement.
I could get only 2 users on a list to the email and the body of the email was empty.
after many attempts, it is still giving me nothing that works.
The idea is that it will loop trough a table where all the users are, add them to the attendees list (this does not work, only the recipients), then create the body and add all the data into the invitation and display it before sending.
please find below the code that is more mixed up than anything else.

Code: 
Private Sub btn_send_invitation_Click()
    Dim objItem                 As Object
    Dim db                      As DAO.Database
    Dim MailList                As DAO.Recordset
    Dim outMail                 As Outlook.AppointmentItem
    Dim outMail2                   As Outlook.MailItem
    Dim oApp                    As Object
    
    Set outMail = objItem
    Set oApp = CreateObject("Outlook.application")
    Set outMail2 = oApp.CreateItem(olMailItem)
    Set db = CurrentDb()
    Set MailList = db.OpenRecordset("My_Email_Addresses")
    Set outMail = Outlook.CreateItem(olAppointmentItem)
Do Until MailList.EOF
        ' This creates the e-mail
        ' We need to move it BEFORE we start the loop, since
        ' we don't want to make a bunch of e-mails, we just want one.
        ' this is where we loop through our list of addresses,
        ' and we add them to the RECIPIENTS collection
            ' This adds the address to the list of recipients
        'And on to the next one...
        MailList.MoveNext
        
 Loop
        
Do Until MailList.EOF
'//-------------------------------------------------------------------------------------------
outMail.Recipients.Add MailList("UserEmail")
Loop
outMail.Subject = Me.training_name
outMail.location = Me.training_location_text
outMail.MeetingStatus = olMeeting
outMail.start = Me.start_date_text & " " & Me.training_time_start
outMail.End = Me.end_date_text & " " & Me.training_end_time

outMail2.HTMLBody = "<HTML><HEAD><Font Size= 4><style> table, th, td </style> </HEAD> <BODY><br><p>" & _
                "*** This is an automatically generated email, please do not reply ***<br><br>" & _
                "Please note that: " & MailList("Forename") & " " & MailList("Surname") & "<br><br>" & _
                "Date: " & Me.start_date_text & "<br>" & _
                "Locatoin: " & Me.training_location_text & " <br>" & _
                "Start Time: " & Me.start_time_text & " <br>" & _
                "End Time: " & Me.end_time_text & " <br>" & _
                "this in the invitation info.<br><br>" & _
                "If you have any questions please contact us.br><br></BODY> </HTML>"
outMail.Display
'Loop
Set outMail = Nothing
    End Sub
(the text in the email is only to test)

Greetings.
 
hi all,

I managed to make the add recipients work.
What does not work now is the body of the email as it does not accept html text format in appointment or invitation.

I wanted to add some line breaks and other things, but I can't get it to go to create line break. Any idea how to do this:
Code: 
Private Sub btn_send_invitation_Click()
    Dim objItem             As Object
    Dim db                  As DAO.Database
    Dim MailList            As DAO.Recordset
    Dim oMail               As Outlook.AppointmentItem
    Dim oApp                As Object
    Dim create_event_table  As Recordset
    Dim training_name_text  As String
    Dim yesno               As String
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "MyEmailAddresses_create_table"
    DoCmd.SetWarnings True
    Set create_event_table = CurrentDb.OpenRecordset("tbl_create_event")
    Set oMail = objItem
    Set oApp = CreateObject("Outlook.application")
    Set oMail = Outlook.CreateItem(olAppointmentItem)
    Set oMail = oApp.CreateItem(olAppointmentItem)
    Set db = CurrentDb()
    Set MailList = db.OpenRecordset("My_Email_Addresses")
    If Me.lunch = -1 Then
    yesno = "yes"
    Else
    yesno = "no"
    End If
' Set up the database and query connections
' this is where we loop through our list of addresses,
' adding them to e-mails and sending them.
Do Until MailList.EOF
        ' This creates the e-mail
        ' We need to move it BEFORE we start the loop, since
        ' we don't want to make a bunch of e-mails, we just want one.
        ' this is where we loop through our list of addresses,
        ' and we add them to the RECIPIENTS collection
        Do Until MailList.EOF
            ' This adds the address to the list of recipients
            oMail.Recipients.Add MailList("UserEmail")
        'And on to the next one...
        MailList.MoveNext
Loop
            'And now that we've addressed it, we can finish composing the rest of the fields.
            'This gives it a subject, sent on behalf of
                oMail.Subject = "Training: " & Me.training_name_text
                    oMail.location = Me.training_location_text
                        oMail.MeetingStatus = olMeeting
                            oMail.start = Me.start_date_text & " " & Me.training_time_start
                                oMail.End = Me.end_date_text & " " & Me.training_end_time
                'This gives it the body
                oMail.Body = "Hi reader," & _
                "Start date: & me.start_date_text"
'This displays it!
    oMail.Display
Loop
'Cleanup after ourselves
    Set oApp = Nothing
    MailList.Close
    Set MailList = Nothing
    db.Close
    Set db = Nothing
    End Sub

greetings.
 
Hi all,

I got further with it :-)
Now I only need to format the text with colour and font and I'm ready with it.
Please see below working code so others can make use of it:

Code: 
Private Sub btn_send_invitation_Click()
    Dim objItem             As Object
    Dim db                  As DAO.Database
    Dim MailList            As DAO.Recordset
    Dim oMail               As Outlook.AppointmentItem
    Dim oApp                As Object
    Dim create_event_table  As Recordset
    Dim training_name_text  As String
    Dim yesno               As String
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "MyEmailAddresses_create_table"
    DoCmd.SetWarnings True
    Set create_event_table = CurrentDb.OpenRecordset("tbl_create_event")
    Set oMail = objItem
    Set oApp = CreateObject("Outlook.application")
    Set oMail = Outlook.CreateItem(olAppointmentItem)
    Set oMail = oApp.CreateItem(olAppointmentItem)
    Set db = CurrentDb()
    Set MailList = db.OpenRecordset("My_Email_Addresses")
    If Me.lunch = -1 Then
    yesno = "yes"
    Else
    yesno = "no"
    End If
' Set up the database and query connections
' this is where we loop through our list of addresses,
' adding them to e-mails and sending them.
Do Until MailList.EOF
        ' This creates the e-mail
        ' We need to move it BEFORE we start the loop, since
        ' we don't want to make a bunch of e-mails, we just want one.
        ' this is where we loop through our list of addresses,
        ' and we add them to the RECIPIENTS collection
        Do Until MailList.EOF
            ' This adds the address to the list of recipients
            oMail.Recipients.Add MailList("UserEmail")
        'And on to the next one...
        MailList.MoveNext
Loop
            'And now that we've addressed it, we can finish composing the rest of the fields.
            'This gives it a subject, sent on behalf of
                oMail.Subject = "Training: " & Me.training_name_text
                    oMail.location = Me.training_location_text
                        oMail.MeetingStatus = olMeeting
                            oMail.start = Me.start_date_text & " " & Me.training_time_start
                                oMail.End = Me.end_date_text & " " & Me.training_end_time
                'This gives it the body
                oMail.Body = "Dear All," & vbCrLf & "" & vbCrLf & _
                "We would like to invite you to the training:  " & Me.training_name & vbCrLf & "" & vbCrLf & _
                "Date: " & Me.training_date_start & " - " & Me.training_end_date & vbCrLf & _
                "Start Time: " & Me.training_location_text & vbCrLf & _
                "Start Time: " & Me.start_time_text & vbCrLf & _
                "End Time: " & Me.end_time_text & vbCrLf & _
                "Trainer: " & Me.trainer_1_name & vbCrLf & _
                "Training will be conducted in English" & vbCrLf & _
                "Lunch will be provided: " & yesno & vbCrLf & _
                "Dress Code: smart casual" & vbCrLf & _
                "Please be punctual, if you have any problems with attendance please contact us." & vbCrLf & "" & vbCrLf & _
                "Please make sure to press the accept and then send the response now/decline button and then send " & vbCrLf & _
                "the response now (please provide reason for decline) for this invitation  - asap." & vbCrLf & "" & vbCrLf & _
                "Best Regards,"
'This displays it!
    oMail.Display
Loop
'Cleanup after ourselves
    Set oApp = Nothing
    MailList.Close
    Set MailList = Nothing
    db.Close
    Set db = Nothing
    End Sub

If one could tell me how to format the text, that would be great.

Greetings.
 
You must log in or register to reply here.

Similar threads

D
Default Signature new outlook current user
Replies
17
Views
639
GPGeorge
GPGeorge
M
Signature Image in GMail email body
Replies
7
Views
381
Marshall Brooks
M
T
Attache to email from attachment Field
Replies
2
Views
210
theinviter
T
T
Access When emailing brings up Outlook 2016 wizard to add email account
Replies
10
Views
412
The_Doc_Man
The_Doc_Man
Jason Lee Hayes
Solved Open Outlook & check for new mail where search criteria is applied to the email subject line
Replies
19
Views
611
Jason Lee Hayes
Jason Lee Hayes

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom