VBA Excel 到 Outlook (2010) - 从日期列创建约会，仅具有唯一日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20868546/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Excel to Outlook (2010) - Creating Appointments from Date column with Unique dates only
提问by MrsAdmin
Info:MS Office 2010
Problem(s)Multiple Outlook Profiles & Multiple Calendars & Same date repeated - I want unique only
From:Excel 2010
To:Outlook 2010
信息：MS Office 2010
问题多个 Outlook 配置文件和多个日历和重复的相同日期 - 我只想要唯一
从：Excel 2010
到：Outlook 2010
Question:
I want to create a VBA that will run from Excel to Outlook. I want to create appointments (tasks if it's proven to be far more suitable) based on a date column in my sheet.
问题：
我想创建一个可以从 Excel 运行到 Outlook 的 VBA。我想根据我的工作表中的日期列创建约会（如果它被证明更合适，则为任务）。
The core issues are:
+ I have 3 different profiles, I only want the appointments/tasks showing in 1 profile (Admin).
+ I have different calendars, I would prefer the appointment to be in the default.
+ I want to add categories to my appointment/task
+ I have 10,000 rows of data, the dates repeat (a lot), I want only unique dates.
+ I do not wantto have prior dates creating appointments/tasks (if possible).
核心问题是：
+ 我有 3 个不同的配置文件，我只希望约会/任务显示在 1 个配置文件（管理员）中。
+ 我有不同的日历，我希望约会处于默认状态。
+ 我想为我的约会/任务添加类别 + 我有 10,000 行数据，日期重复（很多），我只想要唯一的日期。
+ 我不希望有创建约会/任务的先前日期（如果可能）。
I run 2 reports:
1st Tuesday of the month, pulls in all the sales data from previous months.
4 days before EOM I run the report a 2nd time looking for late payments, declines or refunds, as well as distributor payouts.
我运行 2 个报告：
每月的第一个星期二，提取前几个月的所有销售数据。在 EOM 前 4 天，我第二次运行报告以寻找延迟付款、拒绝或退款以及分销商付款。
The appointments/tasks are to be set to match the dates in excel, or to save some column space I would be happy to have it written in to the VBA code to set the appointments:
1st Tuesday Month
4 days before EOM
约会/任务将设置为与 Excel 中的日期相匹配，或者为了节省一些列空间我很乐意将其写入 VBA 代码以设置约会：
EOM 前 4 天的
第一个星期二
I have got some great starter code, but because mine has a lot of data that needs to be filtered (unique dates only, and current to future dates preferred), I'm not sure what I need amended in my code to make it suitable for my setup.
我有一些很棒的入门代码，但是因为我的有很多数据需要过滤（仅限唯一日期，首选当前到未来的日期），我不确定我需要在代码中修改什么以使其适合对于我的设置。
Ref:
http://www.ozgrid.com/forum/showthread.php?t=18157&p=92262#post92262
http://www.vbaexpress.com/forum/showthread.php?25423-Solved-Excel-generate-calendar-appointments-in-Outlook&s=da1942ccfb8b85e3e7eb74ac4c95ed7d&p=177521&viewfull=1#post177521
参考：
http ://www.ozgrid.com/forum/showthread.php? t =18157& p =92262# post92262
http://www.vbaexpress.com/forum/showthread.php?25423-Solved-Excel-generate-calendar -appointments-in-Outlook&s=da1942ccfb8b85e3e7eb74ac4c95ed7d&p=177521&viewfull=1#post177521
My current code
Saved/Created in: Macro enabled spreadsheet, Modules (General, not under the sheet name), Tools/References/Microsoft Office 14.0 Object Library [Checked].
我当前的代码
保存/创建于：启用宏的电子表格、模块（常规，不在工作表名称下）、工具/参考/Microsoft Office 14.0 对象库 [已检查]。
Sub StoreReminders()
Dim appOL As Object
Dim objReminder As Object
Set appOL = GetObject(, "Outlook.application")
Set objReminder = appOL.CreateItem(1) ' olAppointmentItem
Set ws1 = Worksheets("sql all 20131228")
objReminder.Start = ws1.Range("a1") & "10:30"
objReminder.Duration = "05:00"
objReminder.Subject = "EOM Reports #1"
objReminder.ReminderMinutesBeforeStart = 30
objReminder.ReminderSet = True
objReminder.Categories = "Acc - 1st Report"
'Becomes: "Acc - EOM Final" when 2nd appointment runs'
objReminder.BusyStatus = olBusy
objReminder.Save
End Sub
I am getting an error with some of this, starting with: Set appOL = GetObject(, "Outlook.application")
我遇到了一些错误，从以下开始： Set appOL = GetObject(, "Outlook.application")
Any help would be great, I'm cutting & paste code together and I just can't get past the errors.
任何帮助都会很棒，我正在将代码剪切和粘贴在一起，但我无法克服错误。
Thanks in advance :)
提前致谢 ：）
UPDATE
I received the following error when I used new code:
Compile Error: User-Defined type not defined
更新
我在使用新代码时收到以下错误：
编译错误：未定义用户定义的类型
Sub SetAppt()
Dim olApp As Outlook.Application
Dim olApt As AppointmentItem
Dim MySheet As Worksheet
Set MySheet = Worksheets("sql all 20131228")
Set olApp = New Outlook.Application
Set olApt = olApp.CreateItem(olAppointmentItem)
With olApt
.Start = ws1.Range("n7") + TimeValue("10:30")
'Time is set to 10:30AM on the date of the reminder'
.Duration = "05:00"
.Subject = "EOM Reports #1"
.Location = "Office"
.Body = "Start of Month, EOM Reports"
.BusyStatus = olBusy
.ReminderMinutesBeforeStart = 60
.Categories = "Acc - 1st Report"
'Becomes: "Acc - EOM Final" when 2nd appointment runs'
.ReminderSet = True
.Save
End With
Set olApt = Nothing
Set olApp = Nothing
End Sub
So, still at square one not even basic code is working, so not sure how to include the advanced (Outlook User Profile, Unique only dates etc).
因此，仍然在第一个阶段，甚至基本代码都无法运行，因此不确定如何包含高级（Outlook 用户配置文件、唯一日期等）。
回答by Steve Gon
You need to add the proper Reference to the Outlook VBA module.
您需要添加对 Outlook VBA 模块的正确引用。
Go to Tools-->References and choose "Microsoft Outlook vXX Library"
转到工具--> 参考并选择“Microsoft Outlook vXX 库”
That should fix the problem.
那应该可以解决问题。
