Excel 2010 vba to post appts to different outlook calendars at the same time

Status
Not open for further replies.

BigDawg15

Member
Outlook version
Outlook 2010 32 bit
Email Account
IMAP
Hello,

Great site. I have learned a lot from browsing through here, but obviously not enough.:)

Attached is an xl workbook that works to add appoinments to different calendars if I manually change the number in the code. What I would like to do if possible is modify the code to use the calendar name or number listed in the worksheet to add the appoinments to different calendars all in one go.

I hope that is clear enough. Thank you in advance for your assistance.

BigDawg15
 

Attachments

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Re: Excel 2010 vba to post appts to different outlook calendars at the same ti

Are the folders at the same level as the calendar or subfolders? If they are all at the same level, you can pass the folder name as a variable.

Set Appointments = olNs.GetDefaultFolder(olFolderCalendar).Folders(foldernamevariable)
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Re: Excel 2010 vba to post appts to different outlook calendars at the same ti

basic change:

dim arrCal as variant
arrCal = Range("A2").Value

Set MyFolder1 = Appointments.Folders(arrCal)

i'm not totally sure of the code needed to get the correct row. plus it dies on the appt creation.
 

BigDawg15

Member
Outlook version
Outlook 2010 32 bit
Email Account
IMAP
Re: Excel 2010 vba to post appts to different outlook calendars at the same ti

Are the folders at the same level as the calendar or subfolders? If they are all at the same level, you can pass the folder name as a variable.

Set Appointments = olNs.GetDefaultFolder(olFolderCalendar).Folders(foldernamevariable)
Diane,

Thank you for a reply. Yes they are at the same level as the calendar. When you say pass the folder name as a variable, I'm assuming I would do this for each calendar but I'm not sure how to pass each one to the row for each item?

Any example would be appreciated. Also, I have read a bit through different forums, would it be easier to assign everything to the main calendar and then move it to the designated calendar with code?

I am just starting out with Outlook and still trying to get my hands around default and non-default calendars.

Thank you for your time and assistance. You do a great job!

BigDawg15
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Re: Excel 2010 vba to post appts to different outlook calendars at the same ti

This is messy code but it works.

Code:
Option Explicit 
 
Public Sub ExportToCalendar1()
  Sheets("Sheet1").Select
   On Error GoTo Err_Execute
 
   Dim olApp As Outlook.Application
   Dim olApt As Outlook.AppointmentItem
   Dim blnCreated As Boolean
   Dim olNs As Outlook.Namespace
   Dim Folder As Outlook.MAPIFolder
   Dim Appointments As Outlook.MAPIFolder
   Dim MyFolder1 As Outlook.MAPIFolder
   Dim arrCal As String
 
   Dim range() As Variant, i As Long
 
  ' range = Range("A2", Cells(Rows.Count, "K").End(xlUp)).Value
   On Error Resume Next
   Set olApp = Outlook.Application
 
   If olApp Is Nothing Then
       Set olApp = Outlook.Application
        blnCreated = True
       Err.Clear
   Else
       blnCreated = False
   End If
 
   On Error GoTo 0
 
   Set olNs = olApp.GetNamespace("MAPI")
   Set Appointments = olNs.GetDefaultFolder(olFolderCalendar)
     
     i = 2
       Do Until Trim(Cells(i, "A").Value) = ""
          arrCal = Cells(i, "A").Value 
 
Set MyFolder1 = Appointments.Folders(arrCal)
   Set olApt = MyFolder1.Items.Add(olAppointmentItem)
      
   MsgBox MyFolder1, vbOKCancel, "Folder Name"
   With olApt
 
   'Define calendar item properties
       .Start = Cells(i, 6) + Cells(i, 7)     '+ TimeValue("9:00:00")
       .End = Cells(i, 8) + Cells(i, 9)       '+TimeValue("10:00:00")
       .Subject = Cells(i, 2)
       .Location = Cells(i, 3)
       .Body = Cells(i, 4)
      '.Body = "Test Body"
     '  .BusyStatus = olBusy
      ' .ReminderMinutesBeforeStart = range(i, 10)
       .ReminderSet = True
       .Categories = Cells(i, "E").Value
       .Save
 
   End With
             
       i = i + 1
       Loop
 
   Set olApt = Nothing
   Set olApp = Nothing
 
   Exit Sub
  
 
Err_Execute:
   MsgBox "An error occurred - Exporting items to Calendar."
  
 
End Sub
 

BigDawg15

Member
Outlook version
Outlook 2010 32 bit
Email Account
IMAP
Re: Excel 2010 vba to post appts to different outlook calendars at the same ti

Diane,

Thank you so much. Works as expected.

I very much appreciate your assistance.

Have a great rest of your weekend.

Regards,

BigDawg15
 

BigDawg15

Member
Outlook version
Outlook 2010 32 bit
Email Account
IMAP
Re: Excel 2010 vba to post appts to different outlook calendars at the same ti

Sorry,

I have played with the code a bit and I just have to say Thank you, Thank you, Thank you!

I am very excited to get this code working and be able to put it to use.

Thank you again.

BigDawg15
 
Status
Not open for further replies.
Thread starter Similar threads Forum Replies Date
P Excel 2010 can't open .xlsx files Using Outlook 1
ogodt Change Default contact form and import from Excel 2010 Using Outlook 1
K Importing appointment body from excel in outlook 2010 Using Outlook 1
J Importing to the correct calendar from Excel 2010 to Outlook 2010 Outlook VBA and Custom Forms 2
B Losing Excel Data when emailing with OutLook 2010 Using Outlook 4
T Importing Excel 2010 data into Outlook Calendar 2010 Using Outlook 12
M Error Messages when exporting Outlook 2010 Contacts to a CSV (DOS) or Excel Using Outlook 2
B Linking from Excel to public outlook 2010 calendar. Using Outlook 0
N Import Contacts Excel CSV File into Outlook 2010 Using Outlook 2
I Outlook 2010 - Unable to view embedded / in-line data pasted from Excel 2010 into Lotus Notes e-mail Using Outlook 5
A In Outlook 2010, I cannot preview Work or Excel attachments, even though all of the previewing optio Using Outlook 9
R Please Help: How to format muti-line address in Excel file before importing to Outlook 2010 Using Outlook 1
E importing Excel file into Outlook 2010 Using Outlook 3
R Exporting outlook 2010 tasks to excel Using Outlook 2
O Excel 2010 - problem with 'Send as PDF' Using Outlook 1
M Email merge from Excel not executing - Outlook Home&Business 2010 Using Outlook 2
S Excel 2010 Previewer shows NO DATA, just cell boundaries, 95% of the time - 2007 didn't have this is Using Outlook 4
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
C Pull Outlook shared calendars items from Excel 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 Accessing shared outlook folder doesn't work since switch to new outlook/excel Outlook VBA and Custom Forms 11
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 6
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
M Linking Excel Cell to Shared Contacts Outlook VBA and Custom Forms 7
H Macro to Copy Specific content from Mail Body and Paste to Excel Outlook VBA and Custom Forms 4
Similar threads


















































Top