当下属在Excel工作表中进行任何更新时,我想向利益相关者发送电子邮件。我希望使用Workbook_BeforeSave
事件,其中从下属的Outlook帐户触发电子邮件。
下级/用户需要在他们的系统中配置/安装Outlook。如果不是,邮件不会被触发。
是否有办法克服这一点,比如将邮件触发请求发送到远程计算机/服务器,其中Outlook已预先配置,并从该计算机/服务器发送邮件到使用通用或集中的电子邮件id的利益相关者?
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim OutApp As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Recipient
Dim Recipients As Recipients
Set OutApp = CreateObject("Outlook.Application")
Set objOutlookMsg = OutApp.CreateItem(olMailItem)
Set Recipients = objOutlookMsg.Recipients
Set objOutlookRecip = Recipients.Add("receiver@domain.com")
objOutlookRecip.Type = 1
objOutlookMsg.SentOnBehalfOfName = "sender@domain.com"
objOutlookMsg.Subject = "Testing this macro"
objOutlookMsg.HTMLBody = "Testing this macro "
For Each objOutlookRecip In objOutlookMsg.Recipients
objOutlookRecip.Resolve
Next
objOutlookMsg.Display
objOutlookMsg.Send
Set OutApp = Nothing
End Sub
Option Explicit
Private Sub CommandButton1_Click()On Error GoTo ErrHandler
' SET Outlook APPLICATION OBJECT.
Dim objOutlook As Object
Set objOutlook = CreateObject("Outlook.Application")
' CREATE EMAIL OBJECT.
Dim objEmail As Object
Set objEmail = objOutlook.CreateItem(olMailItem)
With objEmail
.to = "webadmin@encodedna.com"
.Subject = "This is a test message from Arun Banik"
.Body = "Hi there"
.Display ' DISPLAY MESSAGE.
End With
' CLEAR.
Set objEmail = Nothing: Set objOutlook = Nothing
ErrHandler:'终止子
你所需要做的就是把。display属性改成。send属性。
With objEmail
.to = "arunbanik21@rediffmail.com"
.Subject = "This is a test message from Arun"
.Body = "Hi there"
.Send ' SEND THE MESSAGE.
End With
更多信息请访问https://www.encodedna.com/excel/send-email-from-excel-using-vba-and-outlook.htm
按照步骤操作,
- 我们需要从Outlook发送电子邮件。由于Outlook是一个外部对象,我们需要做的第一件事就是将对象引用设置为"Microsoft Outlook 16.0 object Library"。
- 在VBA中,转到工具>引用。 现在我们将看到对象参考库。在此窗口中,我们需要将引用设置为"Microsoft Outlook 16.0 Object Library"。
- 设置好对象参考后,点击,Ok。
- 现在我们可以在VBA编码中访问Outlook对象
Sub SendEmail_Example1()
Dim EmailApp As Outlook.Application 'To refer to outlook application
Set EmailApp = New Outlook.Application 'To launch outlook application
Dim EmailItem As Outlook.MailItem 'To refer new outlook email
Set EmailItem = EmailApp.CreateItem(olMailItem) 'To launch new outlook
email
EmailItem.To = "Hi@gmail.com"
EmailItem.CC = "hello@gmail.com"
EmailItem.Subject = "Test Email From Excel VBA"
EmailItem.HTMLBody = "Hi," & vbNewLine & vbNewLine & "This is my first email from Excel" & _
vbNewLine & vbNewLine & _
"Regards," & vbNewLine & _
"VBA Coder" 'VbNewLine is the VBA Constant to insert a new line
EmailItem.Send
End Sub
想了解更多,你可以试试这篇文章中提到的,
https://www.wallstreetmojo.com/vba-send-email-from-excel/