如何将生日从 Excel 导入 Outlook 日历？

如果您在工作表中有很长的生日信息列表，现在您希望将这些生日作为事件导入您的 Outlook 日历。你怎么能用一些快速的方法来处理这个任务？

使用 VBA 代码将生日从 Excel 导入 Outlook 日历

通常情况下，没有直接将生日导入 Outlook 日历的方法，在这里，我将创建一个 VBA 代码来解决这个问题，请按照以下步骤操作：

1. 打开包含要导入 Outlook 的生日的工作表，然后按住 ALT + F11 键打开 Microsoft Visual Basic应用程序 窗口。

2。点击插页 > 模块，然后将以下代码粘贴到“模块窗口”中。

VBA 代码：将生日导入 Outlook 日历

Sub ImportBirthdaysToCalendar()
'Updateby ExtendOffice
Dim xWs As Excel.Worksheet
Dim xRng As Range
Dim xOlApp As Outlook.Application
Dim xCalendarFld As Outlook.Folder
Dim xAppointmentItem As Outlook.AppointmentItem
Dim xRecurrencePattern As Outlook.RecurrencePattern
Dim xRow As Integer
On Error Resume Next
Set xWs = ThisWorkbook.ActiveSheet
Set xRng = Application.InputBox("Please select the data range (only two columns):", "Kutools for Excel", , , , , , 8)
If xRng Is Nothing Then Exit Sub
If xRng.Columns.Count <> 2 Then
  MsgBox "You can only select two columns", vbOKOnly + vbCritical, "Kutools for Excel"
  Exit Sub
End If
Set xOlApp = CreateObject("Outlook.Application")
Set xCalendarFld = xOlApp.Session.GetDefaultFolder(olFolderCalendar)
For xRow = 1 To xRng.Rows.Count
  Set xAppointmentItem = xCalendarFld.Items.Add("IPM.Appointment")
  With xAppointmentItem
    .Subject = xRng.Cells(xRow, 1) & Chr(39) & "s Birthday"
    .AllDayEvent = True
    .Start = xRng.Cells(xRow, 2)
    Set xRecurrencePattern = .GetRecurrencePattern
    xRecurrencePattern.RecurrenceType = olRecursYearly
    .Save
  End With
Next
Set xWs = Nothing
Set xCalendarFld = Nothing
Set xOlApp = Nothing
End Sub

3。仍在 Microsoft Visual Basic应用程序 窗口中，单击工具 > 参考资料. 在弹出的 参考– VBAProject 对话框，检查 Microsoft Outlook 16.0 对象库 选项形式 可用参考 列表框，请参见屏幕截图：

4。然后点击 OK 关闭此对话框。现在，按 F5 键运行这段代码，弹出提示框，请选择姓名和生日栏，看截图：

5。然后，单击 OK 按钮，生日将立即导入 Outlook 日历，您可以启动 Outlook 来查看结果，见截图：

最佳办公生产力工具

Kutools for Outlook - 超过 100 种强大功能可增强您的 Outlook

🤖 人工智能邮件助手: 具有人工智能魔力的即时专业电子邮件——一键天才回复、完美语气、多语言掌握。轻松改变电子邮件！ ...

📧 电子邮件自动化: 外出（适用于 POP 和 IMAP） / 安排发送电子邮件 / 发送电子邮件时按规则自动抄送/密件抄送 / 自动转发（高级规则） / 自动添加问候语 / 自动将多收件人电子邮件拆分为单独的消息 ...

📨 电子邮件管理: 轻松回忆电子邮件 / 按主题和其他人阻止诈骗电子邮件 / 删除重复的电子邮件 / 高级搜索 / 合并文件夹 ...

📁 附件专业版: 批量保存 / 批量分离 / 批量压缩 / 自动保存 / 自动分离 / 自动压缩 ...

👍 一键奇迹: 使用传入附件回复全部 / 反网络钓鱼电子邮件 / 🕘显示发件人的时区 ...

👩🏼‍🤝‍👩🏻 通讯录和日历: 从选定的电子邮件中批量添加联系人 / 将联系人组拆分为各个组 / 删除生日提醒 ...

超过 100特点 等待您的探索！单击此处了解更多。

了解更多免费下载购买

Comments (5)

No ratings yet. Be the first to rate!

Jonas

about 1 year ago

#42808

This comment was minimized by the moderator on the site

Hi Skyyang,

I'm trying to use the code to import the birthdays into a Sharepoint Events List, which is located in my outlook as calendar, but when i run and then select the calendar the recurrances briefly show up and then vanish again right away.
I did successfully import the recurrances into my personal Calendar but I need it to go to the sharepoint calendar to share it with the company :( any ideas?

kind regards
Jonas

Sam

about 1 year ago

#42377

This comment was minimized by the moderator on the site

This is very helpful, thank you! Do you have a specific VBA code for the import, but specifically goes to the "Birthday" calendar in Outlook?

Also, now that I've imported to my calendar, is there an easy way to remove all of those inputted, if needed?

Thank you!

skyyang Sam

about 1 year ago

#42442

This comment was minimized by the moderator on the site

Hello, Sam,
May be the below VBA code can do you a favor:

Sub ImportBirthdaysToCalendar()
'Updateby ExtendOffice
Dim xWs As Excel.Worksheet
Dim xRng As Range
Dim xOlApp As Outlook.Application
Dim xCalendarFld As Outlook.Folder
Dim xAppointmentItem As Outlook.AppointmentItem
Dim xRecurrencePattern As Outlook.RecurrencePattern
Dim xRow As Integer
On Error Resume Next
Set xWs = ThisWorkbook.ActiveSheet
Set xRng = Application.InputBox("Please select the data range (only two columns):", "Kutools for Excel", , , , , , 8)
If xRng Is Nothing Then Exit Sub
If xRng.Columns.Count <> 2 Then
  MsgBox "You can only select two columns", vbOKOnly + vbCritical, "Kutools for Excel"
  Exit Sub
End If
Set xOlApp = CreateObject("Outlook.Application")
'Set xCalendarFld = xOlApp.Session.GetDefaultFolder(olFolderCalendar)
Set xCalendarFld = xOlApp.Session.PickFolder
If xCalendarFld.DefaultItemType <> olAppointmentItem Then
  MsgBox "Please Select calendar folder. ", vbOKOnly + vbInformation, "Kutools for Outlook"
  Exit Sub
End If
For xRow = 1 To xRng.Rows.Count
  Set xAppointmentItem = xCalendarFld.Items.Add("IPM.Appointment")
  With xAppointmentItem
    .Subject = xRng.Cells(xRow, 1) & Chr(39) & "s Birthday"
    .AllDayEvent = True
    .Start = xRng.Cells(xRow, 2)
    Set xRecurrencePattern = .GetRecurrencePattern
    xRecurrencePattern.RecurrenceType = olRecursYearly
    .Save
  End With
Next
Set xWs = Nothing
Set xCalendarFld = Nothing
Set xOlApp = Nothing
End Sub

After running this code, you can create a new folder in the calendar, and the imported birthday will be saved in this new folder, if you need to remove the birthdays, you just need go to this folder, and delete them at once.