将多个文件附加到循环中的Outlook电子邮件中
问题描述:
我试图从活动电子表格中的单元格中绘制可编辑的输入。我设法在Outlook中生成电子邮件并附加多个文件。将多个文件附加到循环中的Outlook电子邮件中
问题是,不是通过每个单元格与数据(和不同的目录输入)循环,从而附加“x”数量的单个文件,而是附加相同的文件“x”次。
Option Explicit
Sub TESTCreateEmail()
'define variables for outlook
Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem
'define variables for attachements to email
Dim wb As Workbook
Dim ws As Worksheet
Dim LoopAttach As Integer
Dim x As Integer
Dim y As Integer
x = Range("E9", Range("E100").End(xlUp)).Count
'set parameters for outlook
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
'set parameters for attachements to email
Set wb = ThisWorkbook
Set ws = wb.Application.ActiveSheet
'create email
With olMail
.To = ws.Range("H9")
.CC = ws.Range("I9")
olMail.SentOnBehalfOfName = "[email protected]"
olMail.Recipients.ResolveAll
.BodyFormat = olFormatHTML
olMail.HTMLBody = "<html><p><font face=""Calibri""><font size=3>Dear Sir/ Madam,</p>"
'loop to attach multiple files
For LoopAttach = 1 To x
.Attachments.Add ws.Range("E9").Offset(x - 1, 0) _
& ws.Range("F9").Offset(x - 1, 0)
Next LoopAttach
.Display
End With
End Sub
答
在代码变量x值保持在执行循环期间相同。这就是为什么每次执行循环时都附加相同的文件。
试试这个
For LoopAttach = x To 1 Step -1
.Attachments.Add ws.Range("E9").Offset(LoopAttach - 1, 0) _
& ws.Range("F9").Offset(LoopAttach - 1, 0)
Next LoopAttach
答
这是简单的错误 - x是用于循环一定的限度。您需要使用LoopAttach
变量而不是x
。
For LoopAttach = 1 To x
.Attachments.Add ws.Range("E9").Offset(LoopAttach - 1, 0) _
& ws.Range("F9").Offset(LoopAttach - 1, 0)
Next LoopAttach
+0
我不能够感谢你! – Jimmy
我不能够感谢你! – Jimmy