使用Excel发送Outlook会议邀请 [英] Send Outlook Meeting Invitation using Excel

问题描述

我希望为工作表中的每一行发送会议邀请.

I am looking to send meeting invitations for each row in a worksheet.

我能够创建一个项目，该项目在显示时显示为约会，而不是可以发送给其他人的会议请求.我需要在Outlook中单击邀请参加者"，然后显示电子邮件地址并可以发送，但是如果我有多行的话，这将花费很多时间.

I am able to create an item that when displayed shows as an appointment, not a meeting request that can be sent to others. I need to click on "Invite Attendees" in Outlook and then the email addresses display and I can send but it would take a lot of time if I have more than a few rows.

这似乎是一个常见问题，因为我在其他论坛中发现了这个问题，但是没有一个解决方案适合我.

This seems to be a common problem as I found this question in other forums but none have a solution that worked for me.

Sub SendAction()

    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range

    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")

    For Each cell In Worksheets("Action Log").Range("H5:H50").Cells
        Set OutMail = OutApp.CreateItem(1)
        If cell.Value Like "*@*" Then      'try with less conditions first
            With OutMail
                .MeetingStatus = olMeeting
                .RequiredAttendees = Cells(cell.Row, "H").Value
                .Subject = Cells(cell.Row, "I").Value
                .Body = Cells(cell.Row, "I").Value
                .Start = Cells(cell.Row, "E").Value & " " & TimeValue("8:00 AM")
                .Location = "Your Office"
                .Duration = 15 ' 15 minute meeting
                .BusyStatus = 0 ' set as free
                .ReminderSet = True 'reminder set
                .ReminderMinutesBeforeStart = "20160" 'reminder 2 weeks before
                .display                
            End With

            Cells(cell.Row, "K").Value = "sent"
            Set OutMail = Nothing
        End If
    Next cell

    Application.ScreenUpdating = True

End Sub

这是我尝试过的一种替代方法，但未能解决问题:

Here is one alternative I tried but it did not fix the issue:

Application.Wait DateAdd("s", 2, Now)       'waiting for 2 sec to let OL window to display.
SendKeys "%s", True                         'Sending Mail.
Set olApt = Nothing

MsgBox "Invite Sent", vbInformation

来源: https://excel-buzz .blogspot.com/2015/03/automation-sending-invitation-to.html

另一种替代方法是将.Display更改为.Save，但是.Send功能将无法正常工作，因此我需要从Outlook中的草稿邮件中打开会议请求.

Another alternative is to change .Display to .Save but the .Send function won't work either way and I would then need to open the meeting request from my draft messages in Outlook.

推荐答案

我意识到了这个问题.我链接到电子邮件的单元格包含公式而不是电子邮件地址文本.一旦我将电子邮件地址更改为文本而不是公式，我的VBA便可以正常工作.

I realized the issue. The cell I was linking to for the emails contained a formula instead of the email address text. Once I changed the email addresses to text instead of a formula my VBA worked perfectly.

这篇关于使用Excel发送Outlook会议邀请的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！