vba 如何将活动 Excel 工作簿附加到电子邮件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22255655/
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
How to attach active Excel workbook to an email
提问by user6654783
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.
我整个上午都在尝试让这个 VBA 脚本将我的活动 excel 文档附加到自动生成的 Outlook 消息中。如果我将文件路径声明为字符串并附加它，则一切正常。除了我想附加当前 excel 文档的完整文件路径而不是使用静态字符串值。
Here is my code:
这是我的代码：
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)
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.
我做了一些研究，并试图自己解决这个问题，但我不知道如何让这个脚本将活动文件附加到这个 Outlook 消息中。正如您在我的代码中看到的那样，我的备份选项是仅使用字符串变量和静态地址来附加文件，但我宁愿让这个脚本更通用。
Here is one of the sites which I found that gave me this idea to begin with: Here
这是我发现给我这个想法的网站之一：这里
回答by
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:
好吧，经过更多的努力，我能够完美地附加工作簿。这是我在原始代码中对 OutMail 对象所做的修改：
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.
我想我会回答我自己的问题，所以它不会在没有技术答案的情况下徘徊。也许它会在未来帮助某人。
回答by user6654783
The fix should actually be:
修复实际上应该是：
With OutMail
.To = sTo
.CC = CC
.Subject = "STR Pre-Clearance"
.HTMLBody = strbody & signature
.Attachments.Add (ActiveWorkbook.FullName) 'should be workbook not document
.Display 'or .Send
