跳到主要内容

当工作簿保存在Excel中时,如何通过Outlook发送电子邮件?

本文讨论的是在Excel中保存特定工作簿时通过Outlook发送电子邮件。 请按照本教程中的说明进行操作。

使用VBA代码保存工作簿时,通过Outlook发送电子邮件


使用VBA代码保存工作簿时,通过Outlook发送电子邮件

若要在工作簿保存在Excel中时通过Outlook发送电子邮件,请执行以下操作。

1.请首先将工作簿另存为Excel Macro-Enabled Workbook。 请点击 文件 > 另存为。 在 另存为 对话框中,选择一个文件夹来保存工作簿,在“文件名”框中将其命名,然后选择 Excel启用宏的工作簿 来自 保存类型 下拉列表,然后单击 保存 按钮。 看截图:

2.打开刚才保存的Excel Macro-Enabled Workbook,按 其他 + F11 同时打开 Microsoft Visual Basic应用程序 窗口。

2.在 Microsoft Visual Basic应用程序 窗口,请双击 的ThisWorkbook 在左侧栏中,然后将以下VBA代码复制并粘贴到 的ThisWorkbook 代码窗口。 看截图:

VBA代码:保存工作簿时发送电子邮件

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
'Updated by Extendoffice 20181102
    Dim xOutApp As Object
    Dim xMailItem As Object
    Dim xName As String
    On Error Resume Next
    Set xOutApp = CreateObject("Outlook.Application")
    Set xMailItem = xOutApp.CreateItem(0)
    xName = ActiveWorkbook.FullName
    With xMailItem
        .To = "Email Address"
        .CC = ""
        .Subject = "The workbook has been saved"
        .Body = "Hi," & Chr(13) & Chr(13) & "File is now updated."
        .Attachments.Add xName
        .Display
       '.send
    End With
    Set xMailItem = Nothing
    Set xOutApp = Nothing
End Sub

备注:请更换 电子邮件 收件人电子邮件地址在一行 .To =“电子邮件地址”根据需要在VBA代码中更改抄送,主题以及正文字段。

3。 按 其他 + Q 同时关闭按键 Microsoft Visual Basic应用程序 窗口。

从现在开始,当您更新并保存工作簿时,将自动创建一封电子邮件,其中附有更新的工作簿。 请点击 提交 按钮发送电子邮件。 看截图:

备注:仅当您将Outlook用作电子邮件程序时,VBA代码才起作用。


相关文章:

最佳办公生产力工具

热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup    多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧    工作簿和工作表管理器   |  资源库 (自动文本)   |  日期选择器   |  合并工作表   |  加密/解密单元格    按列表发送电子邮件   |  超级筛选   |   特殊过滤器 (过滤粗体/斜体/删除线...)...
前 15 个工具集12 文本 工具 (添加文本, 删除字符,...)   |   50+ 图表 类型 (甘特图,...)   |   40+ 实用 公式 (根据生日计算年龄,...)   |   19 插入 工具 (插入二维码, 从路径插入图片,...)   |   12 转化 工具 (小写金额转大写, 货币兑换,...)   |   7 合并与拆分 工具 (高级组合行, 分裂细胞,...)   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能,体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

kte选项卡201905


Office Tab 为 Office 带来选项卡式界面,让您的工作更加轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50%,并减少数百次鼠标单击!
Comments (17)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Dear All, can some one help me, I'm a novice in VBA coding, I have made some modification, but how can I do so send email if the workbook is saved, and if user name is different, like if user name of the station is glade2 then send email if the workbook is saved, else do not send.

Thank you so much for your support
This comment was minimized by the moderator on the site
Hi florin,
Didn't get your point. What does your username represent?
This comment was minimized by the moderator on the site
Hi crystal, thank you for your reply, usernane is enviroment usernane, and i have done it, i did use a if function and exit sub.
Thank you so much.
This comment was minimized by the moderator on the site
Hello - How can I include cell data in the email "cc" field?
This comment was minimized by the moderator on the site
Hi Brent,
Supposing you want to include the value in cell a7 in the email "cc" field, please try the below VBA.

Private Sub Workbook_AfterSave(ByVal Success As Boolean)

'Updated by Extendoffice 20200628

Dim xOutApp As Object

Dim xMailItem As Object

Dim xName As String

On Error Resume Next

Set xOutApp = CreateObject("Outlook.Application")

Set xMailItem = xOutApp.CreateItem(0)

xName = ActiveWorkbook.FullName

With xMailItem

.To = "Email Address"

.CC = Range("a7").Value


.Subject = "The workbook has been saved"

.Body = "Hi," & Chr(13) & Chr(13) & "File is now updated."

.Attachments.Add xName

.Display

'.send

End With

Set xMailItem = Nothing

Set xOutApp = Nothing

End Sub
This comment was minimized by the moderator on the site
Hi! Thanks a lot for this guideline :-) I would like to do something more in this code - send an email based on the countries. It means that I have to create commands with if and select. Right? I have already delete the attachment from email. I would like to add the link with path into folder instead. But when macro run, command is not valid :-(
I appreciate each help how to add it there.
This comment was minimized by the moderator on the site
Is there a way to have the automated email be auto encrypted?
This comment was minimized by the moderator on the site
Hi Mike,
Sorry can't help to solve this problem. Thank you for your comment.
This comment was minimized by the moderator on the site
How would this be handled for an Office 365 document. It is automatically saving.
This comment was minimized by the moderator on the site
Hi, nice article! One thing ive been trying to achieve with this is to attach the current state of the workbook to the email.

At the moment, it only sends the original state of the file and doesnt include any changes the user would have made.

Any ideas on how to implement this using a macro?
This comment was minimized by the moderator on the site
Hi Chris,
The code has been updated with the problem solved, please have a try. Thank you for your comment.
This comment was minimized by the moderator on the site
Hi Chris,

I have chanced upon the same issue.
Currently the codes are used in the "beforesave" module.
Which means that the email will send the spreadsheet that is before saved.

There is another module "aftersave".
I applied the code in this module and it worked like a charm.
This comment was minimized by the moderator on the site
Dear all, I would like to ask how to attach functional link to certain folder on server, if I paste the link, it appears in the workbook just like plain text and so it doensn't work in received e-mail, how can I turn it into link, so recepiants can click on it?
I would like to use this way instead sending enclosed excel file.
Thanks for advice
This comment was minimized by the moderator on the site
I put this in the body of the email and it worked for me...
"file:///Z:\dir1\dir2\dir3\Test1.xlsm"
This comment was minimized by the moderator on the site
Hi Robert,
Sorry can’t help with this, welcome to post any question about Excel to our forum: https://www.extendoffice.com/forum.html. You will get more Excel supports from our professional or other Excel fans.
This comment was minimized by the moderator on the site
Gracias. Consulta: utilizando esta misma rutina, como podría enviar el correo a un correo específico dependiendo el valor de otra celda?
This comment was minimized by the moderator on the site
How to automate the email notification in VBA based on Date range, without having to see the pop up for security permission to allow VBA to send the email.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations