Excel to Shared Outlook Calendar

Status
Not open for further replies.

Alex Dickey

Member
Outlook version
Outlook 2010 64 bit
Email Account
I created a pop up tool using an excel user form. Employees will enter days off and the information pushes to an outlook calendar. I am unable to push it to a shared calendar I have created on the exchange server.

\\Ops.Svc.Shared.Calendar@cfins.com- The file path for the shared cal.

Code below:

Private Sub ComboBox1_Change()
End Sub

Private Sub ComboBox2_Change()

End Sub

Private Sub DTPicker1_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date)

End Sub

Private Sub Label3_Click()

End Sub

Private Sub TextBox2_Change()

End Sub

Sub UserForm_Initialize()
ComboBox1.List = Array("P.T.O.", "Buisness Travel", "Half Day", "Maternity Leave", "Paternity Leave", "Family Leave", "Other")
End Sub

'Calendar end date
Private Sub btnEndDateCal_Click()
Cal.lblCtrlName = "tbEndDate"
Cal.lblUF = "UserForm2"
Cal.Show
End Sub
Private Sub tbEndDate_Change()

End Sub

'Calender start date
Private Sub btnStDateCal_Click()
Cal.lblCtrlName = "tbStDate"
Cal.lblUF = "UserForm2"
Cal.Show
End Sub
Private Sub tbStDate_Change()

End Sub

Private Sub CommandButton2_Click()


Call security
Call AddAppointments
Sheets("sheet1").Range("J1").Value = DTPicker1
End Sub

Sub AddAppointments()

'Finds User Name
Const lpnLength As Integer = 255
Dim status As Integer
Dim lpName, lpUserName As String
lpUserName = Space$(lpnLength + 1)
status = WNetGetUser(lpName, lpUserName, lpnLength)
If status = NoError Then
lpUserName = Left$(lpUserName, InStr(lpUserName, Chr(0)) - 1)
Else
MsgBox "Unable to get the name."
End
End If


' Create the Outlook session
Set Myoutlook = CreateObject("Outlook.Application")



' Start at row 1
r = 1

Do Until Trim(Cells(r, 1).Value) = ""
' Create the AppointmentItem
Set myApt = Myoutlook.CreateItem(1)




' Set the appointment properties
myApt.Subject = lpUserName & "-" & Hour(Now) & ":" & Minute(Now) & "-" & DateValue(Now) & "-" & UserForm2.ComboBox1.Value & "-" & UserForm2.TextBox2.Value
myApt.Start = UserForm2.tbStDate.Value
myApt.End = UserForm2.tbEndDate.Value
'myApt.TimeValue(Item.Start) = UserForm.DTPicker1

' If Busy Status is not specified, default to 2 (Busy)
If Trim(Cells(r, 5).Value) = "" Then
myApt.BusyStatus = 2
Else
myApt.BusyStatus = Cells(r, 5).Value
End If
If Cells(r, 6).Value > 0 Then
myApt.ReminderSet = True
myApt.ReminderMinutesBeforeStart = Cells(r, 6).Value
Else
myApt.ReminderSet = False
End If
myApt.Body = Cells(r, 7).Value
myApt.Save
r = r + 1
Loop
End Sub

Private Sub Application_Startup()
Dim NS As Outlook.Namespace
Set NS = Application.GetNamespace("\\Ops.Svc.Shared.Calendar@cfins.com")
Set curCal = NS.GetSharedFolder("\\Ops.Svc.Shared.Calendar@cfins.com").Items
Set NS = Nothing
End Sub

Private Sub curCal_ItemAdd(ByVal Item As Object)
Dim cAppt As AppointmentItem
Dim moveCal As AppointmentItem
' On Error Resume Next
Set newCalFolder = oFolder.Folders("\\Ops.Svc.Shared.Calendar@cfins.com")

If Item.BusyStatus = olBusy Then

Set cAppt = Application.CreateItem(olAppointmentItem)

With cAppt
.Subject = "Copied: " & Item.Subject
.Start = Item.Start
.End = Item.End
.Location = Item.Location
.Body = Item.Body
End With
 

Forum Admin

Senior Member
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
C Pull Outlook shared calendars items from Excel Outlook VBA and Custom Forms 4
E Accessing shared outlook folder doesn't work since switch to new outlook/excel Outlook VBA and Custom Forms 11
M outlook shared calendar data export to excel sheet Using Outlook 1
S Excel VBA and shared calendar issue Outlook VBA and Custom Forms 4
M Linking Excel Cell to Shared Contacts Outlook VBA and Custom Forms 7
R Automating from excel into shared Calendars Using Outlook 1
N Extract Outlook emails to excel Outlook VBA and Custom Forms 2
M Extract all links from Outlook email, send to Excel Using Outlook 2
O Email not leaving Outbox when using Excel VBA to sync Outlook account Outlook VBA and Custom Forms 4
S How to export urls from email to excel and check the status of the url ? New Slipstick.com Articles 6
S save email from excel Outlook VBA and Custom Forms 1
B Outlook Business Contact Manager with SQL to Excel, User Defined Fields in BCM don't sync in SQL. Can I use VBA code to copy 1 field to another? BCM (Business Contact Manager) 0
C Trying to populate an appointment ComboBox from Excel Outlook VBA and Custom Forms 2
M Auto-export mail to Excel Outlook VBA and Custom Forms 2
E Copying the whole e-mail body into excel Outlook VBA and Custom Forms 0
E Copy e-mail body from outlook and insert into excel Outlook VBA and Custom Forms 3
E Copying data from e-mail attachement to EXCEL file via macro Outlook VBA and Custom Forms 38
S Find a cell value in excel using outlook vba Using Outlook 1
K Update Appointment category when changed in Excel Using Outlook 3
A Script to fetch data from mails in restricted collection and sending them to excel Using Outlook 1
D Paste Excel table into Outlook message Outlook VBA and Custom Forms 7
J Outlook 2013 Extract Flag Completed dates to Excel Macro Outlook VBA and Custom Forms 16
D Retrieving Tables from outlook to excel Using Outlook 14
J Copy or Export Outlook Mail to Excel Outlook VBA and Custom Forms 6
nathandavies Email Details to Excel & Save as .MSG on one macro - combination of 2 macros Outlook VBA and Custom Forms 3
N Export details to a excel spreadsheet using macros Using Outlook 0
P How to copy and append data from Outlook 2016 message into Excel 2016 workbook Using Outlook 0
T Outlook Calendar 2016 import Excel Using Outlook 1
D Macro sending outlook template from Excel list Outlook VBA and Custom Forms 6
N Lookup Value From Excel and Reply With Matching Value Using Outlook 0
D send email from Excel using outlook template Outlook VBA and Custom Forms 3
Diane Poremsky Importing Lists from Excel to Outlook New Slipstick.com Articles 0
N Paste content to Excel when .txt file (attachment) is opened Outlook VBA and Custom Forms 1
R Macro to copy email to excel - Runtime Error 91 Object Variable Not Set Outlook VBA and Custom Forms 11
M Creating Outlook Appointments from Excel Cells Outlook VBA and Custom Forms 1
H Macro to Copy Specific content from Mail Body and Paste to Excel Outlook VBA and Custom Forms 4
R Exporting Excel data to Outlook 2016 Calendar Outlook VBA and Custom Forms 20
K Macro to search emails between two time slots on public folders with excel export Outlook VBA and Custom Forms 12
Diane Poremsky Use a macro to copy data in Outlook email to Excel workbook New Slipstick.com Articles 0
A How to export one email data to excel Using Outlook 1
W macro to export outlook emails to excel Outlook VBA and Custom Forms 6
B Importing Text from Excel to Outlook 2013 Calender Using Outlook 0
A Populate Excel from Outlook Userform Outlook VBA and Custom Forms 3
B Import Excel Text into Outlook Calender Using Outlook 4
J Outlook Macro to Update Sharepoint Excel File Using Outlook 1
K Extract email to excel from a specific sender Outlook VBA and Custom Forms 3
C Copy email to excel runtime error 5020 Using Outlook 5
O VBA to extract email (fields and body) to Excel Outlook VBA and Custom Forms 14
P Selecting specific calendar from Excel Outlook VBA and Custom Forms 5
D calendar to excel spreadsheet Using Outlook 1

Similar threads

Top