创建自动宏以保存Excel 2010并导入到Outlook 2010日历 [英] Creating an automated macro to save anExcel 2010 and import to Outlook 2010 calendar

问题描述

我正在把头撞到墙上。我不擅长VB或远程编程，但我知道我需要的具体和可能。

I'm banging my head against the wall here. I'm not adept in VB or programming remotely, but I know that what I need specific and possible.

问题：

导入电子表格日历事件（谁将去往何处和何时发生的事件），强制被邀请者/参加者块不会导入到日历上的每个条目。

Upon importing a spreadsheet of calendar events (Who is going to What event that is Where and When it is), the Mandatory Invitee/Attendee block does not import into each entry on the calendar.

背景：

我安排了超过50名员工进行各种培训，并可以在Excel中轻松跟踪。我想基于此列表向他们发送日历邀请，而无需在Outlook中手动设置单个约会/事件。使用Excel 2003-2007
格式并将受影响的数据定义为范围，我导入文件，映射字段并完成导入。除邀请者外，所有字段均已输入。他们的电子邮件地址包含在导入中，应该已经结转，但事实并非如此。我们的系统
使用全局地址列表系统来验证电子邮件（我从GAL中提取电子邮件地址），但该字段不会导入到每个事件中。

I'm scheduling over 50 employees for a variety of training and can easily track it in Excel. I would like to send them calendar invites based on this list without manually setting up individual appointments/events in Outlook. Using the Excel 2003-2007 format and the affected data being defined as a Range, I import the file, map the fields and the import completes. All fields are entered except for Invitees. Their email address is included in the import and SHOULD have carried over but it does not. Our system uses a Global Address List system which would validate the email (which I pulled the email address from the GAL), but the field doesn't import into each event.

解决方案：

我要求VBA脚本完成这些操作：

I am asking for VBA script that accomplishes these actions:

1. 在Excel 2003-2007版本中保存.xlsx为
2. 打开Outlook¥b $ b 3. 将.xls导入日历C2301培训

4. 更新受邀者阻止链接到GAL

5. 发送邀请给该人的电子邮件

1.  Save .xlsx in Excel 2003-2007 version
2.  Opens Outlook
3.  Imports .xls into calendar C2301 Training
4.  Updates invitees block with link to GAL
5.  Sends invite to that person's email

这样我就可以在Excel中运行我的宏并自动更新培训日历。我只是不够聪明，无法自己解决这个问题。

This would enable me to just run my macro in Excel and will update the training calendar automatically. I'm just not smart enough to figure this out on my own.

请帮忙。

推荐答案

>>> 1。在Excel 2003-2007版本中保存.xlsx



根据您的描述，您可以参考以下代码：

>>>1. Save .xlsx in Excel 2003-2007 version

According to your description, you could refer to below code:

Sub ProcessFiles()
Dim Filename, Pathname, saveFileName As String
Dim wb As Workbook
Dim initialDisplayAlerts As Boolean

Pathname = "<insert_path_here>"  ' Needs to have a trailing \
Filename = Dir(Pathname & "*.xlsx")
initialDisplayAlerts = Application.DisplayAlerts
Application.DisplayAlerts = False
Do While Filename <> ""
    Set wb = Workbooks.Open(Filename:=Pathname & Filename, _
                            UpdateLinks:=False)
    wb.CheckCompatibility = False
    saveFileName = Replace(Filename, ".xlsx", ".xls")

    wb.SaveAs Filename:=Pathname & saveFileName, _
              FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
              ReadOnlyRecommended:=False, CreateBackup:=False

    wb.Close SaveChanges:=False
    Filename = Dir()
Loop
Application.DisplayAlerts = initialDisplayAlerts
End Sub

>> ;大于2。  打开Outlook



使用Visual Basic for Applications（VBA）工具菜单上的Reference命令设置对Microsoft Outlook xx.x对象库的引用，然后你可以参考下面的代码：

>>>2.  Opens Outlook

Use the Reference command on the Visual Basic for Applications (VBA) Tools menu to set a reference to Microsoft Outlook xx.x Object Library, then you could refer to below code:

Dim oOutlook As object
Set oOutlook = GetObject(, "Outlook.Application")

If oOutlook Is Nothing Then
   Set oOutlook = CreateObject("Outlook.Application")
End If

我建议你可以从
Excel VBA参考&n a;和
Outlook VBA参考



此外还有很多问题在你的帖子中，我建议你可以创建多线程，每个帖子都包含一个简单的问题。



感谢您的理解。

I suggest that you could start with Excel VBA reference and Outlook VBA reference

In addition since there are many questions in your post, I suggest that you could create multi thread that every thread contains one simple question.

Thanks for your understanding.

这篇关于创建自动宏以保存Excel 2010并导入到Outlook 2010日历的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！