vba 保存工作簿时发送电子邮件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16964917/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Send an email when workbook is saved
提问by David Van der Vieren
I am trying to send an email that will update users of changes to a spread sheet. I am trying to make it so that when the document is saved there will be an email automatically sent with a list of the changes.
我正在尝试发送一封电子邮件，将更新用户对电子表格的更改。我正在尝试这样做，以便在保存文档时会自动发送一封电子邮件，其中包含更改列表。
Does anyone know if it is possible to automate email upon saving the document?
有谁知道是否可以在保存文档时自动发送电子邮件？
回答by user2140261
You can use this code here not fancy as Chip Pearson but easy to understand, This method also relies on using outlook:
你可以在这里使用这段代码，不像 Chip Pearson 那样花哨但很容易理解，这个方法也依赖于使用 Outlook：
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Outlook As Object, EMail As Object
Set Outlook = CreateObject("Outlook.Application")
Set EMail = Outlook.CreateItem(0)
With EMail
.To = "[email protected]; [email protected]"
.CC = ""
.BCC = ""
.Subject = "Put your subject here"
.Body = "Add you E-Mail Message Here"
.Attachments.Add ActiveWorkbook.FullName ' To add active Workbook as attachment
.Attachments.Add "C:\Test.xlsx" ' To add other files just use path, Excel files, pictures, documents pdf's ect.
.Display 'or use .Send to skip preview
End With
Set EMail = Nothing
Set Outlook = Nothing
End Sub
To set this up Here is the full guide:
设置这里是完整的指南：
First open up the VBA window using ALT+ F11then Select Worbook on the window to the right, Then workbook from the drop down:
首先使用ALT+打开 VBA 窗口F11，然后在右侧窗口中选择 Worbook，然后从下拉列表中选择工作簿：
Then from the Drop down on the right Select BeforeSave:
然后从右侧的下拉菜单中选择 BeforeSave：
Then paste your code there:
然后将您的代码粘贴到那里：
You should end with this:
你应该这样结束：
回答by Chris Spicer
It should be. You'll need to place your code in the Workbook_BeforeSave event, so it is triggered when the workbook is saved.
它应该是。您需要将代码放在 Workbook_BeforeSave 事件中，以便在保存工作簿时触发它。
Chip Pearson has a good article on Sending E-mail from VBA
Chip Pearson 有一篇关于从 VBA 发送电子邮件的好文章
回答by Santosh
You need to put the code in ThisWorkbook code section. Workbook_BeforeSaveevent is triggered before workbook is save. Hope below code gives you an idea how it can be accomplished.
您需要将代码放在 ThisWorkbook 代码部分。 Workbook_BeforeSave在保存工作簿之前触发事件。希望下面的代码可以让您了解如何完成它。
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
' Identify here list of changes
' You can pass as a string to SendMail
Dim strChanges As String
strChanges = "test"
SendMail strChanges
End Sub
Sub SendMail(msg As String)
Dim iMsg As Object
Dim iConf As Object
Dim Flds As Variant
Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
iConf.Load -1
Set Flds = iConf.Fields
'Configure the below details
With Flds
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "test-002"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
.Update
End With
With iMsg
Set .Configuration = iConf
.To = "[email protected]"
.From = "[email protected]"
.Subject = "msg" & " " & Date & " " & Time
.TextBody = msg
.Send
End With
Set iMsg = Nothing
Set iConf = Nothing
End Sub
