Excel VBA创建新的Outlook约会会导致已取消的约会。

huangapple go评论62阅读模式
英文:

Excel VBA creating a new Outlook appointment results in a cancelled appointment

问题

当我运行以下代码并使用.display时,Outlook约会以正确的方式创建(共享日历、收件人、时间等),我可以发送生成的会议邀请,接收者将其作为会议邀请接收。然而,如果我将.display更改为.send,一切似乎都正常工作,但接收者会收到一封会议取消的邮件(关于一个不存在的会议!)。

有人能指出我哪里出错了吗?

英文:

When I run the following code with .display, the Outlook Appointment gets created in the correct way (shared calendar, recipients, time etc) and I can send the resultant meeting request and it is received by the recipient as a meeting request. However, if I change .display to .send, everything appears to work OK, but the recipient recieves a meeting cancellation (for a meeting that doesn't exist!).

Can anyone point out where I'm going wrong?

Sub CreateMeetings()

Dim olApp As Outlook.Application
Dim olAppItem As Outlook.AppointmentItem
Dim r As Long
Dim oApp As Object
Dim oNameSpace As Namespace
Dim myCalendar As Object
Dim OLNS As Object
Const olAppointmentItem As Long = 1
Dim OLAppointment As Object
Dim MeetingKey As String
Dim datenum As Long
Dim smtprecipient As String
Dim MeetingKeyString As String
Dim emailchk As Long



Set oApp = New Outlook.Application
Set olApp = CreateObject("Outlook.Application")

On Error Resume Next


Set olApp = GetObject("", "Outlook.Application")
On Error GoTo 0
If olApp Is Nothing Then
On Error Resume Next
Set olApp = CreateObject("Outlook.Application")
On Error GoTo 0
If olApp Is Nothing Then
MsgBox "Outlook is not available!"
Exit Sub
End If
End If
'get default user email address
smtprecipient = GetSMTPEmailAddress

'check to see if email address returned is a valid one
emailchk = InStr(1, smtprecipient, "@company_domain.co.uk")
'get a valid email address if the check fails
If emailchk = 0 Then
    smtprecipient = InputBox("Enter your Company Email Address", "Email Address Required")
End If


Set OLNS = olApp.GetNamespace("MAPI")
    OLNS.Logon
    Dim objRec As Outlook.Recipient
    Set objRec = OLNS.CreateRecipient(smtprecipient)
    objRec.Resolve
    Set myCalendar = OLNS.GetSharedDefaultFolder(objRec, olFolderCalendar).Folders("Frontline")
    Set OLAppointment = myCalendar.Items.Add(olAppointmentItem)
    Dim i As Long, Schedsht As Worksheet
    Set Schedsht = Worksheets("Shift Allocation")
    Sheets("Shift Allocation").Select

For i = 6 To Range("A" & Rows.Count).End(xlUp).Row
If Schedsht.Range("T" & i).Value = "" And Schedsht.Range("S" & i).Value = True Then
datenum = Date + (Time * 10000) + i
MeetingKeyString = Schedsht.Range("Z" & i).Value
MeetingKey = "S" & CStr(datenum) & Schedsht.Range("B" & i).Value
    With OLAppointment
            .Subject = "Shift" & " (" & MeetingKey & ")"
            .RequiredAttendees = Schedsht.Range("I" & i).Value & ";" & Schedsht.Range("J" & i).Value _
             & ";" & Schedsht.Range("K" & i).Value
            .Start = Schedsht.Range("D" & i).Value
            .End = Schedsht.Range("E" & i).Value
            .Location = Schedsht.Range("C" & i).Value
            .ReminderMinutesBeforeStart = 720
            .MeetingStatus = olMeeting
            
            .Body = Schedsht.Range("M" & i).Value & vbCrLf & vbCrLf & "Welcome to our new Rota system. For details on how this all works, _
            please go to xxxx."
           .Display
            '.Send
        On Error GoTo 0
    End With

Schedsht.Range("T" & i).Value = True
Schedsht.Range("Y" & i).Value = MeetingKey
Schedsht.Range("AA" & i).Value = MeetingKeyString
Else

End If

Next i
 
MsgBox "All Shifts Processed"
Set olAppItem = Nothing
Set olApp = Nothing
Set oFolder = Nothing

Exit Sub

Set olAppItem = Nothing
Set olApp = Nothing
Set oFolder = Nothing
End Sub

See above. changing to .display works OK, .send doesn't

答案1

得分: 1

不能从共享文件夹显式发送项目,因为将使用不正确的发件人。对于邮件项目,您可以在需要代表另一个人发送项目时使用SentOnBehalfOfName属性,但对于约会,不可行。

在从共享文件夹提交项目之前,请调用Save方法,然后调用Send方法。

英文:

You can not send items from a shared folder explicitly because an incorrect sender will be used. You can use the SentOnBehalfOfName property for mail items, but not appointments, when you need to send items on behalf of another person.

Call the Save method before the Send one to submit the item from a shared folder.

huangapple
  • 本文由 发表于 2023年2月14日 00:13:59
  • 转载请务必保留本文链接:https://go.coder-hub.com/75438474.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定