根据Excel文件中的数据，在发送Outlook邮件之前接收消息提示

Question

我们与外包和自由职业者合作，很难跟踪谁不在以及何时不在。

我拼凑了一个Excel文件，其中用户表单简化了Excel工作表中的日期输入，人员(通过电子邮件地址输入)不可用。

下一步是将此Excel文件"连接"到Outlook，这样当我单击"发送"或输入电子邮件地址时，宏：

1. 检查输入的电子邮件地址是否在Excel文件的工作表中
2. 检查当前时刻是否位于该名称旁边的不可用日期之间
3. 当这两个条件都满足时，会发出提示消息，告诉我此人不可用，并让我取消发送电子邮件
4. 可选：消息提示中有一些字段，这些字段根据该人员的不可用时间段填充

是否可以通过这种方式将Excel连接到Outlook，以便我们可以继续使用Outlook编写邮件？

简化后，它应该看起来像这样：

Click “Send” in Outlook email window
Before sending, call Excel file (does not need to be visible)
Check e-mail address column (column A)
 Matches “To” field in Outlook email window?
If No, Send email
If Yes, Check “From” date column (column C) next to corresponding email address
 Present date is equal to or later than “From” date?
If No, Send Email
If Yes, Check “Until” Date column (column D): present date is earlier than or equal to present date?
If Yes, message prompt: “Name (column B) is not available from “From” until “Until”. Do you still want to send the e-mail?
Buttons:
Yes: Send Email
No: Close prompt, do not send email, but keep email open.
If No, Send Email

Excel文件中用户表单的代码：

Private Sub CommandDate1_Click()
DatePicker1.Show
AbsencePlannerUserForm.StartTextBox.SetFocus
End Sub
Private Sub CancelButton_Click()
Unload Me
End Sub
Private Sub ClearButton_Click()
Call AbsencePlannerUserForm_Initialize
End Sub
Private Sub CommandDate2_Click()
DatePicker2.Show
AbsencePlannerUserForm.EndTextBox.SetFocus
End Sub
Private Sub EndTextBox_Change()
End Sub
Private Sub ExtraInfoTextBox_Change()
End Sub
Private Sub OKButton_Click()
Dim M_Date As Date
Dim M_Item As String
M_Date1 = StartTextBox
M_Date2 = EndTextBox
M_Item = EmailTextBox
M_Info = ExtraInfoTextBox
Application.ScreenUpdating = False
LastRow = Abwesenheit1.Cells(Rows.Count, "D").End(xlUp).Row
For rw = 2 To LastRow
If Abwesenheit1.Cells(rw, "A") = M_Item And Cells(rw, "C") = M_Date1 _
And Cells(rw, "D") = M_Date2 And Cells(rw, "E") = M_Info Then GoTo Passem
Next rw
GoTo NO_Dups

Passem:
Application.ScreenUpdating = True

MsgBox "Der Urlaub für " & M_Item & " vom " & M_Date1 & " bis zum " & _
M_Date2 & " ist schon eingetragen."
Exit Sub

NO_Dups:

Dim emptyRow As Long

'Make Abwesenheit1 active
Abwesenheit1.Activate

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Transfer information
Cells(emptyRow, 1).Value = EmailTextBox.Value
Cells(emptyRow, 3).Value = StartTextBox.Value
Cells(emptyRow, 4).Value = EndTextBox.Value
Cells(emptyRow, 5).Value = ExtraInfoTextBox.Value

Application.Visible = True
Me.Hide

End Sub

Private Sub AbsencePlannerUserForm_Click()

End Sub

Private Sub AbsencePlannerUserForm_Initialize()
'Empty EmailTextBox
EmailTextBox.Value = ""

'Empty StartTextBox
StartTextBox.Value = ""

'Empty EndTextBox
EndTextBox.Value = ""

'Empty ExtraInfoTextBox
ExtraInfoTextBox.Value = ""

'Set Focus on EmailTextBox
EmailTextBox.SetFocus
End Sub

Sub open_form()
Application.Visible = False
UserForm1.Show vbModeless
End Sub
Private Sub StartTextBox_Change()

End Sub

Private Sub UserForm_Click()

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Application.Visible = True
Me.Hide
End Sub

Answer 1

这是可以做到的。您需要在VBA环境中通过excel创建对Outlook的引用。要做到这一点：

从工具/参考激活outlook库

(图书馆的编号可能因您的电脑而异)

然后调用outlook:

https://learn.microsoft.com/en-us/office/vba/outlook/concepts/getting-started/automating-outlook-from-a-visual-basic-application

您可以设置一个条件，如果它匹配，则显示一个包含所有不可用信息的用户窗体，并添加按钮，单击该按钮时退出子窗体并关闭outlook消息。

如果你发布代码，处理它会更容易，但基本上，调用outlook，创建一个带有按钮的用户表单，在需要时关闭所有内容。