Live Chat & Podcast at 1:00PM Eastern on Sunday!
There's no such thing as a stupid question, but they're the easiest to answer.
JoinTour
Login
Search
Business Applications
Tag Cloud
access acer asus bios bsod computer crash desktop dns driver drivers error ethernet excel freeze gaming graphics hard drive hardware hdmi internet laptop malware memory monitor motherboard network printer problem ram registry repair router slow software sound trojan ubuntu 11.10 uninstall usb video virus vista wifi windows windows 7 windows 7 32 bit windows 7 64 bit windows xp wireless
Search
Search for:
Tech Support Guy Forums > Software & Hardware > Business Applications >
Excel to Outlook Macro

Reply  
Thread Tools
Sporadic's Avatar
Member with 43 posts.
 
Join Date: Jun 2009
Location: Ontario, Canada
Experience: Intermediate
29-Jun-2009, 03:41 PM #1
Smile Excel to Outlook Macro
Hey guys, I had some great assistance from computerman29642 earlier on this macro. It works perfectly on my own calendar; however, I need to target a specific shared calendar. Hopefully it could be user imput by a msg box. I am struggling to find information on how to do this.

The first thread was this
http://forums.techguy.org/business-a...-calendar.html

So, as for now the example works. But before it writes to the calendar I want a msg box pops up asking for the desired calendar(Sometimes more than one word), and then target that calendar and write the data. A simple little box that confirms or denies progress (I am pretty sure I can figure that one out) Any suggestions or directions where I can learn more fire away please. Ill attach a copy of my test again.
Attached Files
File Type: xls test4.xls (44.5 KB, 205 views)
computerman29642's Avatar
Computer Specs
Senior Member with 2,794 posts.
 
Join Date: Dec 2007
Location: HERE OR THERE?!?!?!
Experience: Always Learning!
06-Jul-2009, 04:11 PM #2
Sporadic, I do not have the capability to create or use shared calendars, but I will do my best to lend you a hand.

Let me do some research.
Sporadic's Avatar
Member with 43 posts.
 
Join Date: Jun 2009
Location: Ontario, Canada
Experience: Intermediate
10-Jul-2009, 10:17 AM #3
Thanks Computerman! Always appreciate the help I may have found a way around it if it can't be done.
computerman29642's Avatar
Computer Specs
Senior Member with 2,794 posts.
 
Join Date: Dec 2007
Location: HERE OR THERE?!?!?!
Experience: Always Learning!
10-Jul-2009, 10:27 AM #4
I apologize Sporadic. I have been extremely busy here at work, and I have not had time to look into the issue as I would have liked to do.

I will try to take a look at this issue again this weekend.
Sporadic's Avatar
Member with 43 posts.
 
Join Date: Jun 2009
Location: Ontario, Canada
Experience: Intermediate
10-Jul-2009, 11:08 AM #5
No need to apologize computerman any help at any time is welcome Don't stress about it if you get the time take a peak. I have been crazy busy too. Any help whenever is much appreciated so again no worries
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 5,030 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
12-Jul-2009, 02:03 PM #6
Hi there,

Although I can't test this where I'm at, maybe you could try something like this ...

