我整个上午都在尝试获取此 VBA 脚本,将我的活动 Excel 文档附加到自动生成的 Outlook 消息中。如果我将文件路径声明为字符串并附加它,一切都会正常工作。除了我想附加当前 Excel 文档的完整文件路径而不是使用静态字符串值。
这是我的代码:
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
编译器在这一行给了我一个错误:
.Attachments.Add (ActiveDocument.FullName)
我做了一些研究,并尝试自己解决该问题,但我只是不知道如何使该脚本将活动文件附加到此 Outlook 消息。正如您从我的代码中看到的,我的备份选项是仅使用字符串变量和静态地址来附加文件,但我宁愿使该脚本比该脚本更通用。
这是我发现给我这个想法的网站之一:Here http://www.mrexcel.com/forum/excel-questions/711979-visual-basic-applications-attach-active-document.html
好吧,经过更多的努力,我能够完美地附加工作簿。这是我对原始代码中的 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
我想我会回答我自己的问题,这样它就不会在没有技术答案的情况下徘徊。也许它会对将来的某人有所帮助。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)