Body text of Email from invite date/time

[Someguy]

Hello,

I send numerous meeting invites to clients everyday, and they are all a generic statement, but they each have the date and time of the meeting in the body of the email text. I wanted to know if there is a way for when I send a calendar invite from outlook to have it auto fill the body of the email and pull the date and time into the body of the email from the invite date/time drop down menu. I image this will require a macro or something, but given the frequency I need to do this everyday, saving repetitive data entry will be a huge help.

thank you,
james

 

[Diane Poremsky]

A macro could do it. Basically, after you set the time - you would click a button to read the fields and create a text string to add to the body.

 

[Someguy]

Alright, I’ve never made a custom macro before. How would I get it to read those fields?

 

[Diane Poremsky]

I will see if i have something close to it or will put something together. (It might make a nice article. )

 

[Diane Poremsky]

This is the basics - fill out the appointment fields then run the macro. This only does the date, but you can add any of the appointment fields.

How to use Outlook's VBA Editor

Basic instructions on using Outlook's VBA editor: how to open it, where to put the code, how to sign your code.

www.slipstick.com

Sub CopyMeetingInfo()
    Dim objApp As Outlook.Application
    Dim objAppt As Outlook.AppointmentItem
    
    Set objApp = Application
    Set objAppt = objApp.ActiveInspector.CurrentItem

  If Not objAppt Is Nothing Then
        If objAppt.Class = olAppointment Then
           objAppt.Body = "Start date and time: " & objAppt.Start & vbCrLf & "End date and time: " & objAppt.End & crlf & objAppt.Body
           
            
        End If
    End If
    
    Set objAppt = Nothing
End Sub

 

[Someguy]

Okay, so that got me started, but I ended up going a different route after a few hours of work. I ended up making a UserForm that I call from a button on the ribbon, which makes the invite and then displays it. I'm sure there are more elegant ways of doing what I did, but I am rather happy with how it turned out. I have a Userform that has fields to enter the case number, client name, and client email, drop downs for time and time zone and date picker. Here is my code, I'd love some feed back on it.


Option Explicit
'Private oVars As Variables


Private Sub Cancel_Click()
Unload Me
End Sub

Private Sub Clear_Click()

UserForm1.CaseNum = ""
UserForm1.ClientName = ""
UserForm1.ClientEmail = ""

End Sub

Private Sub Generate_Click()
Dim OutApp As Object
Dim OutMail As Object
'Dim dTheDate As Date
Dim tzstart As Outlook.TimeZone
Dim tzabv As String



'dTheDate = DateAdd("d", 28, Me.TextBox5.Value)

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(1)
Select Case UserForm1.TimeZone
Case "Eastern Standard Time"
Set tzstart = Application.TimeZones.Item("Eastern Standard Time")
tzabv = "EST"
Case "Central Standard Time"
Set tzstart = Application.TimeZones.Item("Central Standard Time")
tzabv = "CST"
Case "Mountain Standard Time"
Set tzstart = Application.TimeZones.Item("Mountain Standard Time")
tzabv = "MST"
Case "Pacific Standard Time"
Set tzstart = Application.TimeZones.Item("Pacific Standard Time")
tzabv = "PST"
Case "Alaska Standard Time"
Set tzstart = Application.TimeZones.Item("Alaska Standard Time")
tzabv = "AKST"
Case "Hawaii Standard Time"
Set tzstart = Application.TimeZones.Item("Hawaii Standard Time")
tzabv = "HST"
End Select

'With OutMail
OutMail.MeetingStatus = olMeeting
OutMail.RequiredAttendees = UserForm1.ClientEmail.Value
OutMail.Subject = "Company Webex - Case #" + UserForm1.CaseNum.Value
OutMail.Body = "Hello " & UserForm1.ClientName & "," & vbCrLf & vbCrLf & "I have scheduled your Company WebEx Session for " & UserForm1.MtgDate & " at " & UserForm1.Time & " " & tzabv & "." & vbCrLf & vbCrLf & "Please utilize the below link to access" & vbCrLf & vbCrLf & "WebEx: link-redacted" & vbCrLf & vbCrLf & "Your session number will be provided at the time of the meeting." & vbCrLf & vbCrLf & "Thank you," & vbCrLf & "James"
OutMail.Start = UserForm1.Time & UserForm1.MtgDate
OutMail.Duration = 60
OutMail.ReminderSet = True
OutMail.ReminderMinutesBeforeStart = 15
OutMail.Location = "Company Webex"
OutMail.StartTimeZone = tzstart
'End With

Unload Me
OutMail.Display
'.Close SaveChanges:=False
Set OutMail = Nothing
Set OutApp = Nothing
End Sub


Private Sub UserForm_Initialize()





Caption = "Company Webex Invitation"
Label2.Caption = "Case Number"
Label3.Caption = "Client Name"
Label4.Caption = "Meeting Date"
Label5.Caption = "Time"
Label6.Caption = "Time Zone"
Generate.Caption = "Generate Invite"


'Fill Date Drop Down box
With Time
.AddItem "8:00 AM"
.AddItem "8:30 AM"
.AddItem "9:00 AM"
.AddItem "9:30 AM"
.AddItem "10:00 AM"
.AddItem "10:30 AM"
.AddItem "11:00 AM"
.AddItem "11:30 AM"
.AddItem "12:00 PM"
.AddItem "12:30 PM"
.AddItem "1:00 PM"
.AddItem "1:30 PM"
.AddItem "2:00 PM"
.AddItem "2:30 PM"
.AddItem "3:00 PM"
.AddItem "3:30 PM"
.AddItem "4:00 PM"
.AddItem "4:30 PM"
.AddItem "5:00 PM"
.AddItem "5:30 PM"
.AddItem "6:00 PM"
.AddItem "6:30 PM"
.AddItem "7:00 PM"
.AddItem "7:30 PM"
.AddItem "8:00 PM"
.AddItem "8:30 PM"
.AddItem "9:00 PM"
End With

With TimeZone
.AddItem "Eastern Standard Time"
.AddItem "Central Standard Time"
.AddItem "Mountain Standard Time"
.AddItem "Pacific Standard Time"
.AddItem "Alaska Standard Time"
.AddItem "Hawaii Standard Time"
End With




End Sub

 

[Diane Poremsky]

It looks good - i did not test it, but nothing bad jumps out at me in reading through it.

 

[Someguy]

Well that’s relieving to hear. I know I need to delete a few commented out lines, and clean up a bit, but i’m glad I didn’t make any major mistakes. When I tested it, it works exactly like I wanted it to.