如何将活动的Excel工作簿附加到电子邮件 [英] How to attach active Excel workbook to an email
问题描述
我一直在整天都在尝试获取此VBA脚本，以将我的活动excel文档附加到自动生成的Outlook消息中.如果我将文件路径声明为字符串并将其附加，则一切正常.除了我想附加当前excel文档的完整文件路径，而不是使用静态字符串值.
I have been trying all morning to get this VBA script to attach my active excel document to an auto-generated outlook message. Everything works fine if I declare the file path as a string and attach it. Except that I would like to attach the full file path of the current excel document instead of using a static string value.
这是我的代码:
Private Sub CommandButton1_Click()
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim sAttach As String
Dim sTo As String
Dim sCC As String
'For To field
Set emailRng = Worksheets("Pre-Clearance Email").Range("E11:J14")
For Each cl In emailRng
sTo = sTo & ";" & cl.Value
Next
sTo = Mid(sTo, 2)
'For CC field
Set emailRngCC = Worksheets("Pre-Clearance Email").Range("E16:J19")
For Each cl In emailRngCC
sCC = sCC & ";" & cl.Value
Next
sCC = Mid(sCC, 2)
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
'variable declarations for email body and attachment
strbody = "<BODY style=font-size:11pt;font-family:Calibri>Good Morning;<p>Please see the attached aliases for validation. Please let me know if you have any questions.<p>Thank you.</BODY>"
sAttach = "K:\CRM Support\Data\Systematic Trade Recon (1).xlsm"
'the below code adds a users default signature to the email
With OutMail
.Display
End With
signature = OutMail.HTMLBody
With OutMail
.to = sTo
.CC = sCC
.Subject = "STR Pre-Clearance"
.HTMLBody = strbody & signature
.Attachments.Add (ActiveDocument.FullName)
'.Attachments.Add sAttach
.Display 'Instead of .Display, you can use .Send to send the email _
or .Save to save a copy in the drafts folder
End With
编译器在这一行给我一个错误:
The compiler gives me an error at this line:
.Attachments.Add (ActiveDocument.FullName)
我已经进行了一些研究，并尝试自己解决问题，但我只是想不出如何使此脚本将活动文件附加到此Outlook消息上.如您的代码所示，我的备份选项是仅使用字符串变量和静态地址来附加文件，但我希望此脚本比以前更具通用性.
I have done some research, and tried to fix the problem myself, but I just can't figure out how to make this script attach the active file to this outlook message. As you can see by my code, my backup option is to just use a string variable and a static address to attach the file, but I would rather make this script more versatile than that.
Here is one of the sites which I found that gave me this idea to begin with: Here
推荐答案
好吧，经过更多的努力，我能够使工作簿完美地附加.这是我用原始代码对OutMail对象所做的修订:
Well, after some more effort I was able to get the workbook to attach flawlessly. Here was the revision I made to the OutMail Object in my orginial code:
With OutMail
.to = sTo
.CC = sCC
.Subject = "STR Pre-Clearance"
.HTMLBody = strbody & signature
.Attachments.Add (ActiveDocument.FullName) 'this is the correction I made
.Display
我认为我会回答自己的问题，所以没有技术答案就不会存在.也许它将对将来的某个人有所帮助.
I figured I would answer my own question so it doesn't linger without a technical answer. Maybe it will help someone in the future.
这篇关于如何将活动的Excel工作簿附加到电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
