conradcliff
Board Regular
- Joined
- Feb 24, 2010
- Messages
- 58
Hey guys, so while I'm waiting for help on my other thread I thought I would try and get some info on this issue as well.
I want to create a calendar event with data located in different cells on the active sheet of a workbook. The cells holding the data will be the same every time I do this, just the data inside them will change.
I've found two different macro's for doing this but I'm not sure which one would be better suited for my needs. here's the first one:
The stuff that confuses me the most(that's not to say it all doesn't confuse me) about this bit of code is
and
Also, I need to have the body consist of values in specific cells on the active sheet as well.
I just don't know what this stuff means or how I'm supposed to change it to point to my information.
The other bit of code that I found is here:
This part makes me think it's searching for something:
which is why I don't think this one is as useful for my purposes as the other one. However, if you think it would be better I just need help figuring out how to input my cell ranges.
If anyone could give me some insight or point me in the right direction I would greatly appreciate it.. Thanks!
I want to create a calendar event with data located in different cells on the active sheet of a workbook. The cells holding the data will be the same every time I do this, just the data inside them will change.
I've found two different macro's for doing this but I'm not sure which one would be better suited for my needs. here's the first one:
Code:
Sub ExportAppointmentsToOutlook()
Dim olApp As Outlook.Application
Dim olApt As AppointmentItem
Dim blnCreated As Boolean
'Read the table with appointments:
Dim arrAppt() As Variant, i As Long
arrAppt = Range("A2", Cells(Rows.Count, "E").End(xlUp)).Value
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
blnCreated = True
Err.Clear
Else
blnCreated = False
End If
On Error GoTo 0
'Create the outlook item for the table entries:
'Rows:
' Row 1 = date
' Row 2 = starttime
' Row 3 = endtime
' Row 4 = Description
' Row 5 = Location
For i = LBound(arrAppt) To UBound(arrAppt)
Set olApt = olApp.CreateItem(olAppointmentItem)
With olApt
.Start = arrAppt(i, 1) + arrAppt(i, 2)
.End = arrAppt(i, 1) + arrAppt(i, 3)
.Subject = arrAppt(i, 4)
.Location = arrAppt(i, 5)
.Body = "Created by excel tool"
.BusyStatus = olBusy
.ReminderMinutesBeforeStart = 5
.ReminderSet = True
.Save
End With
Next i
Set olApt = Nothing
Set olApp = Nothing
End Sub
Code:
Dim arrAppt() As Variant, i As Long
arrAppt = Range("A2", Cells(Rows.Count, "E").End(xlUp)).Value
Code:
.Start = arrAppt(i, 1) + arrAppt(i, 2)
.End = arrAppt(i, 1) + arrAppt(i, 3)
.Subject = arrAppt(i, 4)
.Location = arrAppt(i, 5)
I just don't know what this stuff means or how I'm supposed to change it to point to my information.
The other bit of code that I found is here:
Code:
Sub ExportAppointmentsToOutlook()
Dim olApp As Outlook.Application
Dim olApt As AppointmentItem
Dim blnCreated As Boolean
Dim x As Variant, LastRow As Long, ws As Worksheet
'Read the table with appointments:
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
blnCreated = True
Err.Clear
Else
blnCreated = False
End If
On Error GoTo 0
With ActiveSheet
Set ws = ActiveWorkbook.Sheets(.Name)
End With
With ws.Range("A2:B" & ws.Rows.Count)
LastRow = .Find(What:="*", after:=.Cells(1, 1), LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End With
For x = 2 To LastRow
Set olApt = olApp.CreateItem(olAppointmentItem)
With olApt
.Start = Range("B" & x).Value
.End = Range("C" & x).Value
.Subject = Range("A" & x).Value
.Location = Range("E" & x).value
.BusyStatus = olBusy
.ReminderSet = False
.AllDayEvent = True
.Save
End With
Next x
Set olApt = Nothing
Set olApp = Nothing
End Sub
Code:
With ws.Range("A2:B" & ws.Rows.Count)
LastRow = .Find(What:="*", after:=.Cells(1, 1), LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End With
If anyone could give me some insight or point me in the right direction I would greatly appreciate it.. Thanks!