Code:
Sub ExportAppointmentsToOutlook()

    Dim olApp As Outlook.Application
    Dim olFolder As Outlook.MAPIFolder      '**
    Dim olSubfolder As Outlook.MAPIFolder   '**
    Dim olApt As Outlook.AppointmentItem    '*
    Dim sCalendar As String                 '**
    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

    Set ws = ActiveWorkbook.ActiveSheet     '*
    
    With ws.Range("A2:B" & ws.Rows.Count)
        LastRow = .Find(What:="*", after:=.Cells(1, 1), LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End With
    sCalendar = InputBox("Calendar Name Please")
    
    'Code to access another calender (folder)
    Set olFolder = Application.Session.GetDefaultFolder(olFolderCalendar)
    On Error Resume Next
    Set olSubfolder = Folder.Folders(sCalendar)
    If olSubfolder Is Nothing Then
        'an error occured
        MsgBox "An error occured attempting that Calendar!", vbExclamation, "ERROR!"
        'do whatever you want
    End If
    Set olApt = olSubfolder.Items.Add
    
    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("D" & x).Value
        .BusyStatus = olBusy
        .ReminderSet = False
        .AllDayEvent = True
        .Save
    End With
    Next x

    If blnCreated = True Then olApp.Quit    '*
    
    Set olApt = Nothing
    Set olApp = Nothing

End Sub
Ensure you save a copy of the file prior to testing. Let us know how it works.
Sporadic's Avatar
Member with 43 posts.
 
Join Date: Jun 2009
Location: Ontario, Canada
Experience: Intermediate
13-Jul-2009, 09:06 AM #7
Thank you I appreciate you taking the time to write up some code. It is currently giving me a Compile error: User-defined type not defined. And it is highlighting
Sub ExportAppointmentsToOutlook
Dim olApp As Outlook.Application
I am going to try to look up what all that means and fiddle around with it. This is an awesome start Thank you again
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 5,030 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
13-Jul-2009, 12:48 PM #8
Well, the dimensioning (Dim lines) are an Early binding. If you don't have the Outlook reference set, it will give you a compile error. Go (in the VBE) to Tools | References. List what is checked. If Microsoft Outlook xx.0 Object Library (where xx.0 is your version number) is not checked, find it and check it, then click OK. Test your project (compile it) by using the keyboard shortcut Alt + D, L.

Does that help?
Sporadic's Avatar
Member with 43 posts.
 
Join Date: Jun 2009
Location: Ontario, Canada
Experience: Intermediate
13-Jul-2009, 01:31 PM #9
Okay, I did that when I run it it now gives me a
Run-time error '91' Object or varible or With block variable not set.
Not exaclty sure where that error is comming from ill continue to try to find it
Sporadic's Avatar
Member with 43 posts.
 
Join Date: Jun 2009
Location: Ontario, Canada
Experience: Intermediate
13-Jul-2009, 01:45 PM #10
End With
sCalendar = InputBox("Calendar Name Please")

'Code to access another calender (folder)
Set olFolder = Application.Session.GetDefaultFolder(olFolderCalendar)
On Error Resume Next
Set olSubfolder = Folder.Folders(sCalendar)
If olSubfolder Is Nothing Then
'an error occured
MsgBox "An error occured attempting that Calendar!", vbExclamation, "ERROR!"
'do whatever you want
End If
Set olApt = olSubfolder.Items.Add


I can run my debugger up to this point before it gives me the error. So, I assume that it must be here or further down. I am not sure how to check below a certain point.
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 5,030 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
13-Jul-2009, 01:57 PM #11
What line is highlighted when you debug? And after you debug, can you step through each line with F8, and what line does it stop on? (Sometimes these two are different, sometimes they are the same, which tells us different things.)
Sporadic's Avatar
Member with 43 posts.
 
Join Date: Jun 2009
Location: Ontario, Canada
Experience: Intermediate
13-Jul-2009, 02:12 PM #12
LastRow = .Find(What:="*", after:=.Cells(1, 1), LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

is the line it will stop at. It won't let me past it. This line is just above the End With that I posted ealier
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 5,030 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
13-Jul-2009, 03:00 PM #13
That's because you have nothing in those cells, and the Find method will error out if there is nothing to find. Try replacing the following lines of code...
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
... with this code ...
Code:
    With ws.Range("A2:B" & ws.Rows.Count)
        If WorksheetFunction.CountA(.Cells) = 0 Then
            LastRow = 0 'you don't REALLY need this line, since it'll already be zero..
        Else
            LastRow = .Find(What:="*", after:=.Cells(1, 1), LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        End If
    End With
Sporadic's Avatar
Member with 43 posts.
 
Join Date: Jun 2009
Location: Ontario, Canada
Experience: Intermediate
13-Jul-2009, 03:08 PM #14
Awesome thank you that has cleared that part now. It gives me an error after I attempt to type the calendar in.
Set olFolder = Application.Session.GetDefaultFolder(olFolderCalendar) is where the debugger stops.

Run-time '438'
Object doesn't support this property or method

Thanks again for the help if this is starting to stress you id understand it boggles my mind how you can pick this stuff apart how you do.
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 5,030 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
13-Jul-2009, 06:23 PM #15
Right-o, didn't catch that. On that line, change the Application to olApp. On the line just below it (2 lines below), change it from Folder.Folders(sCalendar) to olFolder.Folders(sCalendar) (just add the "ol" in front of it).
Reply

Tags
calendar, excel, macro, outlook

THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
WELCOME TO TECH SUPPORT GUY! Are you looking for the solution to your computer problem? Join our site today to ask your question -- for free! Our site is run completely by volunteers who want to help you solve your computer problems. See our Welcome Guide to get started.
Thread Tools



Facebook Facebook Twitter Twitter TechGuy.tv TechGuy.tv Mobile TSG Mobile
You Are Using:
Server ID
Advertisements do not imply our endorsement of that product or service.
All times are GMT -4. The time now is 07:51 PM.
Copyright © 1996 - 2011 TechGuy, Inc. All rights reserved.

Powered by Cermak Technologies, Inc.