如何使用VBA在电子邮件中附加所有工作表，但第一位
如何使用VBA在电子邮件中附加所有工作表，但第一位
提问于 2017-03-22 10:19:16
我有一本有四张纸的工作簿：
第一-收件人电子邮件数据，如TO，CC，Subject，从第二到第四页，我需要作为附件发送给收件人。
我编写了以下脚本。但作为一个VBA初学者，我面临着两个问题：
- “循环”建议从第一张纸发送第一空行(我想停止使用最后的电子邮件详细信息)；
- 'ActiveWorkbook‘发送所有的纸张(我想跳过第一个收件人& VBA脚本所在的地方)；
我感谢每一个建议/评论，因为我已经学习了3个月的VBA。提前谢谢你！
Sub ICO_Emails()
Dim VSEApp As Object
Dim VSEMail As Object
Dim VSEText As String
Dim Email_Send_To, Email_Cc, Email_Subject As String
row_number = 1
Do
DoEvents
row_number = row_number + 1
Email_Send_To = Sheet1.Range("A" & row_number)
Email_Cc = Sheet1.Range("B" & row_number)
Email_Subject = Sheet1.Range("C" & row_number)
On Error GoTo debugs
Set VSEApp = CreateObject("Outlook.Application")
Set VSEMail = VSEApp.CreateItem(0)
'Email Body script
VSEText = "<BODY style=font-size:14pt;font-family:Times New Roman>Dear all,<p>Test.<p></BODY>"
'Email Signature
With VSEMail
.Display
End With
Signature = VSEMail.HTMLBody
With VSEMail
.To = Email_Send_To
.CC = Email_Cc
.Subject = Email_Subject
.HTMLBody = VSEText & Signature
.Attachments.Add ActiveWorkbook.FullName
.Display
End With
debugs:
Loop Until Email_Send_To = ""
End Sub回答 2
Stack Overflow用户
回答已采纳
发布于 2017-03-22 10:52:42
看看这个。这将获取工作簿的副本，并将其保存到用户“临时”位置。然后，它在附加之前对工作簿的副本执行所需的修改。
Sub ICO_Emails()
Dim VSEApp As Object
Dim VSEMail As Object
Dim VSEText As String
Dim Email_Send_To, Email_Cc, Email_Subject As String
Dim wb As Workbook, nwb As Workbook
Application.ScreenUpdating = False
Set wb = ThisWorkbook
wb.SaveCopyAs (Environ("temp") & "\temp_" & wb.Name)
Set nwb = Workbooks.Open(Environ("temp") & "\temp_" & wb.Name)
With nwb
Application.DisplayAlerts = False
' Delete relevant sheet
.Sheets(1).Delete
Application.DisplayAlerts = True
.Save
End With
row_number = 1
Do
DoEvents
row_number = row_number + 1
Email_Send_To = Sheet1.Range("A" & row_number)
Email_Cc = Sheet1.Range("B" & row_number)
Email_Subject = Sheet1.Range("C" & row_number)
On Error GoTo debugs
Set VSEApp = CreateObject("Outlook.Application")
Set VSEMail = VSEApp.CreateItem(0)
'Email Body script
VSEText = "<BODY style=font-size:14pt;font-family:Times New Roman>Dear all,<p>Test.<p></BODY>"
'Email Signature
With VSEMail
.Display
End With
Signature = VSEMail.HTMLBody
With VSEMail
.To = Email_Send_To
.CC = Email_Cc
.Subject = Email_Subject
.HTMLBody = VSEText & Signature
.Attachments.Add nwb.FullName
.Display
End With
debugs:
Loop Until Email_Send_To = ""
nwb.Close
Application.ScreenUpdating = True
End SubStack Overflow用户
发布于 2017-03-22 10:52:33
- 保存工作簿的副本
- 打开它
- 从副本中移除工作表
- 保存
- 发送此编辑工作簿
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/42948703
复制相关文章
相似问题
腾讯云开发者
