我正在尝试从Excel
中的userform
设置Outlook约会。如果我引用单元格,则该代码有效。如何在userform
中引用框?我还需要添加到会议的代码收件人中,我将从其他列表工作表中参考。
这是引用Excel中的单元格中的代码,该电池通过单击工作表中的按钮来工作:
Sub AddAppointments()
' Create the Outlook session
Set myOutlook = CreateObject("Outlook.Application")
' Start at row 2
r = 2
Do Until Trim(Cells(r, 1).Value) = ""
' Create the AppointmentItem
Set myApt = myOutlook.CreateItem(1)
' Set the appointment properties
myApt.Subject = Cells(r, 1).Value
myApt.Location = Cells(r, 2).Value
myApt.Start = Cells(r, 3).Value
myApt.Duration = Cells(r, 4).Value
' If Busy Status is not specified, default to 2 (Busy)
If Trim(Cells(r, 5).Value) = "" Then
myApt.BusyStatus = 2
Else
myApt.BusyStatus = Cells(r, 5).Value
End If
If Cells(r, 6).Value > 0 Then
myApt.ReminderSet = True
myApt.ReminderMinutesBeforeStart = Cells(r, 6).Value
Else
myApt.ReminderSet = False
End If
myApt.Body = Cells(r, 7).Value
myApt.Display
r = r + 1
Loop
End Sub
这是我尝试更改代码以在用户形式中参考框的尝试:
Private Sub Cmdappointment_Click()
Dim outlookapp As Object
'the mail item is the contents inside a mail
Dim mitem As AppointmentItem
'created outlook app
Set outlookapp = CreateObject("outlook.Application")
'it will open a new application
Set outlookapp = New Outlook.Application
'Set mail item
Set mitem = outlookapp.CreateItem(olMailItem)
Do Until userform2.TextBox4.Value = ""
' Create the AppointmentItem
Set myApt = myOutlook.CreateItem(1)
' Set the appointment properties
On Error Resume Next
mitem
myApt.Subject = Me.texbox4.Value
myApt.Location = Me.texbox3.Value
myApt.Start = Me.ComboBox1.Value
myApt.Duration = Me.ComboBox2.Value
' If Busy Status is not specified, default to 2 (Busy)
If Me.ComboBox3.Value = "" Then
myApt.BusyStatus = 2
Else
myApt.BusyStatus = Me.ComboBox3.Value
End If
If Me.TextBox1.Value > 0 Then
myApt.ReminderSet = True
myApt.ReminderMinutesBeforeStart = Me.TextBox1.Value
Else
myApt.ReminderSet = False
End If
myApt.Body = Me.TextBox2.Value
myApt.Display
End With
Loop
End Sub
对不起,评论中无法适应代码,所以这里有几个问题...
您正在创建 添加收件人做以下 要使它更加安全,我还将添加以下行outlookapp
并使用myOutlook
对象。
而且,您还分别从outlookapp
和myOutlook
创建两个邮件mitem
和myApt
。最终仅使用myApt
。我不知道myOutlook
的起源。但是我会重写代码以仅使用一组。就像您的工作表应用程序中的一组Outlook和MailItem对象 Set outlookapp = CreateObject("outlook.Application")
'it will open a new application
Set outlookapp = New Outlook.Application
'Set mail item
Set mitem = outlookapp.CreateItem(olMailItem)
Do Until userform2.TextBox4.Value = ""
' Create the AppointmentItem
Set myApt = myOutlook.CreateItem(1)
myApt.Recipients.Add('j doe')
Dim myApt As AppointmentItem
Sub cmdappointment_Click()
' Create the Outlook session
Set myOutlook = CreateObject("Outlook.Application")
Do Until userform2.TextBox4.Value = ""
' Create the AppointmentItem
Set myApt = myOutlook.CreateItem(1)
' Set the appointment properties
myApt.Subject = userform2.TextBox4.Value
myApt.Location = userform2.TextBox3.Value
myApt.Start = userform2.ComboBox1.Value
myApt.Duration = userform2.ComboBox2.Value
' If Busy Status is not specified, default to 2 (Busy)
If userform2.ComboBox3.Value = "" Then
myApt.BusyStatus = 2
Else
myApt.BusyStatus = userform2.ComboBox3.Value
End If
If userform2.TextBox1.Value > 0 Then
myApt.ReminderSet = True
myApt.ReminderMinutesBeforeStart = userform2.TextBox1.Value
Else
myApt.ReminderSet = False
End If
myApt.Body = userform2.TextBox2.Value
myApt.Display
Exit Do
Loop
End Sub