Tips on how to open and populate an Outlook appointment from inside Excel utilizing VBA
It’s simple to import information from Excel into an Outlook merchandise, equivalent to a message or appointment, as a result of the performance is built-in, and a wizard walks you thru all the course of. If you wish to accomplish the identical factor from inside Excel—as an illustration, show and populate an appointment whereas working in Excel—you’ll want a little bit of VBA magic. On this article, I’ll present the code for pre-populating an Outlook appointment type utilizing Excel information, from inside Excel.
SEE: 83 Excel ideas each person ought to grasp (TechRepublic)
I’m utilizing Microsoft 365 on a Home windows 10 64-bit system. (I like to recommend that you simply wait to improve to Home windows 11 till all of the kinks are labored out.) Downloading the .xlsm, .xls, and .cls information will prevent numerous work. I bumped into no issues working the process within the .xls format, however the code was written particularly for 365, so I can’t assure that you simply received’t run into one thing I didn’t account for. The Outlook internet functions don’t assist VBA.
This text assumes a little bit of Excel data, however even in case you’re a newbie, you must be capable of full the directions to success. This text is lengthy, nevertheless it’s largely clarification, so that you received’t be working as arduous as you would possibly assume, particularly in case you obtain the demonstration information.
The VBA occasion process
We’ll be utilizing the BeforeDoubleClick occasion process to set off the code in Itemizing A to show Outlook’s default appointment type. As well as, the code will populate a number of fields. At that time, you may proceed so as to add info or save and shut the shape. This process populates only some of the shape’s fields, however you may simply accommodate different fields, and I’ll present you ways to take action as we talk about the code.
Itemizing A
Personal Sub Worksheet_BeforeDoubleClick(ByVal Goal As Vary, Cancel As Boolean)
'Show Outlook appointment type and fill in default fields.
Possibility Specific
Dim ol As Outlook.Software
Dim olApptItem As Outlook.AppointmentItem
Dim wb As Workbook
Dim ws As Worksheet
Dim r As Lengthy
Dim c As Lengthy
Set ol = New Outlook.Software
Set olApptItem = ol.CreateItem(olAppointmentItem)
Set wb = ThisWorkbook
'Replace to work with a particular sheet.
Set ws = wb.Sheets("Sheet1")
r = Goal.Row
c = Goal.Column
On Error GoTo errHandler:
'If double-click is not in column C of Table1, Exit Sub.
If c <> 3 Then Exit Sub 'Column examine.
If r <= 2 Then Exit Sub 'Row examine.
If ws.ListObjects("Table1").DataBodyRange.Rows.Rely > r + 2 Then Exit Sub 'Decrease rows examine.
With olApptItem
'Begin and Finish embody each date and time.
.Topic = ws.Cells(r, c - 1).Worth
.begin = ws.Cells(r, c).Worth & " " & ws.Cells(r, c + 1).Worth
.Finish = ws.Cells(r, c).Worth & " " & ws.Cells(r, c + 2).Worth
.Show
Finish With
Set ol = Nothing
Set olApptItem = Nothing
Set wb = Nothing
Set ws = Nothing
Exit Sub
errHandler:
MsgBox "Error " & Err.Quantity & " " & Err.Description
Finish Sub
If you happen to’re not very acquainted with VBA, Itemizing A in all probability appears a bit scary, however most of it’s declaring and defining variables. The code that truly populates and shows the shape begins on the With block, and is brief, easy and might be simply modified to suit your wants.
Earlier than we get entrenched within the code, let’s enter it and set a reference to the Outlook object library.
Coming into the code
We’re working from inside an Excel workbook, so the very first thing we want is an Excel file with the suitable values for filling an Outlook appointment. Determine A has a number of data; it’s easy on goal.
Determine A
In case you are utilizing a ribbon model, make sure you save the workbook as a macro-enabled file. If you happen to’re working within the menu model, you may skip this step.
To enter the process, press Alt + F11 to open the Visible Primary Editor (VBE). Within the Mission Explorer to the left, choose the sheet that incorporates the appointment information, in our case that’s Sheet1. Enter the code manually or import the downloadable .cls file. As well as, the process is within the downloadable .xlsm, .xls and .cls information. If you happen to enter the code manually, don’t paste from this internet web page. As a substitute, copy the code right into a textual content editor after which paste that code into the sheet module. Doing so will take away any phantom internet characters that may in any other case trigger errors.
Earlier than you may run the code, you will need to reference the Outlook object library as a result of a number of the declarations reference Outlook objects. To take action, click on the Instruments menu and select References (Determine B). Within the ensuing dialog, thumb right down to the Microsoft Outlook Object Library (mine is 16, however you could be working one other model; if there are two, examine the most recent library).
Determine B
To run the code, return to the sheet and double-click any of the 4 date values in column C—C3:C6. Doing so will open the Outlook appointment type and fill within the topic, begin and finish controls utilizing the corresponding values for the date you double-clicked. As an illustration, Determine C reveals the shape after double-clicking C3. You possibly can enter extra information, shut and save the appointment as is, or cancel. For now, do the latter.
Determine C
Attempt all 4 date cells, cancelling every as an alternative of saving something for now. Did you discover an issue with C4? It returns an error message. Click on OK to dismiss the error after which take a look on the information and see in case you can determine why earlier than I clarify.
If you happen to guessed it’s a date drawback, you proper. The beginning time is 10 a.m. The worth wants the minute part—10:00AM. Repair that and check out once more. Ooops … it nonetheless doesn’t work, as you may see in Determine D. As is, the occasion runs from 10 a.m. till 12 a.m., on the identical date, which might’t occur. The Sort Mismatch within the error message is the most effective clue. Change 12:00AM to 12:00PM and check out once more. Each time values are flawed, so you will need to appropriate each earlier than this report will work. I’m not making an attempt to complicate issues, however I need you to see the easy error-handling at work.
Determine D
Now that you simply’ve seen the process at work, you’re in all probability questioning how it really works. That’s what we’ll deal with subsequent.
The VBA process defined
Reviewing the process, you may see that the primary a number of strains declare and outline objects and variables. This half is easy. The primary assertion you would possibly want to switch when making use of the process to your individual work is
Set ws = wb.Sheets("Sheet1")
I’ve specified Sheet1 so this process received’t work on every other sheet. You might reference one other sheet after all and even reference ActiveWorksheet
Set ws = wb.ActiveWorksheet
I like to recommend that you simply make this transformation solely if you wish to run this process on different sheets, which is unlikely. If you happen to use this assertion, you’re opening up a can of worms as a result of the double-click will work within the energetic sheet, not solely Sheet 1.
The On Error assertion handles any run-time errors; you noticed it at work in Determine D. This process is easy sufficient that you simply shouldn’t want rather more error-handling however make sure you check it totally earlier than making that call.
The subsequent statements examine the double-clicked cell
'If double-click is not in column C of Table1, Exit Sub.
If c <> 3 Then Exit Sub 'Column examine.
If r <= 2 Then Exit Sub 'Row examine.
If ws.ListObjects(“Table1”).DataBodyRange.Rows.Rely > r + 2 Then Exit Sub ‘Decrease rows examine.
If the double-clicked cell isn’t in column C, the process stops. If the double-clicked cell is in row 1 or the header row, the process stops. The final examine handles all rows under the final row within the Desk object. When making use of this process to your individual information, you will need to modify these three statements to accommodate your Desk object. As an illustration, in case your dates are in column D, use the part If c <> 4. In case your header row is in row 1, use the part If r = 1.
The final assertion is a bit more sophisticated. First, replace the Desk’s identify. Then, maintaining in thoughts which row the header is in, replace the r + 2 part. The Rows.Rely property returns 4 as a result of there are 4 rows of information (excluding the header row from the rely). The two within the r + 2 part accounts for row 1 and the Desk’s header row. In our instance, that evaluates to six, our final row of information.
That is the one place the place you would possibly wish to improve your process a bit by including a message field that explains why the process stops. I’d discover the message a bit annoying, however in case you’re distributing this to others, they could discover it useful to know why the process isn’t working, although I believe it’s relatively apparent.
The With block does the actual work
With olApptItem
'Begin and Finish embody each date and time.
.Topic = ws.Cells(r, c - 1).Worth
.begin = ws.Cells(r, c).Worth & " " & ws.Cells(r, c + 1).Worth
.Finish = ws.Cells(r, c).Worth & " " & ws.Cells(r, c + 2).Worth
.Show
Finish With
The With block references an Outlook appointment type (olAppItem). If you enter the interval character, Excel opens an inventory of properties and occasions, as proven in Determine E. You’ll add further type info this fashion. I included only some to maintain issues easy.
Determine E
The populating statements use relative addressing to search out the precise worth. As an illustration, the topic textual content is in the identical row (r) because the double-clicked cell. Within the defining code, I set the 2 Lengthy variables r and c to Goal’s row and column
r = Goal.Row
c = Goal.Column
Goal is the handed parameter that identifies the cell deal with. If you happen to double-click the worth in row 3, r equals 3. If you happen to double-click a worth in column C, c can be 3. By subtracting 1 from c, the code references the cell to the left of the double-clicked cell. On this case, that’s the Process column (column B). As a result of we’ve set r and c forward of time, you might want to change solely the precise values to determine the relative place of the populating information.
The Begin and Finish settings are a bit extra complicated as a result of they accommodate the date and time. That’s why these two statements concatenate the values from two cells. Particularly, Begin is the beginning date and begin time. Equally, Finish is the tip date and finish time. Discover that the beginning and finish date use the identical reference as a result of the occasion doesn’t prolong past in the future. When referencing the time, you’ll need to replace the values (1 and a pair of) accordingly to determine the cells containing these values.
The Show occasion shows the shape, which is pre-populated with the suitable values from the Desk.
The previous couple of statements destroy the objects and exit the sub. The next error-handler shows an error message (Determine C) solely when referred to as. You would possibly wish to add extra particular error messages, however this easy code could be sufficient.
At first, the process appears a bit daunting, however as I’ve proven, it’s easier than you would possibly understand with only a look. Most of it’s declaring and defining objects and variables. The demonstration information ought to run high-quality for you, apart from any unaccounted error(s) within the .xls format. When making use of the process to your individual work, you’ll must replace a number of statements, however I’ve recognized these spots for you.
